SQL Server 2012 – Get Max Date by Comparing Total Amount

sql-server-2012

I have two tables tableA(S.no.,amount) and tableB(Id,paid_amt,trxn_dt)

   tableA                tableB
S.no.  amount     Id    paid_amt    trxn_dt  
1       500       1       200      2015-01-01   
2       500       2       300      2015-02-10
3       500       3       500      2015-04-10
4       500       4       1000     2015-10-10

Now i need a query to get the output as follows:

S.no.    amount          trxn_dt
 1        500           2015-02-10
 2        500           2015-04-10
 3        500           2015-10-10
 4        500           2015-10-10

I need to compare the sum of paid_amt from tableB to match each amount of tableA and get the max trxn_dt according to it. i.e (200+300)=500 so get max(trxn_dt) i.e 2015-02-10. Similarly for s.no.2 get get max(trxn_dt) as 2015-04-10 and for last one 1000 has been paid on 2015-10-10 so get max(trxn_dt) as 2015-10-10 for both s.no. 3 and 4. i.e i have to get the trxn_dt of the exact paid amount according to s.no. I am able to do this using resultset in java but it is time consuming for table containing lots of data.I would appreciate any kind of help. Thank you.

Best Answer

The requirements seem complex but if you calculate the running totals first, it's a simple comparison.

Find the first running_total from b that has covered the running_total from a:

WITH 
  rta AS
    ( SELECT [S.no], amount,
             running_total = SUM(amount) OVER 
                                 (ORDER BY [S.no]
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
      FROM tableA
    ),
  rtb AS
    ( SELECT trxn_dt, id,
             running_total = SUM(paid_amt) OVER 
                                 (ORDER BY trxn_dt, id
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
      FROM tableB
    )
SELECT
    a.[S.no], a.amount, b.trxn_dt
FROM rta AS a
  OUTER APPLY 
    ( SELECT TOP (1) b.trxn_dt
      FROM rtb AS b
      WHERE a.running_total <= b.running_total
      ORDER BY b.running_total, b.trxn_dt, b.id
    ) AS b ;

Tested at rextester.com.

Related Question