Update SQL table in a loop with dynamic value from other table

Question

Table 1: #tdac

insert #tdac(secnum,bucketcode,acc,defqty)
select 'ax1','cor',1,'012',-100
insert #tdac(secnum,bucketcode,acc,defqty)
select 'ax1','cor',2,'012',-50

Table 2: #dac

insert #dac(secnum,bucketcode,acc,defqty)
select 'ax1','cor',0,'012',-125

I want to update dfqty in table 1 by subtracting defqty from table 2 beginning from oldest caseid.

If #tdac.defqty-#dac.defqty > 0, then 
    #tdac.defqty=0 and #dac.defqty = (#tdac.defqty-#dac.defqty) 
proceed to the next row. 
Continue this until the  value of (#tdac.defqty-#dac.defqty) becomes less than 0.

In my example I want to update table 1 as below. Please help

secnum  bucketcode  caseid  acc     defqty
ax1     cor         1       012     0     ((-100 -(-125)=25)>0, So make it 0)
ax1     cor         2       012    -25    ((-50 -(-25)=-25)<0, So make it -25)

Is this question solved? Do you need further help? Please allow me one hint: If this question is solved, it would be very kind of you, to tick the acceptance check below the (best) answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Once you've crossed the 15 points border yourself, you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! — Shnugo, Nov 17 '16 at 07:53

score 0 · Answer 1 · answered Nov 13 '16 at 21:32

I'm not sure if I fully understand your logic, but I think this can be done with a recursive CTE:

I added another case group to demonstrate, that this approach can deal with different groups in one go.

declare @tdac table(secnum varchar(100),bucketcode varchar(100),caseid int,acc varchar(100),defqty int);
insert @tdac(secnum,bucketcode,caseid,acc,defqty)
values('ax1','cor',1,'012',-100)
     ,('ax1','cor',2,'012',-50)
     ,('ax1','cor',3,'012',-150)
     ,('ax2','cor',1,'012',-100)
     ,('ax2','cor',2,'012',-100);

declare @dac table(secnum varchar(100),bucketcode varchar(100),caseid int,acc varchar(100),defqty int);
insert @dac(secnum,bucketcode,caseid,acc,defqty)
values('ax1','cor',0,'012',-125)
     ,('ax2','cor',0,'012',-150);

--the first CTE will find all distinct groups and add the value of @dac as start value to the set

WITH DistinctGroups AS
(
    SELECT t.secnum,t.bucketcode,t.acc,d.defqty
    FROM @tdac AS t
    LEFT JOIN @dac AS d ON d.secnum=t.secnum and d.bucketcode=t.bucketcode AND d.acc=t.acc
    GROUP BY t.secnum,t.bucketcode,t.acc,d.defqty
)

-the recursive CTE

,recursiveCTE AS
(
    --anchor: Get the rows with the minimal caseid from each group

    SELECT t.secnum,t.bucketcode,t.caseid,t.acc,t.defqty,CASE WHEN x.NewQty>0 THEN 0 ELSE x.NewQty END AS NewQty,CASE WHEN x.NewQty>0 THEN x.NewQty ELSE 0 END AS NewDiff
    FROM @tdac AS t
    INNER JOIN DistinctGroups AS gr ON t.secnum=gr.secnum AND t.bucketcode=gr.bucketcode AND t.acc=gr.acc
    CROSS APPLY(SELECT t.defqty-gr.defqty AS NewQty) AS x
    WHERE t.caseid=(SELECT MIN(caseid) FROM @tdac AS t2 WHERE t2.secnum=gr.secnum AND t2.bucketcode=gr.bucketcode AND t2.acc=gr.acc)

    UNION ALL

    --find the row with the next caseid and add the diff value of the previous row

    SELECT t.secnum,t.bucketcode,t.caseid,t.acc,t.defqty,CASE WHEN x.NewQty>0 THEN 0 ELSE x.NewQty END AS NewQty,CASE WHEN x.NewQty>0 THEN x.NewQty ELSE 0 END AS NewDiff
    FROM @tdac AS t
    INNER JOIN recursiveCTE AS r ON t.secnum=r.secnum AND t.bucketcode=r.bucketcode AND t.acc=r.acc
    CROSS APPLY(SELECT t.defqty+r.NewDiff AS NewQty) AS x
    WHERE t.caseid=r.caseid+1
)
select * 
from recursiveCTE
order by secnum,caseid

The result

ax1 cor 1   012 -100       0    25
ax1 cor 2   012  -50     -25     0
ax1 cor 3   012 -150    -150     0

ax2 cor 1   012 -100       0    50
ax2 cor 2   012 -100     -50     0

score 0 · Answer 2 · answered Nov 14 '16 at 10:59

0

UPDATE #tdac SET defqty = CASE WHEN ( (#tdac.defqty) -(F.defqty) ) > 0 THEN 0 ELSE ( (#tdac.defqty) -(F.defqty) ) END FROM #dac F WHERE F.secnum = #tdac.secnum

answered Nov 14 '16 at 10:59

Mansoor

4,061
1
17
27

score 0 · Answer 3 · answered Nov 14 '16 at 11:37

Build a running total for defqty (using sum() over()). Then update where the the total is in the range of the correction value.

update tdac
set defqty = case when tdac.total >= dac.total then 0 else tdac.total - dac.total end
from 
(
  select secnum, bucketcode, acc, sum(defqty) as total
  from #dac 
  group by secnum, bucketcode, acc
) dac
join
(
  select secnum, bucketcode, acc, caseid, defqty,
    sum(defqty) over (partition by secnum, bucketcode, acc order by caseid) as total
  from #tdac
  where defqty < 0
) tdac on  tdac.secnum = dac.secnum
       and tdac.bucketcode = dac.bucketcode
       and tdac.acc = dac.acc
       and tdac.total - tdac.defqty >= dac.total;

I suspect you want to join on secnum, bucketcode, and acc. If this is not the case change GROUP BY, PARTITION BY and ON clauses accordingly.

I wrote the query such as to be able to deal with several entries in #dac for the same secnum, bucketcode, and acc. If the three are unique (the table's primary key then supposedly), you don't need the aggregating subquery of course and can select directly from #dac instead.

Here is a stored test: http://rextester.com/VJJY97875.

I did not suggest `SUM() OVER()` because I thought, that this was not supported in SQL Server 2008. But the documentation is actually telling nothing about this restriction. Are you sure, that this works here? Have not chance to test this at the moment... — Shnugo, Nov 14 '16 at 14:27
@Shnugo: I cannot test that either. I looked up the docs and they say `sum() over()` was available in SQL Server 2008, so I used this. It seems they merely added missing windowing features (rows/range clause) in 2012. — Thorsten Kettner, Nov 14 '16 at 14:34
Searched a little and found [this](http://stackoverflow.com/q/19684249/5089204). In a comment below the question Martin Smith states, that `OVER(PARTITION BY ... ORDER BY)` was introduced with 2012... OP will have to try this out :-) — Shnugo, Nov 14 '16 at 14:41

Update SQL table in a loop with dynamic value from other table

3 Answers3