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

You might want to try https://dba.stackexchange.com/. – wazz Sep 15 '19 at 18:47 — wazz, Sep 15 '19 at 18:47

score 0 · Accepted Answer · answered Sep 16 '19 at 05:28

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'

Thank you very much i have one question if i need to add monthlyOT for whole employee Can i use overtime table to set rate — WD_Apps, Sep 16 '19 at 06:15
Interesting business case. Given your original problem was based on work hours per day moving to monthly might be hard. If I was designing a system from the ground up I'd suggest putting the OT calculation as a stored proc which could be run periodically (overnight) and then store the outputs into your own processed table - which could be aggregated up to higher time period (week/month/year) etc . — Mark Taylor, Sep 16 '19 at 08:26
Sorry i typed wrong change "MonthlyOT" to "Monthly" my Monthly mean that whole employee will get Monthly allowance every month. Can i set Monthly allowance rate in overtime table ? — WD_Apps, Sep 16 '19 at 10:24
Ah ok, well you can put in the OT table but personally I probably wouldn't. It's better for simple situations to reuse elements you already have but if you expect your application to grow or see prolonged use it's almost always better in the long term to keep separate concepts in physically distinct areas. Either way you can code for it well enough though. — Mark Taylor, Sep 16 '19 at 12:03

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

1 Answers1