How to get the latest balance before a certain date (t-sql)

Question

I ran into a problem where I am trying to get the latest balance before 30 April 2020.

Table Customer has the following columns:

CustomerId, CustomerName, CustomerCity, CustomerCurrentBalance.

Table Customer_Transaction_Entry has the following columns:

TransactionNumber, CustomerId, Country, Created, Amount, Details, Balance

This is my query so far:

select 
    dbo.Customer_Transaction_Entry.CustomerId,  
    dbo.Customer_Transaction_Entry.Country, 
    dbo.Customer_Transaction_Entry.Balance
from 
    dbo.Customer_Transaction_Entry
join
    dbo.Customer on Customer.CustomerId = Customer_Transaction_Entry.CustomerId
where 
    Customer_Transaction_Entry.Created < '2020-04-30'
order by 
    CustomerId

Problem here is I get all the transactions before this date. but I need the last one before this date as it is the most update one, and I have to show it as the current balance of the customer grouped by BalanceDate.

Here is sample data for dbo.Customer_Transaction_Entry:

TransactionNo CustomerId Country Created Amount Details Balance
10001   1   Country1    2020-01-01  80.000000   Purchase    80.000000
10002   1   Country1    2020-02-06  20.000000   Payment 60.000000
10003   1   Country1    2020-02-06  120.000000  Purchase    180.000000
10004   1   Country1    2020-02-23  20.000000   Payment 160.000000
10005   1   Country1    2020-04-06  20.000000   Payment 140.000000
10006   1   Country1    2020-05-06  120.000000  Purchase    260.000000
10007   1   Country1    2020-06-23  20.000000   Payment 240.000000
10008   4   Country1    2020-01-01  80.000000   Purchase    80.000000
10009   4   Country1    2020-02-06  20.000000   Payment 60.000000
10010   4   Country1    2020-02-06  120.000000  Purchase    180.000000
10011   4   Country1    2020-02-23  20.000000   Payment 160.000000
10012   4   Country1    2020-04-06  20.000000   Payment 140.000000
10013   4   Country1    2020-06-23  20.000000   Payment 248.000000
10014   21  Country2    2020-01-01  80.000000   Purchase    80.000000
10015   21  Country2    2020-02-06  20.000000   Payment 60.000000
10016   21  Country2    2020-02-06  120.000000  Purchase    180.000000
10017   21  Country2    2020-02-23  20.000000   Payment 160.000000
10018   21  Country2    2020-05-09  20.000000   Payment 140.000000
10019   21  Country2    2020-05-09  142.000000  Purchase    282.000000
10020   21  Country2    2020-07-23  20.000000   Payment 262.000000
10023   4   Country1    2020-04-06  128.000000  Purchase    268.000000

and for the smaller dbo.Customer:

CustomerId CustomerName CustomerCity CustomerCurrentBalance
1      CustomerName1    CustomerCity    NULL
2      CustomerName2    CustomerCity    NULL
3       CustomerName3   CustomerCity    NULL
4       CustomerName4   CustomerCity    NULL
6       CustomerName6   CustomerCity    NULL
13      CustomerName13  CustomerCity    NULL
21     CustomerName21   CustomerCity    NULL
22     CustomerName22   CustomerCity    NULL
23      CustomerName23  CustomerCity    NULL

The desired result should be:

BalanceDate CustomerId Country   Balance

2020-04-30   1         Country1  140
2020-04-30   4         Country1  268
2020-04-30   21        Country2  160

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Nov 06 '21 at 18:41

Stu · Accepted Answer · 2021-11-06T15:12:50.160

1

You can use row_number() window function to find the most recent row per customer:

with t as (
  select t.created as BalanceDate, t.CustomerId, t.Country, t.Balance,
    Row_Number() over(partition by t.CustomerId order by t.TransactionNo desc) rn
  from dbo.Customer c
  join dbo.Customer_Transaction_Entry t on t.CustomerId = c.CustomerId
  where t.Created < '20200430'
)
select BalanceDate, CustomerId, Country, Balance
from t
where rn=1

Note also how using aliases makes the query more compact and easier to read.

edited Nov 06 '21 at 15:12

answered Nov 06 '21 at 13:06

Stu

30,392
6
14
33

Came very close to a solution, but on one of the customers 4, the latest transaction doesnt come up even though it is before this date. I wish I could post here some photos as examples but SO doesnt let me upload anything here. Moreover, I have to update the latest balance from table Customer_Transaction_Entry to CustomerCurrentBalance column in Customer table, where initially CustomerCurrentBalance was all NULL – Ziya Mert Karakas Nov 06 '21 at 13:21
@MertKarakas you don't need to post any photos. The above was naturally untested without and sample data. If you add sample data and desired results to your question as text it will make it a lot easier and clearer to provide a working solution, see [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) – Stu Nov 06 '21 at 13:27
I have added sample data – Ziya Mert Karakas Nov 06 '21 at 14:41
@MertKarakas Looking at your sample data I don't see where your dates of 30/04 come from unless you are just hard-coding that in your output? However I've make a slight tweak to the above and it does return your 3 rows for customerId 1,4,21. – Stu Nov 06 '21 at 15:16

How to get the latest balance before a certain date (t-sql)

1 Answers1