Use LAG function on calculated Columns in SQL Server-CodePudding

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

CodePudding user response：

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