CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Sk1X1
Sk1X1

Reputation: 1373

Cumulative substract throught multiple rows

I would like to substract one row from multiple rows. I need to get remaining Quantity (differentiated by BusTransaction_ID and Artikl, and ordered by X_PDateMonth$DATE), which is result of this substract: Result of two queries

And expected results:

expected results

Result can be with or without "zero rows". I don't know, how to accomplish this result. And will be better use some "stored procedure" or something, because it will be use to a pretty large data set?

Thanks for all replies.

Upvotes: 0

Views: 125

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Here is a solution that works by doing the following:

  1. Calculates the cumulative sums of the values in the first table.
  2. Based on the cumulative sum, determines the value to subtract.

The query looks like this:

select t.bustransaction_id, t.artikl, t.xpldate,
       (case when cumeq <= subt.quantity then 0
             when cumeq - t.quantity <= subt.quantity
             then cumeq - subt.quantity
             else t.quantity
        end) as newquantity       
from (select t.*,
             sum(quantity) over (partition by bustransaction_id, artikl order by xpldate) as cumeq
      from start_table t
     ) t left join
     subtract_table subt
     on t.bustransaction_id = subt.bustransaction_id and
        t.artikl = subt.artikl
order by t.bustransaction_id, t.artikl, t.xpldate;

Here is the SQL Fiddle (based on Brians).

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13233

This will give you the result with the 'zero rows' using analytic functions:

select x.*,
       case
         when subqty >= runner
         then 0
         when runner > subqty
          and lag(runner, 1) over( partition by bustransaction_id, artikl
                                   order by bustransaction_id, artikl, xpldate ) > subqty
         then quantity
         else runner - subqty
          end as chk
  from (select s.bustransaction_id,
               s.artikl,
               s.xpldate,
               s.quantity,
               sum(s.quantity) over(  partition by s.bustransaction_id, s.artikl
                                      order by s.bustransaction_id, s.artikl, s.xpldate ) as runner,
               z.quantity as subqty
          from start_table s
          join subtract_table z
            on s.bustransaction_id = z.bustransaction_id
           and s.artikl = z.artikl) x
 order by bustransaction_id, artikl, xpldate

Fiddle: http://sqlfiddle.com/#!6/20987/1/0

The CASE statement combined with the LAG function is what identifies the first "half-depleted" row, which is the biggest piece of your calculation.

In that fiddle I included my derived columns that were necessary to get what you wanted. If you don't want to show those columns you can just select those you need from the inline view, as shown here: http://sqlfiddle.com/#!6/20987/2/0

Upvotes: 1

Related Questions