Cfrgtkky

I have a table with a column visit_date that is a datetime object with format YYYY-MM-DD HH:MI:SS that looks like the following:

visit_date         |visit_id

-------------------|-----

2010-11-01 00:02:00|92314

2010-11-01 23:05:21|23498

2010-11-01 12:42:31|12343

2010-11-02 05:13:21|79881

2010-11-02 14:35:15|22134

2010-11-02 16:12:23|12348

2010-11-03 01:22:44|12384

2010-11-03 05:23:41|12394

2010-11-03 15:13:55|99384

I would like to group by date and by 8-hr window on that date such that I have:

interval           |count

-------------------|-----

2010-11-01 00:00:00|1

2010-11-01 08:00:00|2

2010-11-01 16:00:00|3

2010-11-02 00:00:00|4

2010-11-02 08:00:00|5

2010-11-02 16:00:00|6

2010-11-03 00:00:00|7

2010-11-03 08:00:00|8

2010-11-03 16:00:00|9

My original query (using only dates) was:

SELECT CAST(visit_date as DATE), count(1) as count

FROM table

GROUP BY CAST(visit_date as DATE)

ORDER BY CAST(visit_date as DATE)

But that only groups by date.

Is there a recommended way to get interval counts for each interval per day? I have seen implementations using DATEADD and DATEPART but not sure which makes the most sense in this situation.

Thanks!

Add the hours in to what you group and count:

SELECT 

  CAST(visit_date as DATE), 

  HOUR(visit_date)/8 as ival8h

  count(1) as count

FROM table

  GROUP BY CAST(visit_date as DATE), HOUR(visit_date)/8

  ORDER BY CAST(visit_date as DATE)

The hour function returns the hour number of the passed date, divide it by 8 to get an int of the interval, so 0 to 7 becomes 0, 8 to 16 becomes 1 etc

If you want it back as a time pegged to a round 8h multiply it by 8 again and format it to NN:00:00, or add it to the date, thus:

SELECT 

  DATEADD(hour, (HOUR(visit_date)/8)*8, CAST(CAST(visit_date as DATE) as DATETIME) as quantized_date,

  count(1) as count

FROM table

  GROUP BY DATEADD(hour, (HOUR(visit_date)/8)*8, CAST(CAST(visit_date as DATE) as DATETIME)

  ORDER BY CAST(visit_date as DATE)

This basically rounds the hours down to the lesser 8h market and adds that to midnight. Two casts are required (probably) on the date because DATEADD won't add hours to a date, only a datetime but we need the cast to date to peg the tine element to midnight

If you want there to be a date and a 0 count for periods where no events took place, use a numbers table or row generator and create a sequence of dates to left join your real data onto, then count the real data grouped by the fake dates

Use a cross apply to form 4 shift boundary values, then use those in a case expression to genertate the group by values

SELECT

      case

         when visit_date >= s1 and visit_date < s2 then s1 

         when visit_date >= s2 and visit_date < s3 then s2

         when visit_date >= s3 and visit_date < s4 then s3 

      end as shift

    , count(1) as count

FROM mytable

CROSS APPLY (

    select 

          cast(CAST(visit_date as DATE)as datetime) s1

        , dateadd(hh,8,cast(CAST(visit_date as DATE)as datetime)) s2

        , dateadd(hh,16,cast(CAST(visit_date as DATE)as datetime)) s3

        , dateadd(hh,24,cast(CAST(visit_date as DATE)as datetime)) s4  

    ) ca

GROUP BY

      case

         when visit_date >= s1 and visit_date < s2 then s1 

         when visit_date >= s2 and visit_date < s3 then s2

         when visit_date >= s3 and visit_date < s4 then s3 

      end

ORDER BY shift

result:

+----+---------------------+-------+

|    |        shift        | count |

+----+---------------------+-------+

|  1 | 01.11.2010 00:00:00 |     1 |

|  2 | 01.11.2010 08:00:00 |     1 |

|  3 | 01.11.2010 16:00:00 |     1 |

|  4 | 02.11.2010 00:00:00 |     1 |

|  5 | 02.11.2010 08:00:00 |     1 |

|  6 | 02.11.2010 16:00:00 |     1 |

|  7 | 03.11.2010 00:00:00 |     2 |

|  8 | 03.11.2010 08:00:00 |     1 |

+----+---------------------+-------+

I think the canonical way in SQL Server is to use dateadd() and datediff():

select dateadd(hour, 0, 3 * (datediff(hour, 0, visit_date) / 3)) as day_hour8,

       count(*)

from t

group by dateadd(hour, 0, 3 * (datediff(hour, 0, visit_date) / 3))

order by day_hour8;