x

get hours worked per day from startdate and enddate

How can I query my table to calculate the amount of hours worked from start_date and end_date that spans between 2 days. for example... eventdate = 2016-08-05 00:00:00.000 start_date = 2016-08-05 21:15:00.000 end_date = 2016-08-06 01:15:00.000

I can do the following : select datediff(hour, start_date, end_date) as Hours_worked and it gives me 4 but what I need is hours worked for the day of 2016-08-05 21:15:00.000 and hours_worked for the day of 2016-08-06 01:15:00.000 Also I need a new eventdate column to be populated as 2016-08-06 00:00:00.000 since the remaining of the end_date ended on 2016-08-06

more ▼

asked Aug 23, 2016 at 10:03 AM in Default

avatar image

tomekb19
41 4

@tomekb19 How would you need the hours to be displayed? Using datediff(hour, start_date, end_date) is not a good idea because there is no way to get correct results reliably. For example, in your case the 4 hours is a correct result but only by accident, it works only because the sample happens to be conveniently manipulated to have the same minutes of the hour. If the start date were 21:00:00 and the end date were, say, 01:59 then datediff would still return 4 hours while in reality the number of hours is only 1 minute shy of 5. Please let me know. Also, is there any possibility of the event spanning more than 2 days? If so then the record has to be split not in 2 but into as many records as there are number of days the event spans, right? In this case every intermediate day should report 24 hours while first and last days the hours from start to first midnight and from last midnight till the end date respectively. Is this assumption correct? Please provide some details.

Aug 23, 2016 at 03:48 PM Oleg

Hi Oleg thanks for the reply....the way I need the hours displayed is if someone worked 2 hours and 45 minutes it would show 2.75. Also you are correct with event spanning over 2 days because if a nurse works 12 hour shifts on 8/22 at 9pm to 8/23 9am then again on 8/23 8pm to 8/24 8am they would have worked 7 hours on 8/23.

Aug 23, 2016 at 05:20 PM tomekb19

@Kev Riley ♦♦ Not sure what happened but there was my comment here yesterday and now it is gone. The comment included some questions I had to the OP, I was waiting for some input before attempting to answer, but now the comment is gone. Please let me know whether there is any way to restore it. Thank you.

Aug 24, 2016 at 12:48 PM Oleg

@agotomekb19 I am not sure I follow the example. If a nurse worked 2 shifts as you described then she certainly did not work 7 hours on 8/23, but rather worked 3 hours on 8/22, then worked 13 hours on 8/23 (9 hours midnight till 9 AM of 8/23, and 4 hours 8 PM till midnight same day for a total of 13), and then worked 8 hours on 8/24 (midnight till 8 AM). All that totals to 24, 3 + 13 + 8 = 24, which makes sense because two 12 hour shifts mean 24 hours total. Then I am not sure what does "worked 7 hours on 8/23" mean?

In this specific scenario, would you need to have the results displayed in 3 records (midnight to 9 AM and 8 PM to midnight combined) or 4 records? For now, I assume that you need 4 records like this:

event_id    start_date          end_date            span
----------- ------------------- ------------------- -----------
1           2016-08-22 21:00:00 2016-08-23 00:00:00 3
1           2016-08-23 00:00:00 2016-08-23 09:00:00 9
2           2016-08-23 20:00:00 2016-08-24 00:00:00 4
2           2016-08-24 00:00:00 2016-08-24 08:00:00 8

Please let me know whether this assumption is correct.

Aug 25, 2016 at 12:14 PM Oleg

You are right i miss typed and meant to put 13 hours instead of 7

Aug 25, 2016 at 07:57 PM tomekb19
show all comments (comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I will make some assumptions about the table structure to accommodate the script in this answer. The assumptions are as follows:

  1. The table has columns named EventDate, [start_date] and end_date, all of which are datetime and the EventDate does not have a time part.

  2. The database does not have a table representing the calendar with one row for each day, and so I will use the CTE to build the date range on the fly.

  3. The shifts do not span more than 2 days. Basically, they may start some time one day and finish next day, kind of like the graveyard shifts

  4. Some shifts do not span more than one day (day shifts)

  5. The table name I will use is WorkShifts

  6. The working hours are calculated as decimals with 2 digits past the decimal point

The idea is to join the WorkShifts table with calendar days on the condition that the event starts or ends on the matching calendar date excluding extra matches for those occasions when the event ends exactly at midnight, in which case the extra record is not actually needed. This allows each event which begins and ends on the same date (or ends at midnight which is technically the next day but we will consider it the same day) to be represented by one row in the results, while the event spanning over one day will be represented by 2 rows. The hours worked for the events not spanning over one day are simply the hours from start to end of the event. For events which end up split into 2 rows, the hours of the first match are the hours from start till midnight, while the hours for the second match are the hours from midnight till end_date. Here is the script:

 ;with dates as (
     -- if there is a calendar table in the database with one row for each day then use it
     -- otherwise, this select is used to build the date range. Modify the numbers as needed
     select top 31
         dateadd(day, number, '20160801') CalendarDate
         from master.dbo.spt_values where type = 'P'
 )
     select
         e.*, d.CalendarDate NewEventDate, 
         -- if the event and calendar dates are the same then the number of hours
         -- are hours from start date till the smallest of (end_date or midnight),
         -- otherwise the hours are simply the hours from midnight till end_date
         cast (
             case
                 when e.EvenDate = d.CalendarDate then datediff(second, e.[start_date], 
                     case when e.end_date > d.CalendarDate + 1 then d.CalendarDate + 1 else e.end_date end)
                 else datediff(second, d.CalendarDate, e.end_date)
             end / 3600. as decimal(10, 2)
         ) diff
         from dbo.WorkEvents e inner join dates d
             on dateadd(day, datediff(day, 0, e.[start_date]), 0) = d.CalendarDate or 
             (
                 dateadd(day, datediff(day, 0, e.end_date), 0) = d.CalendarDate
                 and d.CalendarDate <> e.end_date -- remove extra match for shift ending at midnight
             );

The script uses the dateadd-datediff combination to strip the time part from the datetime values. This is more efficient than other methods. Alternatively, the cast(SomeValue as date) may be used but the dateadd-datediff combination appears to be faster. The start_date is a reserved word in T-SQL so there are brackets around it.

Hope this helps,

Oleg

more ▼

answered Aug 27, 2016 at 08:14 PM

avatar image

Oleg
18.9k 3 7 28

Thanks Oleg this is very helpful, saved me a lot of headaches.

Aug 29, 2016 at 04:01 PM tomekb19

@tomekb19 I am glad I was able to help. Accepting the answer would be nice though, not only because it will bump my karma a little bit, but also because it might be easier for someone else looking for a solution to the similar problem in the future.

Aug 29, 2016 at 04:29 PM Oleg
(comments are locked)
10|1200 characters needed characters left

I generated an example of how I'd approach this below, using 21:00 to 01:59. This will round to the nearest .25 of an hour, you can remove the ROUND statement to get the exact amount of the hour if you want.

 DECLARE @StartDate DATETIME = '2016-08-05 21:15:00.000'
 DECLARE @EndDate DATETIME = '2016-08-06 01:59:00.000'
 
 SELECT  ROUND(( DATEDIFF(MINUTE, @StartDate, @EndDate) / 60.0 ) * 4, 0) / 4
more ▼

answered Aug 25, 2016 at 10:06 PM

avatar image

Arcanas
281 1 5 9

@Arcanas It appears that it is not what the OP is asking though. What is actually needed is the script to split the shifts spanning more than one day into 2 records, only then calculating the hours worked for each part of the shift.

Aug 26, 2016 at 02:35 PM Oleg

You are correct sir, I misread the original question. I'll blame it on lack of sleep ;)

Aug 26, 2016 at 02:41 PM Arcanas
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x88
x32

asked: Aug 23, 2016 at 10:03 AM

Seen: 107 times

Last Updated: Aug 29, 2016 at 04:29 PM

Copyright 2017 Redgate Software. Privacy Policy