none
Expanding hierarchy tree with inheriting values

    Question

  • Hi all,
    I will try to explain.....please run the test code below and see results

    The rows that I also need are sub accounts in @AcctProd table that do not have a master Acct product...
    I'm generating the expanded sub accounts based on the Master for products but not sub accounts that are already in the @AcctProd table.

    These are the rows that are missing? ... I'm lost.... I assume I will need a 3rd union query in the CTE?

    3 879568 879571 Xaa102 2 3 879571 222 Red 5.77
    3 879568 879572 Xaa103 2 3 879572 222 Red 5.77
    3 879568 879573 Xaa104 2 3 879573 222 Red 5.77

    DECLARE @AcctProd TABLE
    ( TBId			INT IDENTITY(1,1)
     ,Account		VARCHAR(15)
     ,ProductState  CHAR(3)
     ,ProductId		INT
     ,ProductName	VARCHAR(50)
     ,ProductPrice  DECIMAL(8,2) 
    )
    
    INSERT INTO @AcctProd
    VALUES   ('879568','PA','786','Blue','1.87')   -- master account
    		,('879568','PA','545','Green','7.45')    -- master account
    		,('879568','PA','222','Red','5.77')    -- master account
    		
    		,('879571','PA','786','Blue','1.22')--sub account
    		,('879571','PA','545','Green','4.45') --sub account
    		
    		,('879572','PA','786','Blue','1.22')--sub account
    		,('879572','PA','545','Green','4.45') --sub account
    
    		,('879573','PA','786','Blue','1.45')--sub account
    		,('879573','PA','545','Green','4.75') --sub account
    		
    		,('547878','MA','78901','White','6.22')    -- master account	
    		
    
    
    DECLARE @Tree TABLE
    ( TrId INT IDENTITY(1,1)
     ,MasterAcct	INT
     ,SubAcct		INT
     ,AcctName		VARCHAR(50) 
     ,LeftTree		INT
     ,RightTree		INT
    )
    INSERT INTO @Tree
    VALUES   (547878,547878,'YOY',NULL,NULL)
    		,(879568,879568,'Xaa - Master',1,18)
    		,(879568,879571,'Xaa102',2,3) 
    		,(879568,879572,'Xaa103',4,5)
    		,(879568,879573,'Xaa104',6,7)
    		,(879568,879574,'Xaa105',8,9) 
    		,(879568,879575,'Xaa106',10,11) 
    		,(879568,879576,'Xaa107',12,13) 
    		,(879568,879577,'Xaa108',14,15) 
    		,(879568,879578,'Xaa109',16,17) 	
    		
    ;WITH CTE_TREE AS (	
    
    		-- Current what is in AcctProd table
    		SELECT [QueryNum] = 1,Tr.MasterAcct,Tr.SubAcct,Tr.AcctName,TR.LeftTree,TR.RightTree,AP.Account,AP.ProductId,AP.ProductName,AP.ProductPrice 
    		FROM @Tree Tr
    				INNER JOIN 	@AcctProd AP
    					ON TR.SubAcct = AP.Account	
    		UNION	
    
    		--Expand to get sub accounts not in AcctProd table from the @Tree table and, should have master products with prices.
    		SELECT  [QueryNum] = 2,Tr.MasterAcct,Tr.SubAcct,Tr.AcctName,TR.LeftTree,TR.RightTree,AP.Account,AP.ProductId,AP.ProductName,AP.ProductPrice 
    		FROM @Tree Tr
    				INNER JOIN @Tree Tr2 -- for sub accounts
    						ON TR.LeftTree BETWEEN Tr2.LeftTree AND Tr2.RightTree		
    				INNER JOIN 	@AcctProd AP
    					ON TR.MasterAcct = AP.Account	
    		WHERE NOT EXISTS(SELECT * FROM @AcctProd
    						 WHERE Tr.SubAcct = Account) )
    
    
    SELECT * 
    FROM CTE_TREE
    ORDER BY LeftTree,RightTree,ProductId


    Sword

    Wednesday, April 24, 2013 5:14 PM

Answers

  • I think I solved my own problem...this seems to return the correct results.

    Just needed to add to the not Exists clause...

     

    DECLARE @AcctProd TABLE
    ( TBId			INT IDENTITY(1,1)
     ,Account		VARCHAR(15)
     ,ProductState  CHAR(3)
     ,ProductId		INT
     ,ProductName	VARCHAR(50)
     ,ProductPrice  DECIMAL(8,2) 
    )
    
    INSERT INTO @AcctProd
    VALUES   ('879568','PA','786','Blue','1.87')   -- master account
    		,('879568','PA','545','Green','7.45')    -- master account
    		,('879568','PA','222','Red','5.77')    -- master account
    		
    		,('879571','PA','786','Blue','1.22')--sub account
    		,('879571','PA','545','Green','4.45') --sub account
    		
    		,('879572','PA','786','Blue','1.22')--sub account
    		,('879572','PA','545','Green','4.45') --sub account
    
    		,('879573','PA','786','Blue','1.45')--sub account
    		,('879573','PA','545','Green','4.75') --sub account
    		
    		,('547878','MA','78901','White','6.22')    -- master account	
    		
    
    
    DECLARE @Tree TABLE
    ( TrId INT IDENTITY(1,1)
     ,MasterAcct	INT
     ,SubAcct		INT
     ,AcctName		VARCHAR(50) 
     ,LeftTree		INT
     ,RightTree		INT
    )
    INSERT INTO @Tree
    VALUES   (547878,547878,'YOY',NULL,NULL)
    		,(879568,879568,'Xaa - Master',1,18)
    		,(879568,879571,'Xaa102',2,3) 
    		,(879568,879572,'Xaa103',4,5)
    		,(879568,879573,'Xaa104',6,7)
    		,(879568,879574,'Xaa105',8,9) 
    		,(879568,879575,'Xaa106',10,11) 
    		,(879568,879576,'Xaa107',12,13) 
    		,(879568,879577,'Xaa108',14,15) 
    		,(879568,879578,'Xaa109',16,17) 	
    		
    ;WITH CTE_TREE AS (	
    
    		-- Current what is in AcctProd table
    		SELECT [QueryNum] = 1,Tr.MasterAcct,Tr.SubAcct,Tr.AcctName,TR.LeftTree,TR.RightTree,AP.Account,AP.ProductState,AP.ProductId,AP.ProductName,AP.ProductPrice 
    		FROM @Tree Tr
    				INNER JOIN 	@AcctProd AP
    					ON TR.SubAcct = AP.Account	
    		UNION	
    
    		--Expand to get sub accounts not in AcctProd table from the @Tree table and, should have master products with prices.
    		SELECT  [QueryNum] = 2,Tr.MasterAcct,Tr.SubAcct,Tr.AcctName,TR.LeftTree,TR.RightTree,AP.Account,AP.ProductState,AP.ProductId,AP.ProductName,AP.ProductPrice 
    		FROM @Tree Tr
    				INNER JOIN @Tree Tr2 -- for sub accounts
    						ON TR.LeftTree BETWEEN Tr2.LeftTree AND Tr2.RightTree		
    				INNER JOIN 	@AcctProd AP
    					ON TR.MasterAcct = AP.Account	
    		WHERE NOT EXISTS(SELECT * FROM @AcctProd
    						 WHERE Tr.SubAcct = Account
    								 AND AP.ProductState = ProductState
    								 AND AP.ProductId = ProductId )
    							  
    				)
    
    
    SELECT * 
    FROM CTE_TREE
    ORDER BY LeftTree,RightTree,ProductId

    Thanks! 

    Sword

    • Marked as answer by gv-sword Wednesday, April 24, 2013 6:02 PM
    Wednesday, April 24, 2013 6:02 PM