Create new columns based on min and max values of a record with multiple rows

Transact-SQL
1

Hi everyone

I want to create new opening balance column based on min key_id and new closing balance column based on max key_id.

Original dataset
|cust_num|key_id|Opening Balance|Closing Balance|
|1|544684|5516.01|-223.99|
|1|548139|-223.99|-181.67|
|1|548141|-181.67|-181.67|
|1|548143|-181.67|-50.52|
|1|550954|-50.52|-50.52|

The desired result should be as follows:

cust_num key_id Opening Balance Closing Balance Opening Balance_new Closing Balance_new
1 544684 5516.01 -223.99 5516.01 0
1 548139 -223.99 -181.67 0 0
1 548141 -181.67 -181.67 0 0
1 548143 -181.67 -50.52 0 0
1 550954 -50.52 -50.52 -50.52

How would I write the SQL query?

Here is how I wrote it and I'm not getting correct results:

SELECT prod_inst_id AS cust_num
,key_id
,repay_start_balance AS 'Opening Balance'
,repay_end_balance AS 'Closing Balance'
,(
	SELECT repay_start_balance
	FROM fin_prod_inst_repay_Sched WITH (NOLOCK)
	WHERE start_date >= '2024-02-01'
		AND start_date <= '2024-02-29'
		AND key_id = (
			SELECT min(key_id)
			FROM fin_prod_inst_repay_Sched WITH (NOLOCK)
			WHERE start_date >= '2024-02-01'
				AND start_date <= '2024-02-29'
			)
	) 'Opening Balance_new'
,(
	SELECT repay_end_balance
	FROM fin_prod_inst_repay_Sched WITH (NOLOCK)
	WHERE start_date >= '2024-02-01'
		AND start_date <= '2024-02-29'
		AND key_id = (
			SELECT max(key_id)
			FROM fin_prod_inst_repay_Sched WITH (NOLOCK)
			WHERE start_date >= '2024-02-01'
				AND start_date <= '2024-02-29'
			)
	) 'Closing Balance_new'

FROM fin_prod_inst_repay_Sched WITH (NOLOCK)
WHERE start_date >= '2024-02-01'
AND start_date <= '2024-02-29'
GROUP BY prod_inst_id
,key_id
,repay_start_balance
,repay_end_balance
ORDER BY prod_inst_id

Thanks in advance
M

2

We need you to post directly usable test data. That is, CREATE TABLE and INSERT statement(s), not a splat of data on the screen.

3

make use of case expression and window function

select *,
        case when min(key_id) over (partition by cust_num) = key_id
             then Opening end as Opening_New,
        case when max(key_id) over (partition by cust_num) = key_id
             then Closing end as Closing_New
from   your_table
2 Likes
4

Thank you so much. It's working perfectly, see my results below:

image

Thanks again!

5

Noted. Will do so next time!

6

You should never ever use WITH (NOLOCK) if you are working with financial data, unless you want incorrect results.

NOLOCK Is Bad And You Probably Shouldn't Use It. - Brent Ozar UnlimitedĀ®

7

NOLOCK Is Bad And You Probably Shouldn't Use It. - Brent Ozar UnlimitedĀ®
[/quote]

If it's historical data -- i.e., it is never being modified or added to any way -- which this definitely seems to be (for the prior month), how can (NOLOCK) hurt?

1 Like
8

reason #3 in article

9

Reason #3 says "so that readers and writers can coexist." Again, there are NO writers of that data.

10

Maybe we don't understand each other, but based on the query I assume it's a normal table with an index. You can get incorrect results when the index is maintained. But maybe you are right and it's a really historical data and NOLOCK cannot hurt in that case. Based on the query I saw NOLOCK all over the place and thought that it would be a nice comment. But you are right I will never ever use never ever again :stuck_out_tongue: