Calculate how many deduction for each row based on was taken before

Question

Calculate how many deduction for each row based on was taken before

Default port

Points: 1480

May 5, 2020 at 9:28 am

I have a problem to make a query to fill in how many deduction should be applied for each row instead using fetch status looping for each row.

CREATE TABLE #Leave
( EmployeeID VARCHAR(10), Expired SMALLDATETIME, Balanced INT, [Type] VARCHAR(10))
CREATE TABLE #DeductLeave
( EmployeeID VARCHAR(10), Taken INT)

INSERT INTO #Leave VALUES ('700065','2020-06-26', 1, 'PC')
INSERT INTO #Leave VALUES ('700065','2020-06-30', 9, 'AL')
INSERT INTO #Leave VALUES ('700065','2021-06-30', 4, 'AL')
INSERT INTO #Leave VALUES ('701146','2020-12-31', 11, 'AL')
INSERT INTO #Leave VALUES ('701146','2020-12-31', 1, 'PC')
INSERT INTO #Leave VALUES ('701146','2021-06-30', 4, 'AL')
INSERT INTO #Leave VALUES ('701425','2020-06-25', 2, 'PC')
INSERT INTO #Leave VALUES ('701425','2020-06-27', 1, 'PC')
INSERT INTO #Leave VALUES ('701425','2020-06-30', 6, 'AL')
INSERT INTO #Leave VALUES ('701425','2021-06-25', 4, 'AL')
INSERT INTO #Leave VALUES ('701546','2020-05-19', 1, 'PC')
INSERT INTO #Leave VALUES ('701546','2020-06-30', 10, 'AL')
INSERT INTO #Leave VALUES ('701546','2021-06-30', 4, 'AL')

INSERT INTO #DeductLeave VALUES ('700065',3)
INSERT INTO #DeductLeave VALUES ('701146',4)
INSERT INTO #DeductLeave VALUES ('701425',3)
INSERT INTO #DeductLeave VALUES ('701546',4)

SELECT * FROM #Leave
SELECT * FROM #deductleave

I want to get result like this one

CREATE TABLE #Result
( EmployeeID VARCHAR(10), Expired SMALLDATETIME, Balanced INT, [Type] VARCHAR(10), [Used] INT)
INSERT INTO #result VALUES ('700065','2020-06-26', 1, 'PC',1)
INSERT INTO #result VALUES ('700065','2020-06-30', 9, 'AL',2)
INSERT INTO #result VALUES ('700065','2021-06-30', 4, 'AL',0)
INSERT INTO #result VALUES ('701146','2020-12-31', 11, 'AL',4)
INSERT INTO #result VALUES ('701146','2020-12-31', 1, 'PC',0)
INSERT INTO #result VALUES ('701146','2021-06-30', 4, 'AL',0)
INSERT INTO #result VALUES ('701425','2020-06-25', 2, 'PC',2)
INSERT INTO #result VALUES ('701425','2020-06-27', 1, 'PC',1)
INSERT INTO #result VALUES ('701425','2020-06-30', 6, 'AL',0)
INSERT INTO #result VALUES ('701425','2021-06-25', 4, 'AL',0)
INSERT INTO #result VALUES ('701546','2020-05-19', 1, 'PC',1)
INSERT INTO #result VALUES ('701546','2020-06-30', 10, 'AL',3)
INSERT INTO #result VALUES ('701546','2021-06-30', 4, 'AL',0)

SELECT * FROM #result

Used column represent how many leaves was taken applied as long as deducted balance is enough.

Thanks and i'm sorry for the limitations language that i have.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

I realise that this is the SQL 2008 forum, this may not work out; the code below requires SQL 2012 or later. Then again, the sad story is that the most efficient way to implement this on SQL 2008 may be a loop. You can write set-based updates of looping sums on SQL 2008, but performance is proportional to the square of number of rows in the partition. (I.e. the employeeID in this case.)

;WITH runsum AS (
   SELECT EmployeeID, Expired, Balanced, Type, 
          SUM(Balanced) OVER(PARTITION BY EmployeeID ORDER BY Expired
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Total,
          SUM(Balanced) OVER(PARTITION BY EmployeeID ORDER BY Expired
                             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrevTotal
   FROM   #Leave
)
SELECT r.EmployeeID, r.Expired, r.Balanced, r.Type, 
       CASE WHEN r.PrevTotal IS NULL THEN IIF(r.Balanced > d.Taken, d.Taken, r.Balanced)
            WHEN r.PrevTotal < d.Taken THEN IIF(r.Total < d.Taken, r.Balanced, d.Taken - r.PrevTotal)
            ELSE 0
       END
FROM   runsum r
JOIN   #DeductLeave d ON r.EmployeeID = d.EmployeeID
ORDER  BY r.EmployeeID, r.Expired

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

By the way, thank you for proving CREATE TABLE + INSERT and even the sample data with INSERT statements. Far too few people do that.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

gentong.bocor Default port Points: 1480 More actions · Answer 3

yessss... !!

that's what i want.. but unfortunately that code only can be run using SQL2012. is there any code using cte which simplified loop for each rows but running in 2008 ?

thanks again for your quick reply..

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

If you have a working loop for SQL 2008, stick with it. And consider my piece of code a kick in the rear parts to get you ahead for an upgrade. SQL 2008 is out of support, and an upgrade to a later version is certainly overdue.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]