Recursive function in SQL2005

Posted on 2007-10-19
Last Modified: 2010-03-19
I'm trying to solve the following SQL problem:
I have the  BOMTest table in the SQL Server 2005 database. I need to calculate Quantity for each part.

Example details:
Main part number A is made of parts B, E and F.  Parts B, E and F are made of the part number C. The part number C is made of the part number D. The part number B has quantity of 2.

Table Stucture:
ParentPartNumber nvarchar(50)      
PartNumber      nvarchar(50)
Quantity      float
Hierarchy      varchar(1000)
Table Data:
A      B      2      A.B
B      C      1      A.B.C
C      D      1      A.B.C.D
A      E      1      A.E
E      C      1      A.E.C
F      C      1      A.F.C
C      D      1      A.F.C.D
C      D      1      A.E.C.D
A      F      1      A.F
The result of my calculation should look like this
                     A                   1
      B      2      
      C      4      
      D      4      
      E      1      
      F      1      
I have tried to use recursive function. However, my results are far from what I need it to be.
My SQL statement:
With myTable1(ParentPartnumber, PartNumber, Quantity) as
(SELECT      distinct t1.parentpartnumber, t1.partnumber, isnull((Select  SUM(Quantity) from BOMTEST t2 Where t2.partnumber = t1.parentpartnumber),t1.quantity)AS 'Quantity'
union All
Select BOMTest.parentpartnumber, BOMTest.partnumber, BOMTest.Quantity
From BOMTest  join myTable1  on myTable1.partnumber = BOMTest.Parentpartnumber)

Select  parentpartnumber, partnumber,Quantity
From myTable1

Any help is greatly appreciated.

Question by:mvoronkin
    LVL 18

    Accepted Solution

    Your existing table structure makes it virtually impossible to use recursive functions or CTEs, you're going to have to use a stored procedure to do this.

    This should do what you want

          DROP PROCEDURE spr_BOM

          (@PartNumber nvarchar(50))

          CREATE TABLE #tmpBOM
                (PartNumber nvarchar(50),
                Quantity float,
                Processed int)

          INSERT INTO #tmpBOM
                SELECT @PartNumber, 1, 0

          DECLARE @NewCount int
          SET @NewCount=1

          WHILE @NewCount>0
                UPDATE #tmpBOM SET Processed = 1 WHERE Processed = 0

                INSERT INTO #tmpBOM
                      SELECT B.PartNumber, B.Quantity * T.Quantity, 0
                            FROM #tmpBOM T INNER JOIN (SELECT DISTINCT ParentPartNumber, PartNumber, Quantity FROM BOMTest) B
                                  ON T.PartNumber = B.ParentPartNumber
                            WHERE T.Processed=1

                UPDATE #tmpBOM SET Processed = 2 WHERE Processed = 1
                SELECT @NewCount = Count(*) FROM #tmpBOM WHERE Processed = 0

          SELECT PartNumber, Sum(Quantity) AS Quantity FROM #tmpBOM GROUP BY PartNumber

          DROP TABLE #tmpBOM


    EXEC spr_BOM 'A'

    Author Comment

    Thank you very much for your help!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now