January 13, 2016 at 5:34 am
I have a leave table in the following format:
IF OBJECT_ID('tempdb..#Leave') IS NOT NULL
DROP TABLE #Leave
CREATE TABLE #Leave (ID INT
,LeaveName VARCHAR(10)
,OpeningBalance DECIMAL(7,2)
,Available DECIMAL(7, 2)
,MaxAccum DECIMAL(7, 2)
,TotalAvailed DECIMAL(7, 2)
,Availed DECIMAL(7, 2)
,[Priority] INT
)
INSERT INTO #Leave
(ID ,LeaveName ,OpeningBalance ,Available ,MaxAccum,TotalAvailed ,Availed ,[Priority]
)
SELECT 1,'CL',8.0,6.5,8.0,1.5,0.0,1
UNION ALL
SELECT 2,'PL',15.0,15.0,5.0,0.0,0.0,2
UNION ALL
SELECT 3,'OL',1.0,2.0,1.0,1.0,0.0,3
SELECT * FROM #Leave
I need to calculate the leaves based on the leave application. This table is only for a single user. The business rules for the leave deduction is as follows:
1. Leaves will be deducted based on priority.
2. Whatever is the number of available leaves, a user can only avail "MaxAccum" leaves against every leave application.
3. After deducting the "MaxAccum" leaves the remaining leaves will be deducted from the next row.
4. This process will go on till the user has taken all the applied leaves but if the applied leaves are greater that the total of "MaxAccum" leaves, a new row will be inserted into the table(For leave without pay)
Now, in the above table, if the user applies for 10 leaves my query is giving me correct result set. It is also giving me the currect result but in certain cases I am getting the wrong result. The leaves are deducted as "1" per iteration whereas I need to deduce the leaves based on the remaining leaves. If you run the following script you will understand the scenario better. Just change the value of @Days to whatever(the first line), and you can see that the query is not running correctly. Can someone fix the issue?
DECLARE @Days DECIMAL(7, 2) = 10
IF OBJECT_ID('tempdb..#Leave') IS NOT NULL
DROP TABLE #Leave
CREATE TABLE #Leave (ID INT
,LeaveName VARCHAR(10)
,OpeningBalance DECIMAL(7,2)
,Available DECIMAL(7, 2)
,MaxAccum DECIMAL(7, 2)
,TotalAvailed DECIMAL(7, 2)
,Availed DECIMAL(7, 2)
,[Priority] INT
)
INSERT INTO #Leave
(ID, LeaveName ,OpeningBalance ,Available ,MaxAccum,TotalAvailed ,Availed ,[Priority] )
SELECT 1,'CL',8.0,6.5,8.0,1.5,0.0,1
UNION ALL
SELECT 2,'PL',15.0,15.0,5.0,0.0,0.0,2
UNION ALL
SELECT 3,'OL',1.0,2.0,1.0,1.0,0.0,3
--Cursor Variables.
DECLARE @ID INT
,@Available DECIMAL(7, 2)
,@MaxAccum DECIMAL(7, 2)
,@TotalAvailed DECIMAL(7, 2)
,@Availed DECIMAL(7, 2)
,@Priority INT
--Inner While-Loop Variables
DECLARE @TotalAccumSoFar DECIMAL(7, 2) = 0.0
,@RowAccumSoFar DECIMAL(7, 2) = 0.0
--Variable to check if the cursor is on the last row
DECLARE @RowCount INT = (
SELECT COUNT(*)
FROM #Leave
)
,@CurrentRow INT = 1
--Cursor to loop through the rows
--SELECT * FROM #Leave
UPDATE #Leave
SET MaxAccum = CASE WHEN Available < MaxAccum THEN Available ELSE MaxAccum END
--SELECT * FROM #Leave
DECLARE CURS CURSOR
FOR
SELECT ID
,Available
,MaxAccum
,TotalAvailed
,Availed
,[Priority]
FROM #Leave
WHERE Available > 0
OPEN CURS
FETCH NEXT
FROM CURS
INTO @ID
,@Available
,@MaxAccum
,@TotalAvailed
,@Availed
,@Priority
WHILE @@FETCH_STATUS = 0
BEGIN
--1. Use a while loop to take off as many days as possible from the current row.
WHILE @RowAccumSoFar < @MaxAccum AND @TotalAccumSoFar <= @Days
BEGIN
UPDATE #Leave
SET Available -= CASE
WHEN @Days = 0.5
THEN 0.5
ELSE 1
END
,Availed += CASE
WHEN @Days = 0.5
THEN 0.5
ELSE 1
END
,TotalAvailed += CASE
WHEN @Days = 0.5
THEN 0.5
ELSE 1
END
WHERE ID = @ID
SET @RowAccumSoFar += CASE
WHEN @Days = 0.5
THEN 0.5
ELSE 1
END
SET @TotalAccumSoFar += CASE
WHEN @Days = 0.5
THEN 0.5
ELSE 1
END
IF (@TotalAccumSoFar = @Days)
BEGIN
BREAK
END
END
--2. Check if we've taken off all the leave days
IF (@TotalAccumSoFar = @Days)
BEGIN
BREAK
END
--3. If not and we're on the last row, insert a new row.
IF (@RowCount = @CurrentRow)
BEGIN
DECLARE @RowNumber INT
SELECT @RowNumber = COUNT(*) FROM #Leave
INSERT INTO #Leave (
ID ,LeaveName, OpeningBalance,Available,MaxAccum,TotalAvailed,Availed,[Priority]
)
VALUES (@RowNumber + 1, 'LWP', 0.0,0.0,0.0,(@Days - @TotalAccumSoFar),(@Days - TotalAccumSoFar),0)
END
--4. Update variables
SET @CurrentRow += 1
SET @RowAccumSoFar = 0
FETCH NEXT
FROM CURS
INTO @ID
,@Available
,@MaxAccum
,@TotalAvailed
,@Availed
,@Priority
END
CLOSE CURS
DEALLOCATE CURS
SELECT *
FROM #Leave
IF OBJECT_ID('tempdb..#Leave') IS NOT NULL
DROP TABLE #Leave
January 13, 2016 at 5:45 am
Okay, I have not run your code yet, but I did notice that you do not have any ORDER BY in your cursor definition - so how do you ensure that the rows are processed in order of priority?
Perhaps your bad results are the result of the cursor processing rows in a different order?
(Given that you use default options for the cursor, you get the dynamic version - which is not only slow, but also prone to theoretically returning the same row twice. Not very likely seeing your code, but I didn't look long enough to rule it out).
If neither of these explains it, let me know and I will take a longer look at what is going on. Also, please tell us at least one value for @Days that does produce an incorrect result, what result it produced on your system, what it should have been, and why - remember that what may be obvious to you, is not obvious to others. Rules for leave are different between countries and companies, so please err on the side of assuming I'm a complete idiot. 😀
January 13, 2016 at 7:28 am
Well, the result is already sorted by "priority". Since it is a part of a stored procedure that returns the result set, I have included it as a temp table. Also, for testing you can pass @Days as 4 it will give you the correct result. If you pass 10, it will give you If you wrong result. just check with passing different values for @Days, you will notice the error. It is plain visible(negative values). Anyways, I am explaining you my requirement in details since it is always easy(for me) to start fresh rather than modifying existing code.
I have a leave table in the following format:
IF OBJECT_ID('tempdb..#Leave') IS NOT NULL
DROP TABLE #Leave
CREATE TABLE #Leave (ID INT
,LeaveName VARCHAR(10)
,OpeningBalance DECIMAL(7,2)
,Available DECIMAL(7, 2)
,MaxAccum DECIMAL(7, 2)
,TotalAvailed DECIMAL(7, 2)
,[Priority] INT
)
INSERT INTO #Leave
(ID ,LeaveName ,OpeningBalance ,Available ,MaxAccum, TotalAvailed, [Priority])
SELECT 1, 'CL', 8.0, 8.0, 4.0, 0.0, 1
UNION ALL
SELECT 2, 'PL', 15.0, 15.0, 5.0, 0.0, 2
UNION ALL
SELECT 3, 'OL', 2.0, 2.0, 1.0, 0.0, 3
SELECT * FROM #Leave
The leave deduction policy is like following:
1. The leaves will be deducted sequentially, based on priority number.
2. Only "MaxAccum" leaves can be deducted from every row at one time.
3. The remaining leaves will be forwarded to be deducted from the next leave.
4. After traversing all the rows if there are still leaves left to be taken insert a new row "LWP" (leave without pay" for the remaining leaves.
Case 1. If I apply for 2 days leave, the table must return the following result set:
INSERT INTO #Leave
(ID ,LeaveName ,OpeningBalance ,Available ,MaxAccum, TotalAvailed, [Priority])
SELECT 1, 'CL', 8.0, 6.0, 4.0, 2.0, 1 --Only 2 leaves are deducted from the first row.
SELECT * FROM #Leave
Case 2. If I apply for 10 days leave, the table must return the following result set:
INSERT INTO #Leave
(ID ,LeaveName ,OpeningBalance ,Available ,MaxAccum, TotalAvailed, [Priority])
SELECT 1, 'CL', 8.0, 4.0, 4.0, 4.0, 1 --"Max" 4 leaves are deducted from the first row. Remaining 6 leaves are deducted from the next row.
UNION ALL
SELECT 2, 'PL', 15.0, 10.0, 5.0, 5.0, 2 --"Max" 5 leaves are deducted from the current row. Remaining 1 leave will deducted from the next row.
UNION ALL
SELECT 3, 'OL', 2.0, 1.0, 1.0, 1.0, 3 --Remaining 1 leave is deducted from this row.
SELECT * FROM #Leave
Case 3. If I apply for 12.5 days leave, the table must return the following result set:
INSERT INTO #Leave
(ID ,LeaveName ,OpeningBalance ,Available ,MaxAccum, TotalAvailed, [Priority])
SELECT 1, 'CL', 8.0, 4.0, 4.0, 4.0, 1 --"Max" 4 leaves are deducted from the first row. Remaining 6 leaves are deducted from the next row.
UNION ALL
SELECT 2, 'PL', 15.0, 10.0, 5.0, 5.0, 2 --"Max" 5 leaves are deducted from the current row. Remaining 1 leave will deducted from the next row.
UNION ALL
SELECT 3, 'OL', 2.0, 1.0, 1.0, 1.0, 3 --"Max" 1 leave is deducted from the current row. Remaining 2.5 leaves will be inserted as a new row.
UNION ALL
SELECT 3, 'LWP', 0.0, 0.0, 0.0, 2.5, 4
SELECT * FROM #Leave
I hope this makes the requirement clear.
January 13, 2016 at 1:44 pm
Not sure how you do a cursor over a stored procedure result. If you do it by storing it in a temporary table then cursoring over that, then my remark about not having an ORDER BY in the cursor's SELECT still applies. However, that does not seem to be the cause of your problem in this case.
I think that the reason for the incorrect results is that your cursor processes the leave one day at a time. So when e.g. 6.5 days are available. It does have special logic for requesting half days leave, but no logic for when a type of leave has a half-day available - so it will assign another day when only a half day is available.
Rather than trying to correct the error, I decided to rewrite your logic into a set-based query. Because of the requirement to sometimes add a row, a single query may be theoretically possible (with MERGE), but hard to pull off. Instead, I wrote a set-based update to assign as many days to the leave rows as possible, and then use the sum of the Availed column to determine if more leave was requested.
Note that this also allows other fractions than half-days - given that you chose a data type with two decimal places, I assume that this is relevant in your case as well.
Here's the query. Sorry for the lack of comments; do ask if you don't understand what it's doing (otherwise you'll never be able to maintain it).
DECLARE @Days DECIMAL(7, 2) = 10;
IF OBJECT_ID('tempdb..#Leave') IS NOT NULL
DROP TABLE #Leave;
CREATE TABLE #Leave (ID INT
,LeaveName VARCHAR(10)
,OpeningBalance DECIMAL(7,2)
,Available DECIMAL(7, 2)
,MaxAccum DECIMAL(7, 2)
,TotalAvailed DECIMAL(7, 2)
,Availed DECIMAL(7, 2)
,[Priority] INT
);
INSERT INTO #Leave
(ID, LeaveName ,OpeningBalance ,Available ,MaxAccum,TotalAvailed ,Availed ,[Priority] )
SELECT 1,'CL',8.0,6.5,8.0,1.5,0.0,1
UNION ALL
SELECT 2,'PL',15.0,15.0,5.0,0.0,0.0,2
UNION ALL
SELECT 3,'OL',1.0,2.0,1.0,1.0,0.0,3;
SELECT *
FROM #Leave;
DECLARE @DaysShort decimal(7,2);
WITH
HelperCTE AS
(SELECT ID,
LeaveName,
OpeningBalance,
Available,
MaxAccum,
TotalAvailed,
Availed,
[Priority],
CASE WHEN Available <= MaxAccum THEN Available ELSE MaxAccum END AS MaxToTake
FROM #Leave),
WithRunningTotals AS
(SELECT ID,
LeaveName,
OpeningBalance,
Available,
MaxAccum,
TotalAvailed,
Availed,
[Priority],
MaxToTake,
SUM(MaxToTake) OVER (ORDER BY [Priority] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotalMaxToTake,
COALESCE(SUM(MaxToTake) OVER (ORDER BY [Priority] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS PreviousTotalMaxToTake
FROM HelperCTE),
DaysPerType AS
(SELECT ID,
LeaveName,
OpeningBalance,
Available,
MaxAccum,
TotalAvailed,
Availed,
[Priority],
MaxToTake,
TotalMaxToTake,
PreviousTotalMaxToTake,
CASE WHEN TotalMaxToTake <= @Days
THEN MaxToTake
WHEN WithRunningTotals.PreviousTotalMaxToTake <= @Days
THEN @Days - PreviousTotalMaxToTake
ELSE 0
END AS ToTake
FROM WithRunningTotals)
UPDATE DaysPerType
SET Availed += ToTake,
TotalAvailed += ToTake,
Available -= ToTake;
SET @DaysShort = @Days - (SELECT SUM(Availed) FROM #Leave)
IF @DaysShort > 0
BEGIN;
INSERT INTO #Leave
VALUES (4, 'LWP', 0.0, 0.0, 0.0, @DaysShort, @DaysShort, 0)
END;
SELECT *
FROM #Leave;
IF OBJECT_ID('tempdb..#Leave') IS NOT NULL
DROP TABLE #Leave;
January 13, 2016 at 11:11 pm
Thanks Hugo,
This is spot on. That's why my query was misbehaving. I was iterating the rows "1" leave at a time. The moment a fraction either in "available" or "requested" appeared, it failed. Great answer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply