# question

## 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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
@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.
0 Likes 0 ·
·
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.
0 Likes 0 ·
·
@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.
0 Likes 0 ·
·
@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.
0 Likes 0 ·
·
You are right i miss typed and meant to put 13 hours instead of 7
0 Likes 0 ·

·
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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Thanks Oleg this is very helpful, saved me a lot of headaches.
0 Likes 0 ·
·
@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.
0 Likes 0 ·
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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
@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.
0 Likes 0 ·
·
You are correct sir, I misread the original question. I'll blame it on lack of sleep ;)
0 Likes 0 ·