SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stumped on inventory procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/25/2003 :  07:39:54  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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
1631 Posts

Posted - 11/25/2003 :  20:15:56  Show Profile  Reply with Quote
Are you talking about a one-time load of all the "fruit" to build all possible combinations of baskets or reccuring loads?

Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 11/25/2003 :  23:48:43  Show Profile  Visit MichaelP's Homepage  Reply with Quote
<Yoda>Smells of homework this does.</Yoda>

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

xpandre
Posting Yak Master

201 Posts

Posted - 11/26/2003 :  04:14:07  Show Profile  Reply with Quote
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'
Go to Top of Page

nathan_d_s
Starting Member

4 Posts

Posted - 11/28/2003 :  06:37:00  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000