DAY shift calculations

Question

Input table

Start time     End Time
8/12/14 17:00  8/14/14 12:00

I need to show the output as below

Date     11:00 to 23:00    23:00 to 11:00
8/12/14        6                1
8/13/14        12               12
8/14/14        1                11

I know you all will say "What did you try ?"

But the answer "I Can't think of where to start"

Answer 1

The following shows one way to do with using a Tally tables (aka "table of numbers" table.)

Key assumptions I made:

• Start and End times are both known
• Start and End times are for precise hours (e.g. 1:00, 2:00, and not 13:23 or 00:00.00.007). (This is why I used the smalldatetime datatype)
• End time is always greater than Start time

I went further and assumed that the data is stored in a table for multiple entities--that is, you'd ultimately want to process multiple items in one query. If you only ever want to do this for one item at a time, (a) the query below can be readily chopped down, and (b) it'd probably be easier to do in C# or whatever the calling language is.

Setting up testing data:

--  DROP TABLE Testing
CREATE TABLE Testing
 (
   EntryId    int  not null
  ,StartTime  smalldatetime  not null
  ,EndTime    smalldatetime  not null
 )

INSERT Testing values
  (1, 'Aug 12, 2014 17:00', 'Aug 14, 2014  12:00')  --  Original problem
 ,(2, 'Aug 11, 2014 00:00', 'Aug 11, 2014  23:00')  --  23 hours
 ,(3, 'Aug 11, 2014 00:00', 'Aug 12, 2014  00:00')  --  24 hour shift
 ,(4, 'Aug 11, 2014 12:00', 'Aug 12, 2014  12:00')  --  Noon to Noon

 ,(11, 'Aug 22, 2014 4:00',  'Aug 22, 2014  5:00')  --  One-hour problem cases
 ,(12, 'Aug 22, 2014 10:00', 'Aug 22, 2014  11:00') --  One-hour problem cases
 ,(13, 'Aug 22, 2014 11:00', 'Aug 22, 2014  12:00') --  One-hour problem cases
 ,(14, 'Aug 22, 2014 12:00', 'Aug 22, 2014  13:00') --  One-hour problem cases

 ,(21, 'Aug 23, 2014 18:00', 'Aug 23, 2014  19:00') --  One-hour problem cases
 ,(22, 'Aug 23, 2014 22:00', 'Aug 23, 2014  23:00') --  One-hour problem cases
 ,(23, 'Aug 23, 2014 23:00', 'Aug 24, 2014  00:00') --  One-hour problem cases
 ,(24, 'Aug 24, 2014 00:00', 'Aug 24, 2014   1:00') --  One-hour problem cases

My routine:

DECLARE
  @Earliest smalldatetime
 ,@Latest   smalldatetime


--  This could be thrown in as a first CTE, but doing so would make the overall query that much less comprehensible.
SELECT
   @Earliest = min(StartTime)
  ,@Latest   = max(EndTime)
 from Testing
 --where <filtering criteria, if you're not parsing the whole table)

;WITH
  Pass0 as (select 1 as C union all select 1), --2 rows
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
  Tally as (select row_number() over(order by C) as Number from Pass4),
  DateRange as (select
                   dateadd(hh, ta.Number, @Earliest) ShiftHour
                 from Tally ta
                 where dateadd(hh, ta.Number, @Earliest) <= @Latest)
SELECT
   te.EntryId
  ,cast(dateadd(hh, -1, dr.Shifthour) as date)  [Date]
  ,sum(case when datepart(hh, dateadd(hh, -1, dr.Shifthour)) between 11 and 22 then 1 else 0 end)  [11:00 to 23:00]
  ,sum(case when datepart(hh, dateadd(hh, -1, dr.Shifthour)) between 11 and 22 then 0 else 1 end)  [23:00 to 11:00]
 from Testing te
  inner join DateRange dr
   on dr.ShiftHour > te.StartTime
    and dr.ShiftHour <= te.Endtime
 group by
   te.EntryId
  ,cast(dateadd(hh, -1, dr.Shifthour) as date)
 order by 
   te.EntryId
  ,cast(dateadd(hh, -1, dr.Shifthour) as date)

Plug this in to show the results without the grouping, invaluable in figuring out what's going on in there:

;WITH
  Pass0 as (select 1 as C union all select 1), --2 rows
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
  Tally as (select row_number() over(order by C) as Number from Pass4),
  DateRange as (select
                   dateadd(hh, ta.Number, @Earliest) ShiftHour
                 from Tally ta
                 where dateadd(hh, ta.Number, @Earliest) <= @Latest)
SELECT
    te.EntryId
   ,dateadd(hh, -1, dr.Shifthour)
   ,cast(dateadd(hh, -1, dr.Shifthour) as date)  [Date]
   ,datepart(hh, dateadd(hh, -1, dr.Shifthour))
  from Testing te
   inner join DateRange dr
    on dr.ShiftHour > te.StartTime
     and dr.ShiftHour <= te.Endtime
  order by 
    te.EntryId
   ,cast(dateadd(hh, -1, dr.Shifthour) as date)
   ,dateadd(hh, -1, dr.Shifthour)

The hard parts were:

• Getting the 24th hour (Aug 23, 00:00) into the previous day (Aug 24). That's why I shift everything back one day [edit] one HOUR
• < and > logic when dealing with multiple entries
• Reconciling with the fact that the results for a "One day Noon to Noon" sample just looks goofy.

Yeah, this may be over-engineering, but some times you just have to give it a try. I couldn't say how well it would perform on large datasets, and if your first and last dates are more than four years apart you'll need to add "Pass5" when building the tally table.

Answer 2

Using recursive CTE:

SQL Fiddle

MS SQL Server 2012 Schema Setup:

Query 1:

DECLARE @StartDate DateTime = '2014-08-12 17:00'
DECLARE @EndDate DateTime = '2014-08-14 12:00'


;WITH CTE
AS
(
    SELECT @StartDate AS [Date], 
           CAST(@StartDate As time) AS [Time]

    UNION ALL

    SELECT DATEADD(HH, 1, [Date]) As [Date], 
           CAST(DATEADD(HH, 1, [Date]) AS Time) AS [Time]
    FROM CTE
    WHERE DATEADD(HH, 1, [Date]) < @EndDate
)
SELECT CAST([Date] AS Date) [Date], 
        SUM(CASE WHEN [Time] BETWEEN '11:00' AND '22:59' 
                THEN 1 ELSE 0 END) as [11:00 to 23:00],
        SUM(CASE WHEN [Time] BETWEEN '00:00' AND '10:59' 
              THEN 1
                WHEN  [Time] Between '23:00' and '23:59' THEN 1
       ELSE 0 END) as [23:00 to 11:00]
FROM CTE
GROUP BY CAST([Date] AS Date) 
ORDER BY [Date]

Results:

|       DATE | 11:00 TO 23:00 | 23:00 TO 11:00 |
|------------|----------------|----------------|
| 2014-08-12 |              6 |              1 |
| 2014-08-13 |             12 |             12 |
| 2014-08-14 |              1 |             11 |

Answer 3

DECLARE @StartTime DATETIME='08/01/2013 00:00:00'
    ,@EndTime DATETIME='08/03/2013 23:00:00'
DECLARE @StartDate DATE,
    @Starthour INT,
    @Startmin INT,
    @EndDate DATE,
    @Endhour INT,
    @Endmin INT

SELECT @StartDate=CONVERT(DATE,@StartTime,101),@Starthour=DATEPART(HOUR,@StartTime),@Startmin=DATEPART(MINUTE,@StartTime)
SELECT @EndDate=CONVERT(DATE,@EndTime,101),@Endhour=DATEPART(HOUR,@EndTime),@Endmin=DATEPART(MINUTE,@EndTime)

DECLARE @T TABLE (StartTime DATETIME, EndTime DATETIME)
INSERT @T VALUES (DATEADD(MINUTE,-@Startmin,@StartTime), DATEADD(MINUTE,-@Endmin,@EndTime))

DECLARE @Final1 TABLE (ShiftDate DATETIME,[11:00 to 23:00] INT,[23:00 to 11:00] INT)
DECLARE @Final2 TABLE (ShiftDate DATETIME,[11:00 to 23:00] INT,[23:00 to 11:00] INT)
DECLARE @Final3 TABLE (ShiftDate DATETIME,[11:00 to 23:00] INT,[23:00 to 11:00] INT)

;WITH CTE AS
(
    SELECT StartTime AS dt, EndTime,StartTime
    FROM @T
    UNION  ALL
    SELECT DATEADD(MINUTE, 60, dt) AS dt, EndTime,DATEADD(MINUTE, 60,StartTime) AS StartTime
    FROM CTE
    WHERE dt < DATEADD(HOUR, -1, EndTime)
)
INSERT INTO @Final1([11:00 to 23:00],[23:00 to 11:00],ShiftDate)
SELECT 
    CASE 
      WHEN CAST(dt AS TIME) >= '11:00' AND CAST(dt AS TIME) < '23:00' THEN 1 
      ELSE 0 
    END AS '11:00 to 23:00',
    CASE 
      WHEN CAST(dt AS TIME) >= '11:00' AND CAST(dt AS TIME) < '23:00' THEN 0 
      ELSE 1 
    END AS '23:00 to 11:00'
    ,StartTime
FROM CTE


INSERT INTO @Final2([11:00 to 23:00],[23:00 to 11:00],ShiftDate)
SELECT SUM([11:00 to 23:00])*60 AS [11:00 to 23:00]
    ,SUM([23:00 to 11:00])*60 AS [23:00 to 11:00]
    ,CONVERT(DATE,DATEADD(MINUTE,-660,ShiftDate)) AS ShiftDate
FROM @Final1
GROUP BY CONVERT(DATE,DATEADD(MINUTE,-660,ShiftDate))


INSERT INTO @Final3([11:00 to 23:00],[23:00 to 11:00],ShiftDate)
SELECT CASE WHEN ShiftDate=@StartDate AND @Starthour BETWEEN 11 AND 22 THEN [11:00 to 23:00]-@Startmin ELSE [11:00 to 23:00] END AS [11:00 to 23:00],
        CASE WHEN ShiftDate=@StartDate AND @Starthour BETWEEN 23 AND 10 THEN [23:00 to 11:00]-@Startmin ELSE [23:00 to 11:00] END AS [23:00 to 11:00],
        ShiftDate
FROM @Final2


SELECT ShiftDate,
    CONVERT(VARCHAR(10),
                (CASE WHEN ShiftDate=@EndDate AND @Endhour BETWEEN 11 AND 22 
                    THEN [11:00 to 23:00]+@Endmin 
                ELSE [11:00 to 23:00] END)) AS [11:00 to 23:00],
    CONVERT(VARCHAR(10),
            (CASE WHEN ShiftDate=@EndDate AND @Endhour BETWEEN 23 AND 10 
                THEN [23:00 to 11:00]+@Endmin 
            ELSE [23:00 to 11:00] END)) AS [23:00 to 11:00]
FROM @Final3