?
Solved

Recursive function in SQL2005

Posted on 2007-10-19
2
Medium Priority
?
363 Views
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.
A
|_B
   |_C
      |_D
|_F
   |_C
      |_D
|_E
   |_C
      |_D


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'
FROM BOMTest t1
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.

Milena
0
Comment
Question by:mvoronkin
2 Comments
 
LVL 18

Accepted Solution

by:
PFrog earned 2000 total points
ID: 20114556
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

IF OBJECT_ID('spr_BOM') IS NOT NULL
      DROP PROCEDURE spr_BOM
GO

CREATE PROCEDURE spr_BOM
      (@PartNumber nvarchar(50))
AS
BEGIN

      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
      BEGIN
            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
      END

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

      DROP TABLE #tmpBOM

END
GO

EXEC spr_BOM 'A'
0
 

Author Comment

by:mvoronkin
ID: 20138281
Thank you very much for your help!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

862 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