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

  • 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.

     

  • 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]

  • 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]

  • 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..

  • 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]

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

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