SQL Server Group by date and by time of day over a date range

Question

I'm not even sure if this can/should be done is SQL but here goes.

I have a table that stores a start date and an end date like so

userPingId    createdAt                    lastUpdatedAt
1             2017-10-17 11:31:52.160      2017-10-18 14:31:52.160

I want to return a result set that groups the results by date and if they were active between different points between the two date.

The different points are

• Morning - Before 12pm
• Afternoon - Between 12pm and 5pm
• Evening - After 5pm

So for example I would get the following results

sessionDate    morning    afternoon    evening
2017-10-17     1          1            1
2017-10-18     1          1            0

Here is what I have so far and I believe that it's quite close but the fact I can't get the results I need make me think that this might not be possible in SQL (btw i'm using a numbers lookup table in my query which I saw on another tutorial)

DECLARE @s DATE = '2017-01-01', @e DATE = '2018-01-01';
;WITH d(sessionDate) AS
(
  SELECT TOP (DATEDIFF(DAY, @s, @e) + 1) DATEADD(DAY, n-1, @s) 
  FROM dbo.Numbers ORDER BY n
)
SELECT 
d.sessionDate,
sum(case when 
(CONVERT(DATE, createdAt) = d.sessionDate AND datepart(hour, createdAt) < 12) 
OR (CONVERT(DATE, lastUpdatedAt) = d.sessionDate AND datepart(hour, lastUpdatedAt) < 12) 
then 1 else 0 end) as Morning,
sum(case when 
(datepart(hour, createdAt) >= 12 and datepart(hour, createdAt) < 17)
OR (datepart(hour, lastUpdatedAt) >= 12 and datepart(hour, lastUpdatedAt) < 17) 
OR (datepart(hour, createdAt) < 12 and datepart(hour, lastUpdatedAt) >= 17)
then 1 else 0 end) as Afternoon,
sum(case when datepart(hour, createdAt) >= 17 OR datepart(hour, lastUpdatedAt) >= 17 then 1 else 0 end) as Evening
FROM d
LEFT OUTER JOIN MYTABLE AS s
ON s.createdAt >= @s AND s.lastUpdatedAt <= @e
AND (CONVERT(DATE, s.createdAt) = d.sessionDate OR CONVERT(DATE, s.lastUpdatedAt) = d.sessionDate)
WHERE d.sessionDate >= @s AND d.sessionDate <= @e
AND userPingId = 49
GROUP BY d.sessionDate
ORDER BY d.sessionDate;

Answer 1

Building on what you started with the numbers table, you can add the time ranges to your adhoc calendar table using another common table expression using cross apply() and the table value constructor (values (...),(...)).

From there, you can use an inner join based on overlapping date ranges along with conditional aggregation to pivot the results:

declare @s datetime = '2017-01-01', @e datetime = '2018-01-01';

;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, d as (  /* adhoc date/numbers table */
  select top (datediff(day, @s, @e)+1) 
      SessionDate=convert(datetime,dateadd(day,row_number() over(order by (select 1))-1,@s))
  from n as deka cross join n as hecto cross join n as kilo
                 cross join n as tenK cross join n as hundredK
   order by SessionDate
)
, h as ( /* add time ranges to date table */
  select 
      SessionDate
    , StartDateTime = dateadd(hour,v.s,SessionDate)
    , EndDateTime   = dateadd(hour,v.e,SessionDate)
    , v.point
  from d
    cross apply (values 
        (0,12,'morning')
       ,(12,17,'afternoon')
       ,(17,24,'evening')
      ) v (s,e,point)
)

select
    t.userPingId
  , h.SessionDate
  , morning = count(case when point = 'morning' then 1 end)
  , afternoon = count(case when point = 'afternoon' then 1 end)
  , evening = count(case when point = 'evening' then 1 end)
from t
  inner join h
    on t.lastupdatedat >= h.startdatetime
   and h.enddatetime   > t.createdat 
group by t.userPingId, h.SessionDate

rextester demo: http://rextester.com/MVB77123

returns:

+------------+-------------+---------+-----------+---------+
| userPingId | SessionDate | morning | afternoon | evening |
+------------+-------------+---------+-----------+---------+
|          1 | 2017-10-17  |       1 |         1 |       1 |
|          1 | 2017-10-18  |       1 |         1 |       0 |
+------------+-------------+---------+-----------+---------+

---

Alternately, you could use pivot() instead of conditional aggregation in the final select:

select UserPingId, SessionDate, Morning, Afternoon, Evening
from (
  select
      t.userPingId
    , h.SessionDate
    , h.point
  from t
    inner join h
      on t.lastupdatedat >= h.startdatetime
     and h.enddatetime   > t.createdat 
  ) t
  pivot (count(point) for point in ([Morning], [Afternoon], [Evening])) p

rextester demo: http://rextester.com/SKLRG63092

Answer 2

You can using PIVOT on CTE's to derive solution to this problem.

Below is the test table

select * from ping

Below is the sql query

;with details as 
(
select userPingId, createdAt as presenceDate  , convert(date, createdAt) as 
onlyDate,
datepart(hour, createdAt) as onlyHour
from ping

union all

select userPingId, lastUpdatedAt as presenceDate , convert(date, 
lastUpdatedAt) as onlyDate,
datepart(hour, lastUpdatedAt) as onlyHour
from ping
) 
, cte as 
(
select onlyDate,count(*) as count,
case 
  when onlyHour between 0 and 12 then 'morning' 
  when onlyHour between 12 and 17 then 'afternoon' 
  when onlyHour>17 then 'evening' 


end as 'period'

from details
group by onlyDate,onlyHour
)

select onlyDate,  coalesce(morning,0) as morning, 
coalesce(afternoon,0) as afternoon , coalesce(evening,0) as evening from 
(
 select onlyDate, count,period  
 from cte ) src
 pivot
 (
  sum(count)
  for period in ([morning],[afternoon],[evening])

 ) p

Below is the final result

Answer 3

This is a fairly similar answer to the one already posted, I just wanted the practice with PIVOT :)

I use a separate table with the time sections in it. this is then cross joined with the number table to create a date and time range for bucketing. i join this to the data and then pivot it (example: https://data.stackexchange.com/stackoverflow/query/750496/bucketing-data-into-date-am-pm-evening-and-pivoting-results)

SELECT
  *
FROM (
    SELECT
      [userPingId],
      dt,
      [desc]
    FROM (
        SELECT
          DATEADD(D, number, @s) AS dt,
          CAST(DATEADD(D, number, @s) AS datetime) + CAST(s AS datetime) AS s,
          CAST(DATEADD(D, number, @s) AS datetime) + CAST(e AS datetime) AS e,
          [desc]
        FROM #numbers
        CROSS JOIN #times
        WHERE number < DATEDIFF(D, @s, @e)
        ) ts
    INNER JOIN #mytable AS m
      ON m.createdat < ts.e
      AND m.[lastUpdatedAt] >= ts.s
  ) src
PIVOT
(
COUNT([userPingId])

FOR [desc] IN ([am], [pm], [ev])
) piv;

the #times table is just:

s                   e                   desc
00:00:00.0000000    12:00:00.0000000    am
12:00:00.0000000    17:00:00.0000000    pm
17:00:00.0000000    23:59:59.0000000    ev