Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Sql calculating the difference between a previous row from one column and current row from another column

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?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading