SQL Server : how to select first and last value within a date range grouped by user

user_976399 0
Viewed 59

I have the following table (called report) in SQL Server:

+---------+------------------------+---------+
| User_id | timestamp              | balance |
+---------+------------------------+---------+
|    1    |2021-04-29 09:31:10.100 |   10    |
|    1    |2021-04-29 09:35:25.800 |   15    |
|    1    |2021-04-29 09:36:30.550 |   5     |
|    2    |2021-04-29 09:38:15.009 |   100   |
+---------+------------------------+---------+

I would like to group the opening balance, closing balance and net movement of all users between a date period (only if the user has a record within that date range)

I would like the following output if my query asked for everything between 2021-04-29 and 2021-04-30

+---------+-----------------+-----------------+--------------+
| User_id | opening_balance | closing_balance | net_movement |
+---------+-----------------+-----------------+--------------+
|    1    |       10        |       5         |     -5       |
|    2    |       100       |       100       |      0       |
+---------+-----------------+-----------------+--------------+

I am unclear on what best approach to take, should I be making multiple queries for the TOP 1 of the balance ([TOP 1 order by timestamp] AND [TOP 1 order by timestamp DESC]) and I am unclear on how to calculate the net movement if I do manage to get the values.

Any clues or nudges in the right direction would be most appreciated.

sql-server sql
1 Answers

You can use conditional aggregation:

select user_id,
       max(case when seqnum = 1 then balance end) as opening,
       max(case when seqnum_desc = 1 then balance end) as closing,
       sum(case when seqnum = 1 and seqnum_desc = 1 then 0
                when seqnum = 1 then - balance
                when seqnum_desc = 1 then balance
           end) as movement
from (select r.*,
             row_number() over (partition by user_id order by timestamp) as seqnum,
             row_number() over (partition by user_id order by timestamp desc) as seqnum_desc
      from report r
     ) r
group by user_id;

You can also do this without explicit aggregation:

select distinct user_id,
       first_value(balance) over (partition by user_id order by timestamp) as opening,
       first_value(balance) over (partition by user_id order by timestamp desc) as closing,
       (first_value(balance) over (partition by user_id order by timestamp desc) -
        first_value(balance) over (partition by user_id order by timestamp)
       ) as movement
from t;

Here is a db<>fiddle.

I would expect the two methods to have similar performance. I find that the first is clearer on the intent, though.

user_11440350
Related