CODEWITHSUNDEEP
datetimetimesql-server-2008-r2
Peter Larsen
Peter Larsen

Reputation: 503

SQL Server 2008 R2 looking for a way to get the night hours for an employee

Using SQL Server 2008 R2 we are looking for a way to select the shift hours that an employee has that are during the night which in the this case 22.00 and 6.00 +1.

Our problem becomes how to get the hours when the shift crosses midnight or how we get the overlap when a shift begins 05.30 to 22.30 and has an overlap in both the beginning and end of the shift.

Here is an example, theses are the data available in the database and the result we are looking for:

        startDateTime     |       endDateTime         |   nightHours
--------------------------+---------------------------+----------------
 2012-07-04 05:00:00.000    2012-07-04 23:00:00.000          2 
 2012-07-04 18:00:00.000    2012-07-05 05:00:00.000          7

Does anyone have an example or a few good pointer that we can use.

Upvotes: 1

Views: 1230

Answers (2)

trailmax
trailmax

Reputation: 35116

I think, the best way would be a function that takes start time and end time of the shift. Then inside the function have 2 cases: first when shift starts and ends on the same day and another case when starts on one day and finishes on the next one.

For the case when it starts and finishes on the same day do

@TotalOvernightHours=0 

@AMDifference = Datediff(hh, @shiftStart, @6amOnThatDay);
if @AMDIfference > 0 than @TotalOvernightHours = @TotalOvernightHours + @AMDifference

@PMDifference Datediff(hh, @10pmOnThatDay, @ShiftEnd)
if @PMDifference > 0 than @TotalOvernightHours = @TotalOvernightHours + @PMDifference

For the case when start and finish are on different days pretend it is 2 shifts: first starts at @ShiftStart, but finishes at midnight. Second one starts at midnight, finishes at @ShiftEnd. And for every shift do apply the logic above.

In case you have shifts that a longer than 24 hours, break them up into smaller sub-shifts, where midnight is a divider. So if you have shift starting on 1 Jun 19:00 and finishing at 3 Jun 5:00 then you would end up with three sub-shifts:

  • 1 Jun 19:00 - 1 Jun 24:00
  • 2 Jun 00:00 - 2 Jun 24:00
  • 3 Jun 00:00 - 3 Jun 5:00

And for every sub-shift you do calculate the overnight hours.

I'd probably would write a function that calculates overnight hours for one 24hrs period and another function that breaks the whole shift into 24hrs chunks, then feeds it into the first function.

p.s. this is not sql, only pseudo-code. p.p.s. This would work only if you have ability to create functions. And it would get you a clean, easy-to ready code.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

This may be overly complex, but it does work. We use a number of CTEs to construct useful intermediate representations:

declare @Times table (
    ID int not null,
    StartTime datetime not null,
    EndTime datetime not null
)
insert into @Times (ID,StartTime,EndTime)
select 1,'2012-07-04T05:00:00.000','2012-07-04T23:00:00.000' union all
select 2,'2012-07-04T18:00:00.000','2012-07-05T05:00:00.000'

;With Start as (
    select MIN(DATEADD(day,DATEDIFF(day,0,StartTime),0)) as StartDay from @Times
), Ends as (
    select MAX(EndTime) EndTime from @Times
), Nights as (
    select DATEADD(hour,-2,StartDay) as NightStart,DATEADD(hour,6,StartDay) as NightEnd from Start
    union all
    select DATEADD(DAY,1,NightStart),DATEADD(DAY,1,NightEnd) from Nights n
    inner join Ends e on n.NightStart < e.EndTime
), Overlaps as (
    select
        t.ID,
        CASE WHEN n.NightStart > t.StartTime THEN n.NightStart ELSE t.StartTime END as StartPeriod,
        CASE WHEN n.NightEnd < t.EndTime THEN n.NightEnd ELSE t.EndTime END as EndPeriod
    from
        @Times t
            inner join
        Nights n
            on
                t.EndTime > n.NightStart and
                t.StartTime < n.NightEnd
), Totals as (
    select ID,SUM(DATEDIFF(hour,StartPeriod,EndPeriod)) as TotalHours
    from Overlaps
    group by ID
)
select
    *
from
    @Times t
        inner join
    Totals tot
        on
            t.ID = tot.ID

Result:

ID          StartTime               EndTime                 ID          TotalHours
----------- ----------------------- ----------------------- ----------- -----------
1           2012-07-04 05:00:00.000 2012-07-04 23:00:00.000 1           2
2           2012-07-04 18:00:00.000 2012-07-05 05:00:00.000 2           7

You'll note that I had to add an ID column in order to get my correlation to work.

The Start CTE finds the earliest applicable midnight. The End CTE finds the last time for which we need to find overlapping nights. Then, the recursive Nights CTE computes every night between those two points in time. We then join this back to the original table (in Overlaps) to find those periods in each night which apply. Finally, in Totals, we compute how many hours each overlapping period contributed.

This should work for multi-day events. You might want to change the Totals CTE to use minutes, or apply some other rounding functions, if you need to count partial hours.

Upvotes: 2

Related Questions