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

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

CodePudding user response：

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.