Post reply

Help with query related to timesheets and overtime rules

  • January 26, 2018 at 7:23 am

    #326620

    I've  been spending about a week in trying to figure out the best approach to accomplish this query. I have been asked to query employee timesheets and based on the State that employee works in, determine if the employee incorrectly recorded overtime based on that State's overtime rules. One example would be that an employee must work 40 hours within a given week to begin recording overtime and if they don't meet the 40 hour requirement and have recorded time as overtime, that employee's timesheet is incorrect. The data in the table is pivoted and I've attempted to solve this problem with the table data pivoted and unpivoted to no avail. I thought about using the CASE expression but I'm not sure how I could check to see if the employee met 40 working hours before they actually started recording overtime. I've attached a script with arbitrary test data that will create the pivoted and unpivoted tables. I've also attached the overtime rules for each State.

    Thanks in advance for your help.

  • January 26, 2018 at 7:50 am

    #1977524

    You'll need to find a way of modelling those overtime rules in a table.  You could put it into your query in a long CASE expression, but I don't recommend that, since you'd have to change your code every time the rules change in any particular state.

    John

  • January 26, 2018 at 8:03 am

    #1977528

    Thanks John. I didn't consider that and will give it a shot.

    Thanks, Troe

  • January 26, 2018 at 8:31 am

    #1977535

    Yeah, something like this.  I've not thought it through thoroughly, but it may give you something to build on.  States such as California that have complex rules will need more than one row in the table

    CREATE TABLE OTRules (
         StateCode char(2) NOT NULL
    ,    RuleNo tinyint NOT NULL
    ,    HoursperWeekThreshold decimal(4,1) CONSTRAINT CK_OTRules_HrsperWeek CHECK (HoursperWeekThreshold < 168) NULL
    ,    HoursperDayThreshold decimal(3,1) CONSTRAINT CK_OTRules_HrsperDay CHECK (HoursperDayThreshold < 24) NULL
    ,    DaysperWeekThreshold tinyint CONSTRAINT CK_OTRules_DaysperWeek CHECK (DaysperWeekThreshold <= 7) NULL
    ,    HoursonFinalDayThreshold decimal(3,1) CONSTRAINT CK_OTRules_FinalDay CHECK (HoursonFinalDayThreshold < 24) NULL
    ,    AppliestoFLSA bit NOT NULL
    ,    Multiplier tinyint NOT NULL
    CONSTRAINT CK_OTrules_TablePopulated CHECK (COALESCE(HoursperWeekThreshold,HoursperDayThreshold,DaysperWeekThreshold,HoursonFinalDayThreshold) IS NOT NULL)
    CONSTRAINT PK_OTRukes_StateCode_RuleNo PRIMARY KEY(StateCode, RuleNo)
        )

    John

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

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