shawno shawno - 3 months ago 9
SQL Question

SQL Server aggregate over range of dates

I am using SQL Server 2014. I need to aggregate totals (sum total) over a range of dates that are partitioned or grouped by customer and location. The key is to get all the adjustment amounts and sum them up as they apply to a billing transaction date.

So all adjustments after the last bill date, but less than the next bill date need to get summed up and presented nicely along with the bill amount.

See example:

+------------------+------------+------------+------------------+--------------------+
| TRANSACTION_TYPE | CUSTOMERID | LOCATIONID | TRANSACTION DATE | TRANSACTION AMOUNT |
+------------------+------------+------------+------------------+--------------------+
| bill | 215 | 102 | 7/7/2016 | $100.00 |
| bill | 215 | 102 | 6/6/2016 | $121.00 |
| adj | 215 | 102 | 6/1/2016 | $22.00 |
| adj | 215 | 102 | 5/8/2016 | $0.35 |
| adj | 215 | 102 | 5/7/2016 | $5.00 |
| bill | 215 | 102 | 5/6/2016 | $115.00 |
| bill | 215 | 102 | 4/7/2016 | $200.00 |
| adj | 215 | 102 | 4/2/2016 | $4.35 |
| adj | 215 | 102 | 4/1/2016 | $(0.50) |
| adj | 215 | 102 | 3/28/2016 | $33.00 |
| bill | 215 | 102 | 3/28/2016 | $75.00 |
| adj | 215 | 102 | 3/5/2016 | $0.33 |
| bill | 215 | 102 | 3/3/2016 | $99.00 |
+------------------+------------+------------+------------------+--------------------+


What I would like to see is the following:

+------------------+------------+------------+------------------+-------------+-------------------+
| TRANSACTION_TYPE | CUSTOMERID | LOCATIONID | TRANSACTION DATE | BILL AMOUNT | ADJUSTMENT AMOUNT |
+------------------+------------+------------+------------------+-------------+-------------------+
| bill | 215 | 102 | 7/7/2016 | $100.00 | $- |
| bill | 215 | 102 | 6/6/2016 | $121.00 | $27.35 |
| bill | 215 | 102 | 5/6/2016 | $115.00 | $- |
| bill | 215 | 102 | 4/7/2016 | $200.00 | $36.85 |
| bill | 215 | 102 | 3/28/2016 | $75.00 | $0.33 |
| bill | 215 | 102 | 3/3/2016 | $99.00 | $- |
+------------------+------------+------------+------------------+-------------+-------------------+

Answer

You need to:

  • first conceive the table as two (virtual) sub-tables, on the TransactionType;
  • then use the LEAD function to get the date range of adjustments to be applied; and
  • finally perform a eft join.

Untested SQL below:

with
BillData as (
    select
        TransactionType,
        CustomerID,
        LocationID,
        TransactionDate,
        TransactionAmount,
        lead(TransactionDate, 1) over (partition by CustomerID 
                                       order by TransactionDate) as NextDate
    from @data bill
    where TransactionType = 'bill'
),
AdjData as (
    select
        CustomerID,
        TransactionDate,
        sum(TransactionAmount) as AdjAmount
    from @data adj
    where TransactionType = 'adj'
)
select
    bill.TransactionType,
    bill.CustomerID,
    bill.LocationID,
    bill.TransactionDate,
    sum(TransactionAmount)  as BillAmount,
    sum(AdjAmount)          as AdjAmount
from BillData bill
left join AdjData adj
    on adj.CustomerID = bill.CustomerID
   and bill.TransactionDate <= adj.TransactionDate
   and adj.TransactionDate < bill.NextDate
group by
    bill.TransactionType,
    bill.CustomerID,
    bill.LocationID,
    bill.TransactionDate
;
Comments