About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Friday, February 12, 2010

Reports - Ways of Looking at Data – Part IV: Hierarchies - Update

     While commenting on a post on User Defined Functions from SQL Server Programmers Blog I realized that I could use a multi-statement table-valued function in order to simulate recursive processing for ‘Identifying the top-most parent’ problem presented in Reports - Ways of Looking at Data – Part IV: Hierarchies. Some years back, I needed to create the BOM structure for a report based on IFS-iV ERP system, the data being stored in a SQL Server 2000-based data warehouse. Then, given the fact that common table expressions were introduced only on SQL Server 2005, I used a similar technique as the below one though based on temporary tables, if I’m remembering correctly. Then, like now, I was just iterating through the various levels of the BOM inserting data in the temporary table, this approach having better performance than creating a query self-referencing the same logic multiple times, the logic scaling quite well on multiple millions of records.
-- BOM Top Most Items
CREATE FUNCTION dbo.BOMTopMostItems(
   @ProductID  int ,
   @Date datetime)

RETURNS
@BOM TABLE(
    BillOfMaterialsID int NULL,
   ProductAssemblyID int NULL,
   ComponentID int NOT NULL,
   ModifiedDate datetime NOT NULL,
   Level smallint NOT NULL)

AS
BEGIN
DECLARE
@NoRecords int -- stored the number of Products part of an Assembly

DECLARE @Level smallint
SET @Date = ISNull(@Date, GetDate())
SET @Level = 0 -- initializing the routine
INSERT @BOM(BillOfMaterialsID, ProductAssemblyID, ComponentID, ModifiedDate, Level)
SELECT BillOfMaterialsID, ProductAssemblyID, ComponentID, ModifiedDate, @Level
FROM Production.BillOfMaterials
WHERE ComponentID = @ProductID
AND DATEDIFF(d, IsNull(EndDate, GetDate()), @Date)<=0

SELECT @NoRecords = count(1)
FROM @BOM
WHERE ProductAssemblyID IS NOT NULL

WHILE  (IsNull(@NoRecords, 0)>1)
BEGIN
-- inserting next level
INSERT @BOM(BillOfMaterialsID, ProductAssemblyID, ComponentID, ModifiedDate, Level)

SELECT BOM.BillOfMaterialsID, BOM.ProductAssemblyID, BOM.ComponentID, BOM.ModifiedDate, @Level+1
FROM @BOM B 
JOIN Production.BillOfMaterials BOM
ON B.ProductAssemblyID = BOM.ComponentID
AND DATEDIFF(d, IsNull(BOM.EndDate, GetDate()), @Date)<=0
WHERE Level = @Level

SET @Level = @Level+1


SELECT @NoRecords = count(1)
FROM @BOM
WHERE ProductAssemblyID IS NOT NULL

AND Level = @Level
END
-- deleting the intermediate levels
DELETE 
FROM @BOM
WHERE ProductAssemblyID IS NOT NULL
RETURN
END


     As can be seen I’m first inserting the BOM information of the Product searched, then based on these data I’m taking and inserting the next level, stopping when the top-most items where identified, here ProductAssemblyID IS NOT NULL, in the end deleting the intermediate levels. The same approach could be used to generate the whole hierarchy, in this case starting from the top.

     Here is an example based on the above function, I used NULL in the second parameter because the @date parameter is set to the current date if not provided.


SELECT *  FROM dbo.BOMTopMostItems(486, NULL) 

  When checking the data returned I observed that for the given ProductID the same BillOfMaterialsID appears more than once, this because, as can be seen by generating the whole BOM for one of the respective duplicated ComponentID, the same ProductID could appear in a BOM at different levels or at the same level but belonging to different assemblies. Therefore in order to get only the list of top-most items you could use a DISTINCT:
SELECT DISTINCT ComponentID
FROM dbo.BOMTopMostItems(486, NULL)

    On the other side if we want to remove the duplicates resulted from Products appearing at the same level of the same BOM but for different assemblies, could be used a DISTINCT when inserting into the @BOM table or, even if not recommended, use a DISTINCT when calling the function:
SELECT DISTINCT BillOfMaterialsID, ProductAssemblyID, ComponentID, ModifiedDate, Level
FROM dbo.BOMTopMostItems(486, NULL)
 


No comments: