Sick Time Accrual Balances in SQL

This will be difficult to understand but , I will try to explain as much as I can.

I have a Table with all Sick time Taken per each employee and Year , and I have a table with sick time rules, basically if a n employee works in California and then in Vegas  and then accrue 30hrs in california  and also so 20hrs in vegas for sick time .

then that same employee taks 60 hrs of sick time , I want to be able to subtract from the 30hrs they took in Cali and then  20hrs in vegas
so on the Balance I would apply a negative 10

my table for the employee sick time taken looks something like this
Employee, company , YearWorked , SickTimeTaken
30369      1000      2014      40.00
30369      1000      2015      8.00
88508      1000      2014      16.00
88508      1000      2015      22.00

my table for Accrued Balances looks something like this

Company , Employee    Sick_Plan      YearWorked          HoursAccrued               Timetaken
1000       30369               Cali                 2015                             30.00                               0.000
1000       30369               Vegas          2015                              20.00                               0.000      


my desired result would look something like


Company , Employee    Sick_Plan      YearWorked          HoursAccrued               Timetaken
1000       30369               Cali                 2015                             30.00                               -30.00
1000       30369               Vegas          2015                              20.00                               -30.00                       


I always want to decrements from the top going down , the last record if there's a remainder , it would have the rest of the balance. I've literally been pulling my hair on this for days but nothing works.

Please help and if any questions or more information I can provide as soon as possible.
valentinemhlangaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Will you be entering the 60 hours on some form, that then needs to be deducted from the AccruedBalances? I've set up two temporary tables (below), but not sure if you have this "60 hours" in some table that needs to be applied, or if you will want to call some stored procedure from an online form, or what?

DECLARE @Employee TABLE (
    EmployeeID      int         NOT NULL,
    Company         smallint    NOT NULL,
    YearWorked      smallint    NOT NULL,
    SickTimeTaken   smallint    NOT NULL)

INSERT INTO @Employee VALUES (30369, 1000, 2014, 40)
INSERT INTO @Employee VALUES (30369, 1000, 2015, 8)
INSERT INTO @Employee VALUES (88508, 1000, 2014, 16)
INSERT INTO @Employee VALUES (88508, 1000, 2015, 22)

DECLARE @AccruedBalances TABLE (
    Company         smallint    NOT NULL,
    EmployeeID      int         NOT NULL,
    SickPlan        varchar(8)  NOT NULL,
    YearWorked      smallint    NOT NULL,
    HoursAccrued    smallint    NOT NULL,
    TimeTaken       smallint    NOT NULL )

INSERT INTO @AccruedBalances VALUES (100, 30369, 'Cali', 2015, 30, 0)
INSERT INTO @AccruedBalances VALUES (100, 30369, 'Vegas', 2015, 20, 0)

SELECT * FROM @Employee
SELECT * FROM @AccruedBalances

Open in new window

valentinemhlangaAuthor Commented:
Hello @dsacker , thank you for responding , however I am not looking for that , basically I I want to get the 1st Value into the cursor for the sick time taken and then use that value going row by row decremented it accordingly and when theres a remaining value withing the sicktimetaken table , I would dump that same value on the Sicktimetaken column on the Accrual Table
dsackerContract ERP Admin/ConsultantCommented:
I have a few questions, in order to get a better understanding:

The first table (with four fields) is your SickTime table (I called it the @Employee table in my first post), right?
When you have completed applying the 60 hours, your SickTime table would show 68 hours for 2015 (adding the 60 to the existing 8 hours), right?
Hypothecital: What do you want to see happen if you have a balance in your @AccruedBalances table (what I called it in my first post)? I would assume that next year, if this same employee takes 32 hours sick time, you would clear out the -10 balance, then only need apply 22 remaining hours, right? Or I assume these simply roll off at the beginning of a new year?
This is more a statement than a question. You will need some table or way of ensuring that the @AccruedBalances table is updated in the correct order. There was no real way, other than ordering by the city name, of ensuring that.
Hope my questions make sense.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

dsackerContract ERP Admin/ConsultantCommented:
Assuming the answers to my questions above are mostly yes, here is a working script:

SET NOCOUNT ON

DECLARE @SickTime TABLE (
    EmployeeID      int         NOT NULL,
    Company         smallint    NOT NULL,
    YearWorked      smallint    NOT NULL,
    SickTimeTaken   smallint    NOT NULL)

INSERT INTO @SickTime VALUES (30369, 1000, 2014, 40)
INSERT INTO @SickTime VALUES (30369, 1000, 2015, 8)
INSERT INTO @SickTime VALUES (88508, 1000, 2014, 16)
INSERT INTO @SickTime VALUES (88508, 1000, 2015, 22)

DECLARE @AccruedBalances TABLE (
    Company         smallint    NOT NULL,
    EmployeeID      int         NOT NULL,
    SickPlan        varchar(8)  NOT NULL,
    YearWorked      smallint    NOT NULL,
    HoursAccrued    smallint    NOT NULL,
    TimeTaken       smallint    NOT NULL )

INSERT INTO @AccruedBalances VALUES (100, 30369, 'Cali', 2015, 30, 0)
INSERT INTO @AccruedBalances VALUES (100, 30369, 'Vegas', 2015, 20, 0)

DECLARE @EmployeeID     int,
        @SickTimeTaken  smallint,
        @Year           smallint,
        @Cnt            smallint,
        @MaxRows        smallint,
        @RemainingHours smallint,
        @HoursAccrued   smallint,
        @TimeTaken      smallint

SET @EmployeeID    = 30369  -- I assume these will get passed from an
SET @SickTimeTaken = 60     -- application into a stored procedure built
SET @Year          = 2015   -- from this scripting exercise?

SELECT 'Before' AS [@AccruedBalances], * FROM @AccruedBalances
SELECT 'Before' AS [@SickTime], * FROM @SickTime

SELECT @MaxRows = COUNT(1),
       @RemainingHours = @SickTimeTaken,
       @Cnt = 0
FROM   @AccruedBalances
WHERE  EmployeeID = @EmployeeID
AND    YearWorked = @Year

DECLARE csr CURSOR FOR
SELECT  HoursAccrued, TimeTaken
FROM    @AccruedBalances
WHERE   EmployeeID = @EmployeeID
AND     YearWorked = @Year
AND     TimeTaken  = 0

OPEN csr
WHILE @RemainingHours > 0
BEGIN
    FETCH FROM csr INTO @HoursAccrued, @TimeTaken
    IF @@FETCH_STATUS <> 0 BREAK

    SET @Cnt = @Cnt + 1
    SET @TimeTaken = CASE
                         WHEN @Cnt = @MaxRows THEN @RemainingHours * -1
                         WHEN @HoursAccrued <= @RemainingHours THEN @HoursAccrued * -1
                         ELSE @RemainingHours * -1
                     END
    SET @RemainingHours = @RemainingHours + @TimeTaken
    UPDATE @AccruedBalances
    SET TimeTaken = @TimeTaken
    WHERE CURRENT OF csr
END
CLOSE csr
DEALLOCATE csr

UPDATE  s
SET     SickTimeTaken = ISNULL(SickTimeTaken, 0) - TotalTimeTaken
FROM    @SickTime s
JOIN  ( SELECT EmployeeID, YearWorked, SUM(TimeTaken) AS TotalTimeTaken
        FROM   @AccruedBalances
        GROUP BY EmployeeID, YearWorked ) a
ON      a.EmployeeID = s.EmployeeID
AND     a.YearWorked = s.YearWorked
AND     s.EmployeeID = @EmployeeID

SELECT 'After' AS [@AccruedBalances], * FROM @AccruedBalances
SELECT 'After' AS [@SickTime], * FROM @SickTime

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
valentinemhlangaAuthor Commented:
@dsacker  Thank you soo much this is exactly what I am looking for.
valentinemhlangaAuthor Commented:
Thank you again so much , this is exactly I've been trying to solve for weeks now
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.