Use LAG function on calculated Columns in SQL Server

Question

I want to calculate the opening/closing amount of the month on the condition. I am using calculative columns for the same. In My case, the closing of prev month is the Opening for the next month, so I try to use the LAG function. but not able to achieve results.

In the first month the OpeningAmt will be YearAmt

My Table structure is as Follows

emp_code	year1	year2	col_code	paidin	YearAmt	Increment	Used
330	202204	202303	a3	202204	15000.00	1250.00	100
330	202204	202303	a3	202205	15000.00	1250.00	100
330	202204	202303	a3	202206	15000.00	1250.00	100
330	202204	202303	a3	202207	15000.00	1250.00	100
330	202204	202303	a3	202208	15000.00	1250.00	100

and my expected output as follows

emp_code	year1	year2	col_code	paidin	YearAmt	Increment	Used	OpeningAmt	ClosingAmt
330	202204	202303	a3	202204	15000.00	1250.00	100	15000.00	16150.00
330	202204	202303	a3	202205	15000.00	1250.00	100	16150.00	17300.00
330	202204	202303	a3	202206	15000.00	1250.00	100	17300.00	18450.00
330	202204	202303	a3	202207	15000.00	1250.00	100	18450.00	19600.00
330	202204	202303	a3	202208	15000.00	1250.00	100	19600.00	20750.00

Attempted Query:

SELECT *,

CASE WHEN year1 = MonthYear THEN NULL ELSE 
LAG(StartMonthClosing,1) OVER (ORDER BY emp_code, col_code, MonthYear) 
END OtherMonthOpening

FROM (
SELECT emp_code, year1,year2,col_code, MonthYear ,YearAmt,  Increment,  Used,
CASE WHEN year1 = MySalaryTable.paidin THEN YearAmt ELSE NULL END StartMonthOpening,
CASE WHEN year1 = MySalaryTable.paidin THEN YearAmt + Increment - Used ELSE NULL END StartMonthClosing
FROM MyreimTable
INNER JOIN MySalaryTable ON MyreimTable.emp_code = MySalaryTable.emp_code
) 
AS P2

@Larnu, I am not able to use lag function in case statement on the calculated columns — amol rogye
– amol rogye, Commented Jul 20, 2022 at 7:54
Why are you not able to? And you still haven't included your attempt in the question with an edit. — Thom A
– Thom A ♦, Commented Jul 20, 2022 at 7:54

GarethD · Accepted Answer · 2022-07-20 08:17:39Z

1

Since you need data from all previous rows, not just the previous row, you need to use SUM() OVER() rather than LAG() OVER(). The following gives the desired result from your sample data:

SELECT  t.emp_code, 
        t.year1, 
        t.year2, 
        t.col_code, 
        t.paidin, 
        t.YearAmt, 
        t.Increment, 
        t.Used,
        OpeningAmt = SUM(CASE WHEN t.year1 = t.paidin THEN t.YearAmt ELSE 0 END + t.Increment - t.Used) 
                            OVER(PARTITION BY t.Emp_code, t.col_code
                                ORDER BY t.paidin ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
        ClosingAmt = SUM(CASE WHEN t.year1 = t.paidin THEN t.YearAmt ELSE 0 END + t.Increment - t.Used) 
                        OVER(PARTITION BY t.Emp_code, t.col_code ORDER BY t.paidin)
FROM    dbo.YourTable AS t;

Example on db<>fiddle

answered Jul 20, 2022 at 8:17

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlieface Over a year ago

In ClosingAmt you should use ROWS UNBOUNDED PRECEDING in case there are duplicate paidin values. It's also faster. The default is actually RANGE UNBOUNDED PRECEDING which is subtly different

Collectives™ on Stack Overflow

Use LAG function on calculated Columns in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related