I have a table containing some data about overtime hours. I want to calculate the remaining hours that can be used. enter image description here
I am using the lag function to calculate the difference between previous row of number of left column and current row of number of used. My desired output is shown on desired tabledesired table. I want the column number of left overtime to be calculated like (15,5-1 for first row, second row 14,5-2, and so on) and to be grouped by Year abd Month
Can someone please help me?
>Solution :
Using the LAG function would be optimal here
SELECT
Year,
Month,
Number_of_Used,
Number_of_Left,
(Number_of_Left - LAG(Number_of_Used, 1, 0) OVER (PARTITION BY Year, Month ORDER BY Year, Month)) AS Remaining_Hours
FROM
YourTable;