SQL Deduct value from multiple rows

Question

I would like to apply total $10.00 discount for each customers.The discount should be applied to multiple transactions until all $10.00 used. Example:

CustomerID  Transaction Amount      Discount       TransactionID
1           $8.00                   $8.00          1
1           $6.00                   $2.00          2
1           $5.00                   $0.00          3
1           $1.00                   $0.00          4
2           $5.00                   $5.00          5
2           $2.00                   $2.00          6
2           $2.00                   $2.00          7
3           $45.00                  $10.00         8
3           $6.00                   $0.00          9

Answer 1

I usually like to setup a test environment for such questions. I will use a local temporary table. Please note, I made the data un-ordered since it is not guaranteed in a real life.

-- play table
if exists (select 1 from tempdb.sys.tables where name like '%transactions%')
drop table #transactions
go

-- play table
create table #transactions
(
  trans_id int identity(1,1) primary key,
  customer_id int,
  trans_amt smallmoney
)
go


-- add data
insert into #transactions
values
(1,$8.00),
(2,$5.00),  
(3,$45.00),     
(1,$6.00),   
(2,$2.00),     
(1,$5.00),   
(2,$2.00),   
(1,$1.00),        
(3,$6.00);      
go

I am going to give you two answers.

First, in 2014 there are new windows functions for rows preceding. This allows us to get a running total (rt) and a rt adjusted by one entry. Give these two values, we can determine if the maximum discount has been exceeded or not.

-- Two running totals for 2014
;
with cte_running_total 
as
(
select 
  *,

   SUM(trans_amt) 
   OVER (PARTITION BY customer_id
   ORDER BY trans_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND 
   0 PRECEDING) as running_tot_p0,

   SUM(trans_amt) 
   OVER (PARTITION BY customer_id
   ORDER BY trans_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND 
   1 PRECEDING) as running_tot_p1
from 
  #transactions
)
select 
   *
   ,
    case 
        when coalesce(running_tot_p1, 0) <= 10 and running_tot_p0 <= 10 then
            trans_amt 
        when coalesce(running_tot_p1, 0) <= 10 and running_tot_p0 > 10 then
            10 - coalesce(running_tot_p1, 0)
        else 0
     end as discount_amt
from cte_running_total;

Again, the above version is using a common table expression and advanced windowing to get the totals.

Do not fret! The same can be done all the way down to SQL 2000.

Second solution, I am just going to use the order by, sub-queries, and a temporary table to store the information that is normally in the CTE. You can switch the temporary table for a CTE in SQL 2008 if you want.

-- w/o any fancy functions - save to temp table
select *,
(
select count(*) from #transactions i
where i.customer_id = o.customer_id
and   i.trans_id <= o.trans_id
) as sys_rn,

(
select sum(trans_amt) from #transactions i
where i.customer_id = o.customer_id
and   i.trans_id <= o.trans_id
) as sys_tot_p0,

(
select sum(trans_amt) from #transactions i
where i.customer_id = o.customer_id
and   i.trans_id < o.trans_id
) as sys_tot_p1

into #results     
from #transactions o
order by customer_id, trans_id
go

-- report off temp table
select
    trans_id, 
    customer_id,
    trans_amt,
    case 
        when coalesce(sys_tot_p1, 0) <= 10 and sys_tot_p0 <= 10 then
            trans_amt 
        when coalesce(sys_tot_p1, 0) <= 10 and sys_tot_p0 > 10 then
            10 - coalesce(sys_tot_p1, 0)
        else 0
     end as discount_amt     
from #results
order by customer_id, trans_id
go

In short, your answer is show in the following screen shot. Cut and paste the code into SSMS and have some fun.

Answer 2

The query below keeps track of the running sum and calculates the discount depending on whether the running sum is greater than or less than the discount amount.

select 
    customerid, transaction_amount, transactionid,
    (case when 10 > (sum_amount - transaction_amount)
    then (case when transaction_amount >= 10 - (sum_amount - transaction_amount)
    then 10 - (sum_amount - transaction_amount) 
    else transaction_amount end)
    else 0 end) discount
from (
    select customerid, transaction_amount, transactionid,
    sum(transaction_amount) over (partition by customerid order by transactionid) sum_amount
    from Table1
) t1 order by customerid, transactionid

http://sqlfiddle.com/#!6/552c2/7

same query with a self join which should work on most db's including mssql 2008

select 
    customerid, transaction_amount, transactionid,
    (case when 10 > (sum_amount - transaction_amount)
    then (case when transaction_amount >= 10 - (sum_amount - transaction_amount)
    then 10 - (sum_amount - transaction_amount) 
    else transaction_amount end)
    else 0 end) discount
from (
    select t1.customerid, t1.transaction_amount, t1.transactionid,
    sum(t2.transaction_amount) sum_amount
    from Table1 t1
    join Table1 t2 on t1.customerid = t2.customerid
    and t1.transactionid >= t2.transactionid
    group by t1.customerid, t1.transaction_amount, t1.transactionid
) t1 order by customerid, transactionid

http://sqlfiddle.com/#!3/552c2/2

Answer 3

You can do this with recursive common table expressions, although it isn't particularly pretty. SQL Server stuggles to optimize these types of query. See Sum of minutes between multiple date ranges for some discussion.

If you wanted to go further with this approach, you'd probably need to make a temporary table of x, so you can index it on (customerid, rn)

;with x as (
  select
    tx.*,
    row_number() over (
      partition by customerid 
      order by transaction_amount desc, transactionid
    ) rn
  from
    tx
), y as (
  select
    x.transactionid,
    x.customerid,
    x.transaction_amount,
    case 
      when 10 >= x.transaction_amount then x.transaction_amount
      else 10
    end as discount,
    case 
      when 10 >= x.transaction_amount then 10 - x.transaction_amount
      else 0
    end as remainder,
    x.rn as rn
  from
    x
  where
    rn = 1
  union all
  select
    x.transactionid,
    x.customerid,
    x.transaction_amount,
    case
      when y.remainder >= x.transaction_amount then x.transaction_amount
      else y.remainder
    end,
    case
      when y.remainder >= x.transaction_amount then y.remainder - x.transaction_amount
      else 0
    end,
    x.rn
  from
    y
        inner join
    x
        on y.rn = x.rn - 1 and y.customerid = x.customerid
  where
    y.remainder > 0
)
update
  tx
set
  discount = y.discount
from
  tx
    inner join
  y
    on tx.transactionid = y.transactionid;

Example SQLFiddle