| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 11/25/2003 : 07:39:54
|
Todd writes "I have been trying to work out how to create a procedure to insert records into a couple of inventory tables.
I simplified everything by creating a fictional fruit basket scenario that perfectly illustrates the problem. Parts of the problem (parsing the input string, etc.) can be found in some of the excellent articles on SQLTeam. However, where I run out of idea's is how to create each of the combinations required and assigning the uniqueness of each. Not knowing the number of FruitBasketIDs or the maximum is really throwing me. I've tried combinations of nested loops and cross joins without success. Any help would be greatly appreciated.
The goal is to create a procedure that:
Takes input in the form of an array multiple FruitId's (1,2,3) (input in the form of a table is allowed)
Creates a FruitBasketID for each combination of FruitTypeIDs for the FruitIDs entered
No two FruitBasketIDs contains the same combination of FruitTypeIDs
Business rules:
There is no maximum to the number of fruit in a basket
The number of fruit in each basket cannot be known (2), (1,3), (1,2,3)
Like fruit cannot be in the same basket (1,1,2) not allowed
Shop rules:
Existing tables can be altered as long as the existing view still works and existing relationships are preserved
No dynamic SQL
No cursors
Environment:
SQL Server 2000 SP3A on Windows Server 2000 SP4
Here is the DDL and some data...
CREATE TABLE Fruit (
FruitID int NOT NULL,
FruitName varchar(50) NOT NULL,
CONSTRAINT PK_Fruit PRIMARY KEY (FruitID)
)
CREATE TABLE FruitType (
FruitTypeID int NOT NULL,
FruitID int NOT NULL,
FruitTypeName varchar(50) NOT NULL,
CONSTRAINT PK_FruitType PRIMARY KEY (FruitTypeID),
CONSTRAINT IX_FruitType UNIQUE NONCLUSTERED (FruitID,FruitTypeName),
CONSTRAINT FK_FruitType_Fruit FOREIGN KEY (FruitID) REFERENCES Fruit (FruitID)
)
CREATE TABLE FruitBasket (
FruitBasketID int NOT NULL
CONSTRAINT PK_FruitBasket PRIMARY KEY (FruitBasketID)
)
CREATE TABLE FruitType_FruitBasket (
FruitTypeID int NOT NULL,
FruitBasketID int NOT NULL,
CONSTRAINT PK_FruitType_FruitBasket PRIMARY KEY (FruitTypeID,FruitBasketID),
CONSTRAINT FK_FruitType_FruitBasket_FruitBasket FOREIGN KEY (FruitBasketID) REFERENCES FruitBasket (FruitBasketID),
CONSTRAINT FK_FruitType_FruitBasket_FruitType FOREIGN KEY (FruitTypeID) REFERENCES FruitType (FruitTypeID)
)
INSERT Fruit (FruitID,FruitName) VALUES (1,'APPLE')
INSERT Fruit (FruitID,FruitName) VALUES (2,'ORANGE')
INSERT Fruit (FruitID,FruitName) VALUES (3,'PEAR')
INSERT FruitType (FruitTypeID, FruitID, FruitTypeName) VALUES (1,1,'RED DELICIOUS')
INSERT FruitType (FruitTypeID, FruitID, FruitTypeName) VALUES (2,1,'GRANNY SMITH')
INSERT FruitType (FruitTypeID, FruitID, FruitTypeName) VALUES (3,2,'NAVEL')
INSERT FruitType (FruitTypeID, FruitID, FruitTypeName) VALUES (4,2,'VALENCIA')
INSERT FruitType (FruitTypeID, FruitID, FruitTypeName) VALUES (5,3,'YELLOW BARTLETT')
INSERT FruitType (FruitTypeID, FruitID, FruitTypeName) VALUES (6,3,'GREEN ANJOU')
INSERT FruitBasket (FruitBasketID) VALUES (1)
INSERT FruitBasket (FruitBasketID) VALUES (2)
INSERT FruitType_FruitBasket (FruitTypeID, FruitBasketID) VALUES (1,1)
INSERT FruitType_FruitBasket (FruitTypeID, FruitBasketID) VALUES (3,1)
INSERT FruitType_FruitBasket (FruitTypeID, FruitBasketID) VALUES (5,1)
INSERT FruitType_FruitBasket (FruitTypeID, FruitBasketID) VALUES (1,2)
INSERT FruitType_FruitBasket (FruitTypeID, FruitBasketID) VALUES (3,2)
INSERT FruitType_FruitBasket (FruitTypeID, FruitBasketID) VALUES (6,2)
CREATE VIEW FruitInBasket
AS
SELECT d.FruitBasketID, a.FruitName, b.FruitTypeName
FROM Fruit a INNER JOIN
FruitType b ON a.FruitID = b.FruitID INNER JOIN
FruitType_FruitBasket c ON b.FruitTypeID = c.FruitTypeID INNER JOIN
|
|
|
ehorn
Flowing Fount of Yak Knowledge
USA
1629 Posts |
Posted - 11/25/2003 : 20:15:56
|
Are you talking about a one-time load of all the "fruit" to build all possible combinations of baskets or reccuring loads?
|
 |
|
|
MichaelP
Jedi Yak
USA
2489 Posts |
Posted - 11/25/2003 : 23:48:43
|
<Yoda>Smells of homework this does.</Yoda>
Michael
<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
xpandre
Posting Yak Master
193 Posts |
Posted - 11/26/2003 : 04:14:07
|
am not sure...but could u just try this n let me know!
alter procedure concatt @fid varchar(20) as
begin
create table #temp (fruitbasketid int,fruittypeid int,fruitid int)
create table #temp1 (fruitbasketid int,fruittypeid varchar(20))
CREATE TABLE #TempList(fruitid int)
declare @b varchar(700),@maxbid int
DECLARE @OrderID varchar(10), @Pos int
SET @fid = LTRIM(RTRIM(@fid))+ ','
SET @Pos = CHARINDEX(',', @fid, 1)
IF REPLACE(@fid, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@fid, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO #TempList (fruitid) VALUES (CAST(@OrderID AS int))
END
SET @fid = RIGHT(@fid, LEN(@fid) - @Pos)
SET @Pos = CHARINDEX(',', @fid, 1)
END
END
insert into #temp
select fruitbasketid,fruittypeid,fruitid
from (
select a.FruitBasketID, b.fruittypeid,b.fruitid from
FruitType_FruitBasket a,FruitType b where b.FruitID in (select * from #TempList) and
a.FruitTypeID =b.FruitTypeID )c order by c.FruitBasketID,c.fruittypeid
select @maxbid=max(fruitbasketid) from #temp
while @maxbid>0
begin
set @b=''
select @b = coalesce(@b + ' ','') + cast(fruittypeid as varchar)
from
(select fruittypeid from #temp where fruitbasketid =@maxbid) as a
insert into #temp1 values (@maxbid,@b)
set @maxbid=@maxbid-1
end
select distinct(fruittypeid) fruittypeid,(select top 1 fruitbasketid from #temp1 c where c.fruittypeid=b.fruittypeid order by fruitbasketid) basketid from #temp1 b
end
GO
exec concatt '1,2,3'
|
|
|
|
nathan_d_s
Starting Member
4 Posts |
Posted - 11/28/2003 : 06:37:00
|
Just try this..
Here I am using XML to get the FruitID's as input and inserting distinct FruitID into a table variable @Fruits. You can change the code to use your way of getting the FruitID's as input and populate them into the table variable @Fruits.
Create Procedure DBO.CreateFruitBaskets
@ntxtFruits ntext
As
Begin
SET NOCOUNT ON
DECLARE @intXMLDoc int
DECLARE @Fruits TABLE (FruitID int)
DECLARE @FruitTypes TABLE (RowID int IDENTITY(1,1), FruitTypeID int, FruitID int)
EXEC sp_xml_preparedocument @intXMLDoc OUTPUT, @ntxtFruits
Insert Into @Fruits(FruitID)
SELECT FruitID FROM OPENXML (@intXMLDoc, '/fruits/fruit') WITH (FruitID int '@fruitid')
Insert Into @FruitTypes(FruitTypeID,FruitID)
Select Distinct FT.FruitTypeID,F.FruitID From FruitType FT INNER JOIN @Fruits F ON F.FruitID=FT.FruitID
DECLARE @i int,@j int,@k int,@Count int,@loop bit,@ComboID int
DECLARE @next int, @last int
DECLARE @Combo Table(ComboID int, Sets int, FruitTypeID int, FruitID int)
Set @i = 1
Set @ComboID = 0
Select @Count = Max(RowID) From @FruitTypes
While(@i<=@Count)
Begin
Set @j = 1
While(@j<=@Count)
Begin
If(@i=1)
Begin
Set @ComboID = @ComboID + 1
Insert Into @Combo(ComboID,Sets,FruitTypeID,FruitID)
Select @ComboID,@i,FruitTypeID,FruitID From @FruitTypes Where RowID=@j
End
Else If((@j+@i)<=(@Count+1))
Begin
Set @next = @j + 1
Set @last = 0
Set @loop = 1
While(@loop=1)
Begin
If(@last>=@Count)
Begin
Set @next = @next + 1
Set @last = 0
End
If(@last=0)
Set @last = @next + (@i-2)
Else
Set @last = @last + 1
If(@last<=@Count and (@next-@j)<@i)
Begin
Set @ComboID = @ComboID + 1
Insert Into @Combo(ComboID,Sets,FruitTypeID,FruitID)
Select @ComboID,@i,FruitTypeID,FruitID From @FruitTypes Where RowID=@j
Set @k = 1
While(@k<=(@i-1))
Begin
If(@k<(@i-1))
Insert Into @Combo(ComboID,Sets,FruitTypeID,FruitID)
Select @ComboID,@i,FruitTypeID,FruitID From @FruitTypes Where RowID=(@next+(@k-1))
Else
Insert Into @Combo(ComboID,Sets,FruitTypeID,FruitID)
Select @ComboID,@i,FruitTypeID,FruitID From @FruitTypes Where RowID=@last
Set @k = @k + 1
End
End
Else
Set @loop = 0
End
End
Set @j = @j + 1
End
Set @i = @i + 1
End
Delete From CBO
From @Combo CBO INNER JOIN
(Select ComboID,FruitID,Count(*) As Total From @Combo Group By ComboID,FruitID Having Count(*)>1) DUP ON DUP.ComboID=CBO.ComboID
DECLARE @Temp Table(RowID int IDENTITY(1,1), ComboID int)
Insert Into @Temp(ComboID)
Select Distinct ComboID From @Combo Order By ComboID
Update CBO Set CBO.ComboID=TMP.RowID From @Combo CBO INNER JOIN @Temp TMP ON TMP.ComboID=CBO.ComboID
DECLARE @MaxFruitBasketID int
Begin Tran
Select @MaxFruitBasketID = IsNull(Max(FruitBasketID),0) From FruitBasket (UPDLOCK)
Insert Into FruitBasket(FruitBasketID)
Select Distinct ComboID+@MaxFruitBasketID From @Combo Order By 1
Insert Into FruitType_FruitBasket(FruitBasketID,FruitTypeID)
Select ComboID+@MaxFruitBasketID,FruitTypeID From @Combo Order By 1,2
Commit Tran
End
Declare @XML varchar(8000)
Set @XML = '<fruits><fruit fruitid=''1''/><fruit fruitid=''2''/><fruit fruitid=''3''/></fruits>'
exec dbo.CreateFruitBaskets @XML
Nathan |
|
|
| |
Topic |
|
|
|
Stumped on inventory procedure
