Tag: sql

You have only been assigned the task of working on an old reservation system that runs on SQL2008r2. Yes, the support for this has ended or soon it will be, but it is as it is.

The vacation hours earned are stored in a table and the reserved Hoilday Hours are stored in another. Due to some limitations of the system, you can reserve more hours that are actually obtained. So my task is to help audit the numbers.

So here is a set of sample data: the expected result is the column that I want to obtain.

Date EmpId HrsEarned HrsReserved Expected result
20160613 100 10.00 -31.70 -21.70 - total <0 so deduct from next row
20160713    100     52.70       -20.00      11.00 -- total >= 0 so do not deduce from the next row
20170613 100 1.00 -11.50 -10.50
20170713 100 16.70 0.00 6.20
20180713 100 5.00 -16.70 -11.70
20180713 100 112.70 -100.00 1.00
20190613 100 0.00 -0.50 -0.50
20190713 100 6.70 0.00 6.20

The rule here is that when the total of HrsEarned and HrsBooked is <0, then deduct this HrsEarned total from the next row. Keep a cumulative total if this results in "Expected result" < 0 on the following row. If the total of HrsEarned and HrsBooked is >= 0, then do not deduct from HrsEarned from the next row.

This is the solution I came up with, this works for the previous example and I get the expected result.

; With cteDATA
AS(
SELECT [ReqId]= 1,[Date]= & # 39; 20160613 & # 39 ;, HrsEarned = 10, HrsBooked = -31.7,[EmpId]= 100 UNION ALL
SELECT 2, & # 39; 20160713 & # 39 ;, 52.7, -20,100 UNION ALL
SELECT 3, & # 39; 20170613 & # 39 ;, 1, -11.5,100 UNION ALL
SELECT 4, & # 39; 20170713 & # 39 ;, 16.7,0,100 UNION ALL
SELECT 5, & # 39; 20180613 & # 39 ;, 5, -16.7,100 UNION ALL
SELECT 6, & # 39; 20180713 & # 39 ;, 112.7, -100,100 UNION ALL
SELECT 7, & # 39; 20190613 & # 39 ;, 0, -0.5,100 UNION ALL
SELECT 8, & # 39; 20190713 & # 39 ;, 6.7,0100
), cteAddRN
AS(
SELECT
RN = ROW_NUMBER () OVER
(
PARTITION OF C.EmpId
ORDER OF C.[Date]
        )
, C.ReqId
,DO.[Date]
        , C.HrsLoaded
, C.HrsReserved
, C.EmpId
SINCE
cteDATA C
)
SELECT
R.EmpId
, R.[Date]
 , R.HrsEarned
, R.HrsReservado
, ExpectedOutcome =
CASE
WHEN R.RN = 1
THEN R.Hrs Booked + R.HrsEarned
WHEN R.RN <> 1 Y ([LAG].Hrs Reserved + [LAG].HrsReceived) <0
SO ([LAG].Hrs Reserved + [LAG].HrsEarned) + (R.HrsEarned + R.HrsBooked)
WHEN R.RN <> 1 Y ([LAG].Hrs Reserved + [LAG].HrsEarned)> = 0
THEN R.Hrs Booked + R.HrsEarned
FINISH
SINCE
cteAddRN R
JOIN LEFT
cteAddRN [LAG]  IN R.EmpId = [LAG].EmpId
And R.ReqId-1 = [LAG].ReqId

However, this fails in cases where, after deducting the total from the previous row, the total of the current row is <0.

Sample of the data set that shows this.

Date EmpId HrsEarned HrsReserved
20160613 100 10.00 -31.70
20160713 100 40.70 -20.00 - Change of Hours: from 52.7 to 40.7
20170613 100 1.00 -11.50
20170713 100 16.70 0.00
20180713 100 5.00 -16.70
20180713 100 112.70 -100.00
20190613 100 0.00 -0.50
20190713 100 6.70 0.00    

The previous query gives the incorrect results

EmpId Date HrsEarned HrsReserved ExpectedOutcome
100 20160613 10.0 -31.7 -21.7
100 20160713 40.7 -20.0 -1.0 --The cumulative total here is <0
100 20170613 1.0 -11.5 -10.5 - It is expected that here it is -11.5
100 20170713 16.7 0.0 6.2 - makes the following rows incorrect
100 20180613 5.0 -16.7 -11.7
100 20180713 112.7 -100.0 1.0
100 20190613 0.0 -0.5 -0.5
100 20190713 6.7 0.0 6.2