SQL Summing feature form multiple overtime rates

Question

So at work currently the overtime is figured out in excel by hand, I wish to change this and use sql and ssrs to remove this manual task once a month. A slight problem i have is the complex overtime rates listed below.

(Overtime is once you have hit 40+ hours monday-friday, weekends and bank holidays are overtime no matter what)

                Mon-Fri            Sat                 Sun-BH            Rate
    NOH         6am-6pm           N/A                   N/A               x1
    OOH1        6pm - 00          8am - 00             8am- 00            x1.25
    OOh2        00- 6am           00-8am               00-8am             x1.25

Currently i have data in an sql table that is pulled from Toggl,

These rows are made up of a single input for the days tasks, made up of task, description, start date, start time, end date, end time and then duration.

Hopefully this will be enough for someone to help us out! The different rates really have thrown up some problems!

CREATE TABLE [dbo].[JSC010_Tech_Time](
[ID] [int] NULL,
[TECH] [varchar](100) NULL,
[EMAIL] [varchar](150) NULL,
[CLIENT] [varchar](50) NULL,
[PROJECT] [varchar](50) NULL,
[TASK] [varchar](50) NULL,
[DESCRIPTION] [varchar](250) NULL,
[BILLABLE] [varchar](3) NULL,
[START_DATE] [date] NULL,
[START_TIME] [time](7) NULL,
[END_DATE] [date] NULL,
[END_TIME] [time](7) NULL,
[DURATION] [time](7) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[JSC010_Tech_Time]
       ([ID]
       ,[TECH]
       ,[EMAIL]
       ,[CLIENT]
       ,[PROJECT]
       ,[TASK]
       ,[DESCRIPTION]
       ,[BILLABLE]
       ,[START_DATE]
       ,[START_TIME]
       ,[END_DATE]
       ,[END_TIME]
       ,[DURATION])
 VALUES
       (<ID, int,>
       ,<TECH, varchar(100),>
       ,<EMAIL, varchar(150),>
       ,<CLIENT, varchar(50),>
       ,<PROJECT, varchar(50),>
       ,<TASK, varchar(50),>
       ,<DESCRIPTION, varchar(250),>
       ,<BILLABLE, varchar(3),>
       ,<START_DATE, date,>
       ,<START_TIME, time(7),>
       ,<END_DATE, date,>
       ,<END_TIME, time(7),>
       ,<DURATION, time(7),>)

Here is some results below, which shows the user has worked over 40 hours (task of travel is not included in the weekly 40 hrs)

  insert into JSC010_Tech_Time values('10','bob','bob@bob.com','ABC','Work Order','Office','First Day, Induction along with manual handling certs','No','2019-04-01','09:00:00.0000000','2019-04-01','17:00:00.0000000','08:00:00.0000000')
  insert into JSC010_Tech_Time values('20','bob','bob@bob.com','ABC','Work Order','Office','2nd Day induction rack intro etc','No','2019-04-02','09:00:00.0000000','2019-04-02','17:00:00.0000000','08:00:00.0000000')
   insert into JSC010_Tech_Time values('30','bob','bob@bob.com','ABC','Work Order','Travel','office to site','Yes','2019-04-03','06:00:00.0000000','2019-04-03','08:00:00.0000000','02:00:00.0000000')
    insert into JSC010_Tech_Time values('40','bob','bob@bob.com','ABC','Work Order','Pre-patch','Work Order','Yes','2019-04-03','08:00:00.0000000','2019-04-03','16:00:00.0000000','08:00:00.0000000')
    insert into JSC010_Tech_Time values('50','bob','bob@bob.com','ABC','Work Order','Travel','site to office','Yes','2019-04-03','16:00:00.0000000','2019-04-03','18:00:00.0000000','02:00:00.0000000')
    insert into JSC010_Tech_Time values('60','bob','bob@bob.com','ABC','Work Order','Travel','site to office','Yes','2019-04-04','06:00:00.0000000','2019-04-04','08:00:00.0000000','02:00:00.0000000')
    insert into JSC010_Tech_Time values('70','bob','bob@bob.com','ABC','Work Order','Pre-patch','Work Order','Yes','2019-04-04','08:00:00.0000000','2019-04-04','14:00:00.0000000','06:00:00.0000000')
    insert into JSC010_Tech_Time values('80','bob','bob@bob.com','ABC','Work Order','Travel','work order','Yes','2019-04-04','14:00:00.0000000','2019-04-04','14:30:00.0000000','00:30:00.0000000')
     insert into JSC010_Tech_Time values('90','bob','bob@bob.com','ABC','Work Order','Audit','Work Order','Yes','2019-04-04','14:30:00.0000000','2019-04-04','16:30:00.0000000','02:00:00.0000000')
     insert into JSC010_Tech_Time values('100','bob','bob@bob.com','ABC','Work Order','Travel','Site-office','Yes','2019-04-04','16:30:00.0000000','2019-04-04','18:30:00.0000000','02:00:00.0000000')
    insert into JSC010_Tech_Time values('110','bob','bob@bob.com','ABC','Work Order','Travel','office-site','Yes','2019-04-05','05:00:00.0000000','2019-04-05','07:00:00.0000000','02:00:00.0000000')
    insert into JSC010_Tech_Time values('120','bob','bob@bob.com','ABC','Work Order','Audit','Work Order','Yes','2019-04-05','07:00:00.0000000','2019-04-05','19:00:00.0000000','12:00:00.0000000')
    insert into JSC010_Tech_Time values('130','bob','bob@bob.com','ABC','Work Order','Travel','Site-office','Yes','2019-04-05','19:30:00.0000000','2019-04-05','21:00:00.0000000','01:30:00.0000000')

So this should bring back results of

OOh1 : 1 hours NOH : 3 hours

This is because week total is 44 hrs, 05/4 is where an extra 4 hours is done, 3 before the 6pm deadline, 1 after.

Here is the bank holiday table.

     Create table JSC015_Bank_Holidays(ID int, Date date, day_Name varchar(10), Name varchar(30), Day int, Month int, Year int)
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (1,'20190826','Monday','Summer Bank Holiday','26','8','2019')
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (2,'20191225','Wednesday','Christmas Day','25','12','2019')
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (3,'20191226','Thursday','Boxing Day','26','12','2019')
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (4,'20200101','Wednesday','New Years Day','1','1','2020')
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (5,'20200410','Friday','Good Friday','10','4','2020')
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (6,'20200413','Monday','Easter Monday','13','4','2020')
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (7,'20200504','Monday','Early May Bank Holiday','4','5','2020')
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (8,'20200525','Monday','Spring Bank Holiday','25','5','2020')
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (9,'20200831','Monday','Summer Bank Holiday','31','8','2020')
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (10,'20201225','Friday','Christmas Day','25','12','2020')
     Insert into JSC015_Bank_Holidays (ID,Date,day_Name, Name, Day, Month, Year) values (11,'20201228','Monday','Boxing Day (Sub)','28','12','2020')

     select * from JSC015_Bank_Holidays

Answer 1

I've re-written the query to make it simpler. This pivots the data on the rate type and sums the individual hours throughout the result set, taking in to account the duration (whether or not this is rounded to a whole hour) and where in the hour the shift starts/ends (in combination with the duration can be used to determine which hour to round down to the nearest minute value).

EDIT:

I have modified the query to be able to handle time periods which are less than an hour long.

2nd Edit:

I have modified the query to handle the first and last hours independently so it should calculate the number of minutes correctly depending on if the first or last hour should be considered.

3rd Edit:

I've fixed the rounding issue, it was to do with the way that the minutes land.

I've also added 3 commented sections in to the query to help you with debugging (in case you want to have a go at fixing some of these issues too).

The commented out where clause will allow you to focus on which row is causing you the issue

The commented "houroftheday" column (commented out of the select statement and the inner select statement) will allow you to see what value is being calculated per hour.

4th Edit:

I have re-written the query to work in a slightly different way. To account for shifts which span more than one calendar day (cross midnight) I've used a common table expression which assigns a unique id per hour throughout the week. This prevents the issue we were having with your last comment where the starting hour of the shift was 19 and the finishing hour was 2. This common table expression may need to be expanded to cover shifts that stretch over calendar weeks.

/*Create the temp table for the sample rates data*/
CREATE TABLE #JSC010_Tech_Time(
[ID] [int] NULL,
[TECH] [varchar](100) NULL,
[EMAIL] [varchar](150) NULL,
[CLIENT] [varchar](50) NULL,
[PROJECT] [varchar](50) NULL,
[TASK] [varchar](50) NULL,
[DESCRIPTION] [varchar](250) NULL,
[BILLABLE] [varchar](3) NULL,
[START_DATE] [date] NULL,
[START_TIME] [time](7) NULL,
[END_DATE] [date] NULL,
[END_TIME] [time](7) NULL,
[DURATION] [time](7) NULL
);

/*insert the sample data modified to fit table structure provided*/
INSERT INTO #JSC010_Tech_Time
       ([ID]
       ,[TECH]
       ,[EMAIL]
       ,[CLIENT]
       ,[PROJECT]
       ,[TASK]
       ,[DESCRIPTION]
       ,[BILLABLE]
       ,[START_DATE]
       ,[START_TIME]
       ,[END_DATE]
       ,[END_TIME]
       ,[DURATION])
 VALUES
(1,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts', '',    'Office  First Day, Induction along with manual handling certs',   'No',  '2019-04-01',  '09:00:00.0000000',    '2019-04-01', '17:00:00.0000000',    '08:00:00.0000000'),
(2,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts', '',  'Office  2nd Day induction rack intro etc',    'No',  '2019-04-02',  '09:00:00.0000000',    '2019-04-02',  '17:00:00.0000000',    '08:00:00.0000000'),
(3,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts', '',   'Travel  office to site',  'Yes', '2019-04-03',  '06:00:00.0000000',    '2019-04-03',  '08:00:00.0000000',    '02:00:00.0000000'),
(4,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts', '',   'Pre-patch   Work Order',  'Yes', '2019-04-03',  '08:00:00.0000000',    '2019-04-03',  '16:00:00.0000000',    '08:00:00.0000000'),
(5,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts', '',   'Travel  site to office',  'Yes', '2019-04-03',  '16:00:00.0000000',    '2019-04-03',  '18:00:00.0000000',    '02:00:00.0000000'),
(6,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts', '',   'Travel  site to office',  'Yes', '2019-04-04',  '06:00:00.0000000',    '2019-04-04',  '08:00:00.0000000',    '02:00:00.0000000'),
(7,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts', '',   'Pre-patch   Work Order',  'Yes', '2019-04-04',  '08:00:00.0000000',    '2019-04-04',  '14:00:00.0000000',    '06:00:00.0000000'),
(8,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts', '',   'Travel  Journey', 'Yes', '2019-04-04',  '14:00:00.0000000',    '2019-04-04',  '14:30:00.0000000',    '00:30:00.0000000'),
(9,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts', '',   'Audit   Work Order',  'Yes', '2019-04-04',  '14:30:00.0000000',    '2019-04-04',  '16:30:00.0000000',    '02:00:00.0000000'),
(10,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts','',    'Travel  Site-office', 'Yes', '2019-04-04',  '16:30:00.0000000',    '2019-04-04',  '18:30:00.0000000',    '02:00:00.0000000'),
(11,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts','',    'Travel  office-site', 'Yes', '2019-04-05',  '05:00:00.0000000',    '2019-04-05',  '07:00:00.0000000',    '02:00:00.0000000'),
(12,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts','',    'Audit   Work Order',  'Yes', '2019-04-05',  '07:00:00.0000000',    '2019-04-05',  '19:00:00.0000000',    '12:00:00.0000000'),
(13,   'Bob', 'Bob@bob.com', 'ABC', 'Accounts','',    'Travel  Site-office', 'Yes', '2019-04-05',  '19:30:00.0000000',    '2019-04-05',  '21:00:00.0000000',    '01:30:00.0000000');

INSERT INTO #JSC010_Tech_Time ([ID],[TECH],[EMAIL],[CLIENT],[PROJECT],[TASK],[DESCRIPTION],[BILLABLE],[START_DATE],[START_TIME],[END_DATE],[END_TIME],[DURATION])
VALUES ((select isnull(max(ID),0)+1 from #JSC010_Tech_Time),'bob','bob@bob.com','project B','Bob Project 04/05/2019','End to End ','overtime ','Yes','20190504','05:00:00','20190504','18:00:00','13:00:00');

 INSERT INTO #JSC010_Tech_Time ([ID],[TECH],[EMAIL],[CLIENT],[PROJECT],[TASK],[DESCRIPTION],[BILLABLE],[START_DATE],[START_TIME],[END_DATE],[END_TIME],[DURATION])
 VALUES ((select isnull(max(ID),0)+1 from #JSC010_Tech_Time),'BOB','BOB','BOB','Q19 Migration','patch','wo','Yes','20190418','08:00:57','20190418','12:30:57','04:30:00');

 INSERT INTO #JSC010_Tech_Time ([ID],[TECH],[EMAIL],[CLIENT],[PROJECT],[TASK],[DESCRIPTION],[BILLABLE],[START_DATE],[START_TIME],[END_DATE],[END_TIME],[DURATION])
 VALUES ((select isnull(max(ID),0)+1 from #jsc010_tech_time),'bob','bob.com','Projects','Q18','Travel','city','Yes','20190404','14:00:00','20190404','14:30:00','00:30:00');

 INSERT INTO #JSC010_Tech_Time ([ID],[TECH],[EMAIL],[CLIENT],[PROJECT],[TASK],[DESCRIPTION],[BILLABLE],[START_DATE],[START_TIME],[END_DATE],[END_TIME],[DURATION])
 VALUES ((select isnull(max(ID),0)+1 from #JSC010_Tech_Time),'bob','bob.com','DACC','140-2','Travel','Between site','Yes','20190409','01:00:00','20190409','01:15:00','00:15:00');

 INSERT INTO #JSC010_Tech_Time ([ID],[TECH],[EMAIL],[CLIENT],[PROJECT],[TASK],[DESCRIPTION],[BILLABLE],[START_DATE],[START_TIME],[END_DATE],[END_TIME],[DURATION])
 VALUES ((select isnull(max(ID),0)+1 from #JSC010_Tech_Time),'Bob','bob.com','T','DRC','Engineer On Site','','Yes','20190509','18:30:00','20190509','19:15:00','00:45:00');

 INSERT INTO #JSC010_Tech_Time ([ID],[TECH],[EMAIL],[CLIENT],[PROJECT],[TASK],[DESCRIPTION],[BILLABLE],[START_DATE],[START_TIME],[END_DATE],[END_TIME],[DURATION])
 VALUES ((select isnull(max(ID),0)+1 from #JSC010_Tech_Time),'Bob','bob.com','DACC','TH-BS','Decom','Decom BS','Yes','20190409','13:15:00','20190409','15:30:00','02:15:00');

 INSERT INTO #JSC010_Tech_Time ([ID],[TECH],[EMAIL],[CLIENT],[PROJECT],[TASK],[DESCRIPTION],[BILLABLE],[START_DATE],[START_TIME],[END_DATE],[END_TIME],[DURATION])
 VALUES ((select isnull(max(ID),0)+1 from #JSC010_Tech_Time),'bob','bob.com','BAU','BloombergWK','Engineer On Site','','Yes','20190522','22:45:39','20190522','23:30:52','00:45:13');

 INSERT INTO #JSC010_Tech_Time ([ID],[TECH],[EMAIL],[CLIENT],[PROJECT],[TASK],[DESCRIPTION],[BILLABLE],[START_DATE],[START_TIME],[END_DATE],[END_TIME],[DURATION])
 VALUES ((select isnull(max(ID),0)+1 from #JSC010_Tech_Time),'bob','bob.com','DCC','140-','Decom','Decom','Yes','20190409','08:45:00','20190409','13:00:00','04:15:00');

 INSERT INTO #JSC010_Tech_Time ([ID],[TECH],[EMAIL],[CLIENT],[PROJECT],[TASK],[DESCRIPTION],[BILLABLE],[START_DATE],[START_TIME],[END_DATE],[END_TIME],[DURATION])
 VALUES ((select isnull(max(ID),0)+1 from #JSC010_Tech_Time),'Bob','BOB.com','Projects','LD6 - 01/05/2019','Patch','devices','Yes','20190501','19:00:00','20190502','02:00:00','07:00:00');

/*build rates table*/
declare @ratesperday table (
    rateperdayid int identity(1,1) primary key,
    weekdayno int,
    hourno int,
    ratetype nvarchar(5),
    rate FLOAT
);
/*use incrementing values to build relevant week day numbers and hour numbers*/
declare @dayno int = 1;
declare @hourno int=0;
/*loop through each day and perform logic per hour starting at midnight (0)*/
while @dayno < = 7
    begin
    select @hourno=0;
       /*loop through from midnight until 11 pm per day*/
        while @hourno<=23
            begin
 --              Mon-Fri            Sat                 Sun-BH            Rate
    --NOH         6am-6pm           N/A                   N/A               x1
    --OOH1        6pm - 00          8am - 00             8am- 00            x1.25
    --OOh2        00- 6am           00-8am               00-8am             x1.25
            insert @ratesperday (weekdayno, hourno, ratetype)
            select @dayno, @hourno, case when @dayno in (1, 7) and @hourno >=0 and @hourno <8 then 'OOH2'
                                         when @dayno in (1, 7) and @hourno>=8 and @hourno <= 23 then 'OOH1'
                                         when @dayno in (2, 3, 4, 5, 6) and @hourno >=0 and @hourno <6 then 'OOH2'
                                         when @dayno in (2, 3, 4, 5, 6) and @hourno >18 and @hourno <= 23 then 'OOH1'
                                         else 'NOH' end;
            select @hourno=@hourno+1;
            end
    select @dayno=@dayno+1;

    end
    /*use the applied rate type to set the rate in one hit*/
    update @ratesperday set rate=case ratetype when 'NOH' then 1 else 1.25 end;
with dayjoin as (
    select id, datepart(weekday,start_date) as wekkdaystart, datepart(hour, start_time) as weekhourstart, 
               datepart(weekday, end_date) as weekdayend, datepart(hour,end_time) as weekhourend,
               min(rateperdayid) as starthourid, max(rateperdayid) as endhourid from #JSC010_Tech_Time j inner join @ratesperday 
               r on (datepart(weekday, start_date)=r.weekdayno and datepart(hour, START_TIME)=r.hourno)
                 or (datepart(weekday, END_DATE)=r.weekdayno and datepart(hour, END_TIME)=r.hourno)
    group by  id, datepart(weekday,start_date), datepart(hour, start_time), datepart(weekday, end_date), datepart(hour,end_time))

--/*As the overtime rate types are known then they can be pivoted directly. The logic on the source derived table (src) manipulates the data which row by row checks the rate type per hour and also checks if that hour is a whole hour or a sub-set.*/
    select id, tech, email, client, project, task, description, billable, start_date, start_time, end_date, end_time, duration, --weekdayno, houroftheday,
    --rateperdayid, 
    [NOH], [OOH1], [OOH2] from
    (
    select j.ID, tech, email, client, project, task, description, billable, start_date, start_time, end_date, end_time, duration,
    --weekdayno, 
    --hourno AS houroftheday, rateperdayid,
                       case when datepart(minute,START_TIME)>0 and hourno=datepart(hour, start_time) and hourno=DATEPART(hour, j.end_time) and datepart(minute, duration)=0 then coalesce(cast(datepart(minute,duration)as float)/60, 0)
                            when datepart(minute,end_time)>0 and hourno=datepart(hour, end_time)-1 and datepart(minute, duration)>0 and DATEPART(hour, duration)=0 then coalesce(cast(datepart(minute,duration) as float)/60, 0)
                            when DATEPART(hour, duration)=0 and hourno = datepart(hour, START_TIME) and  hourno = DATEPART(hour, end_time) then coalesce(cast(datepart(minute,duration) as float)/60, 0)
                            when hourno=DATEPART(hour, end_time) and DATEPART(hour, duration)>0 and DATEPART(minute, end_time)>DATEPART(minute, start_time) and DATEPART(minute, start_time)=0 then coalesce(cast(datepart(minute,duration) as float)/60, 0)
                            when hourno=DATEPART(hour, start_time) and DATEPART(minute, duration)>0 and DATEPART(minute, start_time)=DATEPART(minute, duration) then coalesce(cast(datepart(minute,duration) as float)/60, 0)
                            when hourno = DATEPART(hour, start_time) and 60-DATEPART(minute, start_time)=DATEPART(minute, duration) then coalesce(cast(datepart(minute,duration) as float)/60, 0)
                            else cast(1 as float) end as hourno, ratetype 
    from #JSC010_Tech_Time j inner join
    dayjoin d on datepart(weekday, j.start_date)=d.wekkdaystart and datepart(weekday, j.end_date)=d.weekdayend
             and datepart(hour, j.start_time)=d.weekhourstart and datepart(hour, j.end_time)=d.weekhourend
             and d.id=j.ID
    inner join @ratesperday r on r.rateperdayid between d.starthourid and case 
    when DATEPART(hour, duration)=0 and datepart(hour, j.start_time)=datepart(hour, j.end_time) then d.endhourid
    when datepart(minute, j.end_time)>0 and datepart(minute, j.start_time)<>datepart(minute, j.end_time) and datepart(hour, duration)>0 then D.endhourid 
    else d.endhourid-1 end
    --where j.ID in (22)
    ) src
    pivot
    (sum(hourno) for ratetype in ([NOH], [OOH1], [OOH2])) piv

/*drop table to re-use in query batch for development purposes*/
drop table #JSC010_Tech_Time;

Result Set: