none
Recursive Query with Parent - Child combination

    질문

  • Hi -

    Can someone please help me with the SQL code for the following issue: 

    I am looking to compute CalcField in the table below. Right now I have computed it using excel with this formula:

    =IF(ChildCompany ="NULL",VLOOKUP(ChildCompany ,FactorTable,2,FALSE),SUMIF(ParentCompany,ChildCompany,CalcField)*Allocation*Factor)

    Basically, when child is not available, I am bringing in Cost from the Factor table, otherwise I am summing together all the combinations for the child (when it's a parent) and multiplying it with allocation and factor. 

    Below are the two base tables:

    CREATE TABLE Temp ( [ParentCompany] VARCHAR, [ChildCompany] VARCHAR,Allocation FLOAT, Factor FLOAT, CalcField FLOAT)
    
    INSERT INTO Temp ( [ParentCompany] , [ChildCompany] ,Allocation ,Factor, CalcField )
     Values
    ('1',	'2'	,100, 0.95	,9396.537245),
    ('2',	'3',	100, 1.05,	10385.64643),
    ('3',	'4',	100,1	, 3891.091837),
    ('3',	NULL,	100,1,	6000),
    ('4',	'5'	,   40 , 1.02   , 3968.913674),
    ('5',	NULL,	0	, 0.87, 4000),
    ('5',	'6'	,100, 0.87	,2836.956522),
    ('5',	'7'	,100, 0.87	,1626.168224),
    ('6',	NULL,	60	,  0.92, 3000),
    ('7',	NULL,	100, 1.07,	2000)

    CREATE TABLE Factor (Company varchar, Cost float)
    INSERT INTO Factor Values
    ( '1',8000),
    ('2',7000),
    ('3',6000),
    ('4',5000),
    ('5',4000),
    ('6',3000),
    ('7',2000)
    

    Thanks in advance

    2018년 6월 15일 금요일 오전 7:54

답변

  • Yes, that is right. But we also need to use the same calcfield when we are doing the calculation for a parent, for example 4 is a parent of 5 so, we need to sum calcfield for 5 (parent 5) and that will be the value of calcfield for (4,5) (parent,child) combo 

    check this,

     ;with cte
     as
     (
    SELECT   ParentCompany, ChildCompany, Allocation, Factor ,cast(f.Cost as decimal(10,5)) as tempCalcField  
    FROM Temp t inner join Factor f
    on t.ParentCompany= f.Company where ChildCompany is null  
    UNION ALL
    select t.ParentCompany, t.ChildCompany, t.Allocation, t.Factor,  cast(c.tempCalcField* (t.Allocation/100.0)*t.Factor as decimal(10,5))
     
     from cte c
    inner join Temp t 
    on c.ParentCompany =t.ChildCompany
     
     )
     select ParentCompany,ChildCompany,Allocation, Factor,SUM(tempCalcField) as CalcField   from cte 
     group by ParentCompany,ChildCompany,Allocation, Factor

    For 5 > 6 combination its 3000 * 0.87 , which gives 2610.00. But your post says 2836.956522 , not sure how u got that. 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • 답변으로 표시됨 P22D88 2018년 6월 18일 월요일 오전 5:48
    2018년 6월 15일 금요일 오후 7:41

모든 응답

  • And what is the expected results from the sample data? I'm afraid that I don't grok what the Excel thing might be doing.

    2018년 6월 15일 금요일 오전 8:20
  • Your question combine two products, Excel and SQL Server. We can try to help you with SQL Server here, but not so much with Excel. Are you able to determine where you believe the issue is? That is, is it failing in SQL Server, or is it failing in Excel?
    2018년 6월 15일 금요일 오전 10:51
    중재자
  • Looks like something in this path,

     select Company, case when finaCal is null then f.Cost else finaCal end  from Factor f
     outer apply
     (
     select (SUM (t.Allocation)* max(t.Factor)*max(t.CalcField)) from Temp t 
     where t.ParentCompany= f.Company and t.ChildCompany is not null 
     group by t.ParentCompany
     ) cal(finaCal)

    A suggestion, I see that Allocation and Factor are part of Temp table, but by the values feel that they are depending on Parent company, if so they should be part of Factor table


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 6월 15일 금요일 오후 5:20
  • Hi Erland,

    Basically, I need to calculate the "calcfield" in the temp table, . I was not sure how to do this calculation in SQL so i did it in excel, and inserted the results in the Temp table. But to put it in words I need to check if childcompany is NULL in temp table, then take the cost from factor table(company and parentcompany are the same). If childcompany is not NULL then take the summation of CalcField, multiply it the factor for child company. So if we start with parentcompany = 7 the calcfield will be 2000, then 6 will be 3000, then for parentcompany 5 (this has 2 children 6 and 7) , so for 5,7 combination the calculation will be (2000*.87) and 5,6 the calculation will be (3000*.87), for 5,NULL it will be 4000, for 4,5 it will be the summation of the calcfield for Parent 5 (4000+2160+1740)*40%*1.02 and so on.

    2018년 6월 15일 금요일 오후 6:03
  • Select parentcompany,childcompany
     ,case when t.childcompany IS NULL THEN f.cost
     else (f1.cost*allocation*factor)/100
    	   end as calc
     FROM Temp t
     LEFT JOIN Factor f on t.parentcompany = f.company 
      LEFT JOIN Factor f1 on t.childcompany = f1.company 
    This is the query i have for now, it shows correct results until 5, NULL combination (going bottom to top), but need to figure out, how to do summation of calcfield when (all calc values for parent 5) the combination is 4,5 and then going up from there(3,4), (2,3) and (1,2).
    2018년 6월 15일 금요일 오후 6:25
  • Thanks Sarat , but this is not giving me the right result. Sorry I know I may have not explained the issue at hand ver well.
    2018년 6월 15일 금요일 오후 6:27
  • Thanks Sarat , but this is not giving me the right result. Sorry I know I may have not explained the issue at hand ver well.
    Now I understand that the CalcFeild in Temp table is your expected output..am right?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 6월 15일 금요일 오후 7:30
  • Yes, that is right. But we also need to use the same calcfield when we are doing the calculation for a parent, for example 4 is a parent of 5 so, we need to sum calcfield for 5 (parent 5) and that will be the value of calcfield for (4,5) (parent,child) combo 
    2018년 6월 15일 금요일 오후 7:37
  • Yes, that is right. But we also need to use the same calcfield when we are doing the calculation for a parent, for example 4 is a parent of 5 so, we need to sum calcfield for 5 (parent 5) and that will be the value of calcfield for (4,5) (parent,child) combo 

    check this,

     ;with cte
     as
     (
    SELECT   ParentCompany, ChildCompany, Allocation, Factor ,cast(f.Cost as decimal(10,5)) as tempCalcField  
    FROM Temp t inner join Factor f
    on t.ParentCompany= f.Company where ChildCompany is null  
    UNION ALL
    select t.ParentCompany, t.ChildCompany, t.Allocation, t.Factor,  cast(c.tempCalcField* (t.Allocation/100.0)*t.Factor as decimal(10,5))
     
     from cte c
    inner join Temp t 
    on c.ParentCompany =t.ChildCompany
     
     )
     select ParentCompany,ChildCompany,Allocation, Factor,SUM(tempCalcField) as CalcField   from cte 
     group by ParentCompany,ChildCompany,Allocation, Factor

    For 5 > 6 combination its 3000 * 0.87 , which gives 2610.00. But your post says 2836.956522 , not sure how u got that. 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • 답변으로 표시됨 P22D88 2018년 6월 18일 월요일 오전 5:48
    2018년 6월 15일 금요일 오후 7:41
  • Thank You Sarat, the solution works perfectly.
    2018년 6월 18일 월요일 오전 5:49
  • Hi Sarat,

    I have a follow up on the above question. Let me know if this needs to be a new question and i can post it.

    So taking the above table as the output after running the CTE, I now need to do another recursive summation.  Basically I need two columns Total1 and total2. Total2 (when parent) = 0, Total1( when parent) = sum(total2); then for child the formula will be Total1( when child) = sum(total2) and total2 (when child) = sum(allocation and calcfield). In other words, in the previous problem, we needed to do a vertical summation, but in this case there needs to be a vertical plus horizontal summation. Using the above output table as base table the output of the new calculation should be as shown. Here the total2(for parent) will always be 0, total1(parent) is sum(total2) (sum of total2 for 7, NULL), same calc for total1 and total2 for 6 (6,NULL); then total1(for 5,7) is sum(total1 for 7,NULL) and total2(for 5,7) is sum(allocation+calcfield+total1); then total1(5,6) is sum(total1 for 6,NULL) and total2(for 5,6) is sum(allocation+calcfield+total1); thne total1(5,NULL) is sum(total2 for 5,6 and 5,7); then total1(4,5) is sum(total1 for 5,NULL + 5,6 + 5,7) and total2(4,5) is sum(total1 for 4,5) and so on.. Please let me know if further explanation is required.

    ParentCompany   ChildCompany  Allocation   CalcField   total1 total2
    1 2 100  9383.28  12606.8  22090.08
    2 3 100 9877.14 12606.8  22583.94
    3              NULL 100 6000 8056.8 0
    3 4 100 3406.8 4550 8056.8
    4 5 40 3406.8 4550 7996.8
    5              NULL 0 4000 4550 0
    5 6 100 2610 0 2710
    5 7 100 1740 0 1840
                 NULL 60 3000 0 0
    7              NULL 100 2000 0 0


    NULL

    • 편집됨 P22D88 2018년 6월 25일 월요일 오전 4:13
    2018년 6월 25일 월요일 오전 4:03
  • Hi Sarat,

    I have asked the same question in another thread here as a new question. So, i am closing this thread. Thanks for your help.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f468ca00-ae2a-43af-9f53-41d477ffb880/recursive-summation-sql-query-when-one-calculated-field-depends-on-the-other-calculated-field-and?forum=transactsql

    2018년 6월 25일 월요일 오후 6:45