Using a Function Recursively

  • dwtite

    SSC Veteran

    Points: 204

    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
  • dwtite

    SSC Veteran

    Points: 204

    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
  • dwtite

    SSC Veteran

    Points: 204

    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.

  • GRN

    Hall of Fame

    Points: 3210

    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

  • jwiner

    SSCrazy

    Points: 2241

    You should also be able to use the Shape Provideer and create a shaped recordset to show the relationships.

  • dwtite

    SSC Veteran

    Points: 204

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply