Stumped on inventory procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stumped on inventory procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-25 : 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
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-25 : 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

2489 Posts

Posted - 2003-11-25 : 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

212 Posts

Posted - 2003-11-26 : 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 - 2003-11-28 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources