July 30, 2001 at 3:25 pm
I would appreciate some feedback. As part of a database conversion project I need to determine the package that has all the options that a customer has selected. The problem is minimized here but basically there is one table that relates options to packages and another table that relates customers to the options they have selected. The challenge is to determine what package has the options the customer has selected.
I am somewhat new at writing T-SQL and would appreciate any feedback on my solution which uses a recursive function. Is there a better or more efficient way? Any comments are welcome.
Here are example tables. I will post my solution next.
-- A table that tells what options are in each plan
Create Table PkgOptions
( Pkg Integer
, Opt Char(3)
, CONSTRAINT PkgOptions_PK PRIMARY KEY ( Pkg, Opt )
)
Go
-- A table that tells what options each customer has selected
Create Table CustOptions
( CustID Int
, Opt Char(3)
, CONSTRAINT CustOptions_PK PRIMARY KEY ( CustID, Opt )
)
Go
-- Insert some data
Insert into PkgOptions (Pkg, Opt) Values (101,'D01')
Insert into PkgOptions (Pkg, Opt) Values (101,'D02')
Insert into PkgOptions (Pkg, Opt) Values (101,'M01')
Insert into PkgOptions (Pkg, Opt) Values (102,'D01')
Insert into PkgOptions (Pkg, Opt) Values (102,'L01')
Insert into PkgOptions (Pkg, Opt) Values (102,'M01')
Insert into PkgOptions (Pkg, Opt) Values (103,'D01')
Insert into PkgOptions (Pkg, Opt) Values (103,'M02')
Insert into CustOptions (CustID, Opt) Values (1,'D01') -- Pkg 101 or 102
Insert into CustOptions (CustID, Opt) Values (1,'M01')
Insert into CustOptions (CustID, Opt) Values (2,'D01') -- Pkg 102
Insert into CustOptions (CustID, Opt) Values (2,'M01')
Insert into CustOptions (CustID, Opt) Values (2,'L01')
Insert into CustOptions (CustID, Opt) Values (3,'D01') -- Pkg 103
Insert into CustOptions (CustID, Opt) Values (3,'M02')
Insert into CustOptions (CustID, Opt) Values (4,'L01') -- Pkg 102
July 30, 2001 at 3:31 pm
I created 2 functions. One to prepare the for a call to the main recursive function. Here is the first:
-- ==================================
-- fn_GetCustPkg is a function that returns one package number
-- that has all the options that the customer has selected.
-- =========================================
CREATE FUNCTION fn_GetCustPkg
(@CustID int)
RETURNS int
AS
BEGIN
DECLARE @Opt Char(3), @OptList varchar(50)
DECLARE cur_CustOptions CURSOR LOCAL FOR
SELECT Opt FROM dbo.CustOptions WHERE CustID = @CustID
-- ========================
-- Since we can't pass an array of options create
-- a comma delimited list of options
-- ======================================
SET @OptList = ''
OPEN cur_CustOptions
FETCH NEXT FROM cur_CustOptions INTO @Opt
WHILE @@FETCH_STATUS = 0
BEGIN
SET @OptList = @OptList + @Opt + ','
FETCH NEXT FROM cur_CustOptions INTO @Opt
END
-- remove last comma
SET @OptList = SUBSTRING(@OptList,1,LEN(@OptList)-1)
CLOSE cur_CustOptions
DEALLOCATE cur_CustOptions
-- Call fn_GetPkgRecursive to get all packages that satify the options
-- We can only return one package number so choose the lowest valued one
RETURN (SELECT Min(Pkg) FROM dbo.fn_GetPkgRecursive(@OptList))
END
July 30, 2001 at 3:35 pm
Here is the recursive fuction that does most of work:
-- ==========================================
-- fn_GetPkgRecursive is a function that recuresively calls
-- itself but ultimately returns package numbers that that have
-- all of the options
-- ==========================================
CREATE FUNCTION fn_GetPkgRecursive
(@OptList varchar(50))
RETURNS @tblPkgsForCust TABLE (Pkg int)
AS
BEGIN
DECLARE
@Opt char(5),
@OptRemaining varchar(50)
-- ==============
-- Is there more than one option left in the list?
-- ======================================
IF len(ltrim(@OptList))>3
BEGIN
-- ===============
-- There is more than one option in the list.
-- Strip off the first one and pass the rest down to a recursive call
-- to this function.
-- ================================
Set @Opt = SUBSTRING(@OptList, 1, 3)
Set @OptRemaining = SUBSTRING(@OptList, 5, LEN(@OptList)-4)
INSERT @tblPkgsForCust
-- Here is where the recursive call takes place
SELECT Pkg FROM dbo.PkgOptions
WHERE Opt = @Opt
AND Pkg IN (Select * from fn_GetPkgRecursive(@OptRemaining))
END
ELSE
BEGIN
-- ==================
-- There in only one option in the list.
-- This is the lowest level of recursion.
-- Get all the plans that have this option and pass
-- back to the caller of this function.
-- ==================================
INSERT @tblPkgsForCust
SELECT Pkg FROM dbo.PkgOptions WHERE Opt = @OptList
END
RETURN
END
Finally, I can use a SELECT similar to the following to determine the package that satisfies each customer.
Select Distinct(CustID), dbo.fn_GetCustPkg(CustID) From CustOptions
Any feedback would be appreciated. Thanks for your time.
July 31, 2001 at 6:37 am
Hi,
U can get a list of packages that match the customers option by using this sql
SELECT Pkg.Pkg
FROM
(SELECT Pkg As Pkg,
COUNT(*) As OptMatch
FROM PkgOptions
INNER JOIN CustOptions
ON CustOptions.Opt = PkgOptions.Opt AND CustOptions.CustID =4
GROUP BY Pkg) As Pkg
CROSS JOIN
(SELECT COUNT(*) As CustOpt
FROM CustOptions
WHERE CustID = 4) As CustOpt
WHERE Pkg.OptMatch = CustOpt.CustOpt
FOR Just replace the 4 with whatever custid u want
to get the output for each cust . u can modify and use this sql as a derived table and join it with the cust table to get the output as required
Hope this Helps
July 31, 2001 at 8:35 am
You should also be able to use the Shape Provideer and create a shaped recordset to show the relationships.
July 31, 2001 at 11:19 am
GRN, thanks for the suggestion to use COUNT as a way to determine the package. Since I need a query for a data conversion I came up with the following variations.
SELECT CustOptCnt.CustID , Min(CustPkgs.Pkg) As Pkg
FROM
(SELECT CustID, COUNT(*) As CustOptCnt
FROM CustOptions
GROUP BY CustID) As CustOptCnt
JOIN
(SELECT CustId, Pkg, COUNT(*) As OptMatch
FROM PkgOptions INNER JOIN CustOptions
ON CustOptions.Opt = PkgOptions.Opt
GROUP BY CustID, Pkg) As CustPkgs
ON CustOptCnt.CustId = CustPkgs.CustId
AND CustOptCnt.CustOptCnt = CustPkgs.OptMatch
GROUP BY CustOptCnt.CustID
also...
SELECT CustId, Min(Pkg) As Pkg FROM
(SELECT CustId, Pkg
FROM PkgOptions INNER JOIN CustOptions As CustOpts
ON CustOpts.Opt = PkgOptions.Opt
GROUP BY CustID, Pkg
HAVING COUNT(*) = (SELECT COUNT(*)
FROM CustOptions
WHERE CustID =CustOpts.CustID)
) As MultiCustPkgs
GROUP BY CustID
Not sure if there is any difference in performance between the two.
Jwiner... I will have to read about Shape Provider
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply