Get most recent payment records by date and value and group by customer

Transact-SQL
1

I need to get the most recent payment date for a customer record and show it's value as well. But if there are more than 1 record for a customer for that most recent date, the payment value should be a sum of those. Here is my basic query:

SELECT sa.CUCODE, sn.SLN_PROMISED_PAYMENT_DATE, sn.SLN_PROMISED_PAYMENT_VALUE 
FROM SL_NOTES sn
LEFT JOIN SL_NOTES_LINK sln on sln.SLN_LINK_PRIMARY = sn.SLN_PRIMARY
LEFT JOIN SL_ACCOUNTS sa on sa.CU_PRIMARY = sln.SLN_LINK_CUSTOMER

And some example data:

+--------+---------------------------+----------------------------+
| CUCODE | SLN_PROMISED_PAYMENT_DATE | SLN_PROMISED_PAYMENT_VALUE |
+--------+---------------------------+----------------------------+
| 100121 | 2021-01-28 00:00          | 5101.34                    |
| 100121 | 2021-01-28 00:00          | 7302.06                    |
| 100121 | 2021-02-26 00:00          | 3110.4                     |
| 100121 | 2021-03-19 00:00          | 554.83                     |
| 100121 | 2021-05-04 00:00          | 2782.58                    |
| 100121 | 2021-05-04 00:00          | 34156.66                   |
| 100123 | 2020-11-16 00:00          | 20437                      |
| 100123 | 2020-11-17 00:00          | 6774.84                    |
| 100123 | 2021-02-02 00:00          | 2040                       |
| 100123 | 2021-04-14 00:00          | 871.2                      |
+--------+---------------------------+----------------------------+

What I want to see is one record per customer for the most recent date, so for CUCODE 100121 I would want to see the most recent date (04/05/2021) and as there are 2 records for that date, the sum of those 2 records. And for CUCODE 100123 I would want to see the value of the most recent record (14/04/2021) as there is only 1 for that date. So the result for the example date should look like this:

+--------+---------------------------+----------------------------+
| CUCODE | SLN_PROMISED_PAYMENT_DATE | SLN_PROMISED_PAYMENT_VALUE |
+--------+---------------------------+----------------------------+
| 100121 | 2021-05-04 00:00          | 36939.24                   |
| 100123 | 2021-04-14 00:00          | 871.2                      |
+--------+---------------------------+----------------------------+

What is the best way to accomplish this? I have got as far as the query below but the results are not what I'm looking for as it is summing all values for that CUCODE and not just the ones for the most recent date:

SELECT sa.CUCODE
	,MAX(sn.SLN_PROMISED_PAYMENT_DATE) SLN_PROMISED_PAYMENT_DATE
	,SUM(sn.SLN_PROMISED_PAYMENT_VALUE) SLN_PROMISED_PAYMENT_VALUE
FROM SL_NOTES sn
LEFT JOIN SL_NOTES_LINK sln ON sln.SLN_LINK_PRIMARY = sn.SLN_PRIMARY
LEFT JOIN SL_ACCOUNTS sa ON sa.CU_PRIMARY = sln.SLN_LINK_CUSTOMER
GROUP BY sa.CUCODE

Any help would be most appreciated.

Many thanks
Martyn

2
click here for sample data create insert script

create table test_data
(
CUCODE int, SLN_PROMISED_PAYMENT_DATE date, SLN_PROMISED_PAYMENT_VALUE decimal(10,2)
)

insert into test_data select 100121 ,'2021-01-28 00:00',5101.34
insert into test_data select 100121 ,'2021-01-28 00:00',7302.06
insert into test_data select 100121 ,'2021-02-26 00:00',3110.4
insert into test_data select 100121 ,'2021-03-19 00:00',554.83
insert into test_data select 100121 ,'2021-05-04 00:00',2782.58
insert into test_data select 100121 ,'2021-05-04 00:00',34156.66
insert into test_data select 100123 ,'2020-11-16 00:00',20437
insert into test_data select 100123 ,'2020-11-17 00:00',6774.84
insert into test_data select 100123 ,'2021-02-02 00:00',2040
insert into test_data select 100123 ,'2021-04-14 00:00',871.2

;
WITH cte
     AS (SELECT cucode,
                Max(sln_promised_payment_date) AS mxdate
         FROM   test_data
         GROUP  BY cucode)
SELECT a.cucode,
       Sum(a.sln_promised_payment_value)
FROM   test_data a
       JOIN cte b
         ON a.cucode = b.cucode
            AND a.sln_promised_payment_date = b.mxdate
GROUP  BY a.cucode

image

2 Likes
3

Works perfectly, thanks Harish you've made my Friday!

Martyn

4

This will be less overhead:

SELECT
    CUCODE,
    MAX(SLN_PROMISED_PAYMENT_DATE) AS SLN_PROMISED_PAYMENT_DATE,
    SUM(SLN_PROMISED_PAYMENT_VALUE) AS SLN_PROMISED_PAYMENT_VALUE
FROM (
    SELECT *, DENSE_RANK() OVER(PARTITION BY CUCODE ORDER BY SLN_PROMISED_PAYMENT_DATE DESC) AS rank_num
    FROM test_data
) AS last_date_only
WHERE rank_num = 1
GROUP BY CUCODE
ORDER BY CUCODE
1 Like