How to calculate value with multiple condition and how to design database?

Question

I need to calculated data with multiple condition

MinOTHrs    MaxOTHs     DayType          Rate
------------------------------          -----------
3             -         Working_day        18
4             11        Weekend            18
11            -         Weekend            36

For example if employee do OT on working_day > 3 Hrs. they will get rate 18 (MAX 18 for working_day) if employee do OT on weekend > 4 Hrs. but < 11 Hrs. they will get rate 18 if employee do OT on weekend > 11 Hrs. they will get rate 36(MAX 36 for weekend) Could you suggest me about coding and design database ? Thank you very much

Answer 1

I'd probably do most of the work as multiple joins between Work and OT tables. That way you can be fairly specific about the scenario you want.

Below is a nice primer for you. It's not perfect but it should get you a long way towards where I think you want to go! (IDs included are just for debugging purposes - they're not required of course).

//setup

CREATE TABLE overTime (
    MinOTHrs integer,
    MaxOTHrs integer,
    DayType varchar(max),
    payRate integer
);

CREATE TABLE workLog (
    logID integer,
    empID integer,
    DayType varchar(max),
    hoursWorked integer
);

insert into overTime values (3, null, 'Working_day', 18);
insert into overTime values (4, 11, 'Weekend', 18);
insert into overTime values (11, null, 'Weekend', 36);

insert into workLog values (567, 1234, 'Working_day', 2);
insert into workLog values (568, 1234, 'Working_day', 5);
insert into workLog values (569, 1234, 'Weekend', 2);
insert into workLog values (570, 1234, 'Weekend', 9);
insert into workLog values (571, 1234, 'Weekend', 14);

//query
select 
    wl.logID
  , wl.empID
  , wl.DayType
  , wl.hoursWorked
  , coalesce(weekdayOverTime.MinOTHrs, weekendLowOverTime.MinOTHrs, weekendHighOverTime.MinOTHrs) as MINOTHrs
  , coalesce(weekdayOverTime.MaxOTHrs, weekendLowOverTime.MaxOTHrs, weekendHighOverTime.MaxOTHrs) as MAXOTHrs
  , coalesce(weekdayOverTime.payRate, weekendLowOverTime.payRate, weekendHighOverTime.payRate) as maxOTPayBracket

from workLog wl
left join overTime weekdayOverTime on wl.DayType = weekdayOverTime.dayType and wl.hoursWorked > weekdayOverTime.MinOTHrs and wl.dayType = 'Working_day'
left join overTime weekendLowOverTime on wl.DayType = weekendLowOverTime.dayType and wl.hoursWorked > weekendLowOverTime.MinOTHrs and wl.hoursWorked < weekendLowOverTime.MaxOTHrs and wl.dayType= 'Weekend'
left join overTime weekendHighOverTime on wl.DayType = weekendHighOverTime.dayType and wl.hoursWorked > weekendHighOverTime.MinOTHrs and weekendHighOverTime.MaxOTHrs is null and wl.dayType= 'Weekend'