Get counts grouped by time range

• Tony Farrell-305085

  Old Hand

  Points: 342

  More actions

  May 3, 2012 at 9:40 am

  #260970

  Hi everyone,

  I have a table that holds all the user log in data... basically, when a user logs into the system, the datetime stamp is written to the table.

  I need to query the data and get a count of logins per half hour block.

  I have performed the query that will count and display the timeslot and count, but... now here is the kick,

  the customer wants all the timeslots to be presented.....

  basically, if there is no entry in that timeslot, the result would be zero. but still be displayed.

  Here is my feeble attempt....

  SELECT DATEADD(mi,DATEDIFF(mi,0,TimeIn)/30*30,0) TimeIn, COUNT(*) AS TheCount

  FROM dbo.AdmissionProgram

  GROUP BY DATEADD(mi,DATEDIFF(mi,0,TimeIn)/30*30,0)

  ORDER BY timein

• Daxesh Patel

  Hall of Fame

  Points: 3208

  More actions

  May 3, 2012 at 10:00 am

  #1483160

  Create a static table with all time slots and do LEFT JOIN

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 3, 2012 at 10:05 am

  #1483167

  Could show you what needs to be done, but your post is light on information.

  Is this for a single day, multiple days, current day, yesterday, a specified day?

  Please read the first article I reference below in my signature block. It will help you with what needs to be posted and how to post it. Also, remember sample data needs to be sample data, not live production data. And, plesae post what the expected results should look like based on the sample data. Gives us something to check our work against.

• Tony Farrell-305085

  Old Hand

  Points: 342

  More actions

  May 3, 2012 at 10:18 am

  #1483181

  Sure -

  The user will select the day from the front end...

  This is the data i am drawing from...

  TimeIn

  2011-09-08 19:16:38.127

  2011-09-14 13:59:05.913

  2012-02-21 18:34:20.830

  2011-09-05 17:32:59.753

  2012-03-22 13:24:34.957

  2012-03-22 13:24:17.957

  2010-12-13 18:29:05.660

  2011-12-08 13:19:57.497

  2010-10-27 15:50:53.217

  2011-09-05 17:33:40.463

  2011-09-08 19:23:01.287

  2011-09-14 13:48:44.667

  2011-12-13 22:45:41.113

  2011-12-07 22:58:45.990

  2010-10-27 17:20:15.560

  2011-12-08 13:17:58.263

  2011-12-08 13:18:42.743

  2010-11-16 15:16:17.273

  2010-11-19 14:53:38.863

  2011-12-09 18:09:39.967

  This is what i would like the output to be

  2011-12-08 00:00:00.0000

  2011-12-08 00:30:00.0000

  2011-12-08 01:00:00.0000

  2011-12-08 01:30:00.0000

  2011-12-08 02:00:00.0000

  2011-12-08 02:30:00.0000

  2011-12-08 03:00:00.0000

  2011-12-08 03:30:00.0000

  2011-12-08 04:00:00.0000

  2011-12-08 04:30:00.0000

  2011-12-08 05:00:00.0000

  2011-12-08 05:30:00.0000

  2011-12-08 06:00:00.0000

  2011-12-08 06:30:00.0000

  2011-12-08 07:00:00.0000

  2011-12-08 07:30:00.0000

  2011-12-08 08:00:00.0000

  2011-12-08 08:30:00.0000

  2011-12-08 09:00:00.0000

  2011-12-08 09:30:00.0000

  2011-12-08 10:00:00.0000

  2011-12-08 10:30:00.0000

  2011-12-08 11:00:00.0000

  2011-12-08 11:30:00.0000

  2011-12-08 12:00:00.0000

  2011-12-08 12:30:00.0000

  2011-12-08 13:00:00.0003

  2011-12-08 13:30:00.0000

  2011-12-08 14:00:00.0000

  2011-12-08 14:30:00.0000

  2011-12-08 15:00:00.0000

  2011-12-08 15:30:00.0000

  2011-12-08 16:00:00.0000

  2011-12-08 16:30:00.0000

  2011-12-08 17:00:00.0000

  2011-12-08 17:30:00.0000

  2011-12-08 18:00:00.0000

  2011-12-08 18:30:00.0000

  2011-12-08 19:00:00.0000

  2011-12-08 19:30:00.0000

  2011-12-08 20:00:00.0000

  2011-12-08 20:30:00.0000

  2011-12-08 21:00:00.0000

  2011-12-08 21:30:00.0000

  2011-12-08 22:00:00.0000

  2011-12-08 22:30:00.0000

  2011-12-08 23:00:00.0000

  2011-12-08 23:30:00.0000

  Hope this helps

  take care

  tony

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 3, 2012 at 10:27 am

  #1483188

  Tony Farrell-305085 (5/3/2012)

  ---

  Sure -

  The user will select the day from the front end...

  This is the data i am drawing from...

  TimeIn

  2011-09-08 19:16:38.127

  2011-09-14 13:59:05.913

  2012-02-21 18:34:20.830

  2011-09-05 17:32:59.753

  2012-03-22 13:24:34.957

  2012-03-22 13:24:17.957

  2010-12-13 18:29:05.660

  2011-12-08 13:19:57.497

  2010-10-27 15:50:53.217

  2011-09-05 17:33:40.463

  2011-09-08 19:23:01.287

  2011-09-14 13:48:44.667

  2011-12-13 22:45:41.113

  2011-12-07 22:58:45.990

  2010-10-27 17:20:15.560

  2011-12-08 13:17:58.263

  2011-12-08 13:18:42.743

  2010-11-16 15:16:17.273

  2010-11-19 14:53:38.863

  2011-12-09 18:09:39.967

  This is what i would like the output to be

  2011-12-08 00:00:00.0000

  2011-12-08 00:30:00.0000

  2011-12-08 01:00:00.0000

  2011-12-08 01:30:00.0000

  2011-12-08 02:00:00.0000

  2011-12-08 02:30:00.0000

  2011-12-08 03:00:00.0000

  2011-12-08 03:30:00.0000

  2011-12-08 04:00:00.0000

  2011-12-08 04:30:00.0000

  2011-12-08 05:00:00.0000

  2011-12-08 05:30:00.0000

  2011-12-08 06:00:00.0000

  2011-12-08 06:30:00.0000

  2011-12-08 07:00:00.0000

  2011-12-08 07:30:00.0000

  2011-12-08 08:00:00.0000

  2011-12-08 08:30:00.0000

  2011-12-08 09:00:00.0000

  2011-12-08 09:30:00.0000

  2011-12-08 10:00:00.0000

  2011-12-08 10:30:00.0000

  2011-12-08 11:00:00.0000

  2011-12-08 11:30:00.0000

  2011-12-08 12:00:00.0000

  2011-12-08 12:30:00.0000

  2011-12-08 13:00:00.0003

  2011-12-08 13:30:00.0000

  2011-12-08 14:00:00.0000

  2011-12-08 14:30:00.0000

  2011-12-08 15:00:00.0000

  2011-12-08 15:30:00.0000

  2011-12-08 16:00:00.0000

  2011-12-08 16:30:00.0000

  2011-12-08 17:00:00.0000

  2011-12-08 17:30:00.0000

  2011-12-08 18:00:00.0000

  2011-12-08 18:30:00.0000

  2011-12-08 19:00:00.0000

  2011-12-08 19:30:00.0000

  2011-12-08 20:00:00.0000

  2011-12-08 20:30:00.0000

  2011-12-08 21:00:00.0000

  2011-12-08 21:30:00.0000

  2011-12-08 22:00:00.0000

  2011-12-08 22:30:00.0000

  2011-12-08 23:00:00.0000

  2011-12-08 23:30:00.0000

  Hope this helps

  take care

  tony

  Doesn't quite answer my questions. Your "sample" data has dates from all over the calendar but your "output" for only a single day.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 3, 2012 at 10:57 am

  #1483208

  Are you looking to get a result set with each half hour block for a given day and the count of rows in your table for each block?

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 3, 2012 at 11:13 am

  #1483217

  Just to be sure, my problem is that I'm not sure where to start to help you solve this problem and I need your help to do it. The more information you provide to clarify the problem or issue, the better.

• Tony Farrell-305085

  Old Hand

  Points: 342

  More actions

  May 3, 2012 at 11:15 am

  #1483221

  Hi Lynn,

  I am sorry....

  The user will select one day from the interface screen.

  The stored proc will take that date and gather only the logins for that date.

  Breaking the login data down to show the number of logins per half hour period for that day.

  When there are no logins for a half hour period, I still need to show the half hour period, but with a count of zero.

  Sorry for the problems

  take care

  tony

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 3, 2012 at 11:22 am

  #1483227

  Tony Farrell-305085 (5/3/2012)

  ---

  Hi Lynn,

  I am sorry....

  The user will select one day from the interface screen.

  The stored proc will take that date and gather only the logins for that date.

  Breaking the login data down to show the number of logins per half hour period for that day.

  When there are no logins for a half hour period, I still need to show the half hour period, but with a count of zero.

  Sorry for the problems

  take care

  tony

  No problems, just needed clarification so that we knew where to start. Thank you for the additional information, it is helpful.

• Daxesh Patel

  Hall of Fame

  Points: 3208

  More actions

  May 3, 2012 at 11:34 am

  #1483233

  You can create a static table for time slot as follow

  This works in SQL 2008

  create table #StaticTable(TimeSlot time)

  insert into #StaticTable

  select '00:00:00'

  Union ALL

  select '00:30:00'

  Union ALL

  select '01:00:00'

  Union ALL

  select '01:30:00'

  Union ALL

  select '02:00:00'

  Union ALL

  select '02:30:00'

  Union ALL

  select '03:00:00'

  Union ALL

  select '03:30:00'

  Union ALL

  select '04:00:00'

  Union ALL

  select '04:30:00'

  Union ALL

  select '05:00:00'

  Union ALL

  select '05:30:00'

  Union ALL

  select '06:00:00'

  Union ALL

  select '06:30:00'

  Union ALL

  select '07:00:00'

  Union ALL

  select '07:30:00'

  Union ALL

  select '08:00:00'

  Union ALL

  select '08:30:00'

  Union ALL

  select '09:00:00'

  Union ALL

  select '09:30:00'

  Union ALL

  select '10:00:00'

  Union ALL

  select '10:30:00'

  Union ALL

  select '11:00:00'

  Union ALL

  select '11:30:00'

  Union ALL

  select '12:00:00'

  Union ALL

  select '12:30:00'

  Union ALL

  select '13:00:00'

  Union ALL

  select '13:30:00'

  Union ALL

  select '14:00:00'

  Union ALL

  select '14:30:00'

  Union ALL

  select '15:00:00'

  Union ALL

  select '15:30:00'

  Union ALL

  select '16:00:00'

  Union ALL

  select '16:30:00'

  Union ALL

  select '17:00:00'

  Union ALL

  select '17:30:00'

  Union ALL

  select '18:00:00'

  Union ALL

  select '18:30:00'

  Union ALL

  select '19:00:00'

  Union ALL

  select '19:30:00'

  Union ALL

  select '20:00:00'

  Union ALL

  select '20:30:00'

  Union ALL

  select '21:00:00'

  Union ALL

  select '21:30:00'

  Union ALL

  select '22:00:00'

  Union ALL

  select '22:30:00'

  Union ALL

  select '23:00:00'

  Union ALL

  select '23:30:00'

  ----

  Declare @Date datetime

  select @Date = '2011-12-08'

  select @Date + a.TimeSlot TimeSlot,b.TheCount

  from #StaticTable a

  Left join

  (SELECT DATEADD(mi,DATEDIFF(mi,0,TimeIn)/30*30,0) TimeIn, COUNT(*) AS TheCount

  FROM dbo.AdmissionProgram

  where TimeIn between @Date and @Date+1

  GROUP BY DATEADD(mi,DATEDIFF(mi,0,TimeIn)/30*30,0)

  ) b on a.TimeSlot = cast(b.TimeIn as time)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 3, 2012 at 11:53 am

  #1483240

  Here is my solution:

  DECLARE @StartDate DATETIME;

  SET @StartDate = '20111208'; -- Sample start date

  WITH

  e1(n) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), -- 10 rows

  e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows

  e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows

  tally(n) AS (SELECT 0 UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e4)

  ,TimeBase AS (

  SELECT

  DATEADD(mi, n * 30, DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)) TimeReport

  FROM

  tally

  WHERE

  n < (DATEDIFF(hh, DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0), DATEADD(dd, DATEDIFF(dd, 0, @StartDate) + 1, 0)) * 2)

  ),

  TestData AS ( -- This cte should be replaced by your source table in the query below

  SELECT

  DATEADD(mi, (DATEDIFF(mi, 0, LoginDateTime) / 30) * 30, 0) TimeGroup,

  COUNT(*) Cnt

  FROM

  (

  SELECT '2011-09-08 19:16:38.127' UNION all

  SELECT '2011-09-14 13:59:05.913' UNION all

  SELECT '2012-02-21 18:34:20.830' UNION all

  SELECT '2011-09-05 17:32:59.753' UNION all

  SELECT '2012-03-22 13:24:34.957' UNION all

  SELECT '2012-03-22 13:24:17.957' UNION all

  SELECT '2010-12-13 18:29:05.660' UNION all

  SELECT '2011-12-08 13:19:57.497' UNION all

  SELECT '2010-10-27 15:50:53.217' UNION all

  SELECT '2011-09-05 17:33:40.463' UNION all

  SELECT '2011-09-08 19:23:01.287' UNION all

  SELECT '2011-09-14 13:48:44.667' UNION all

  SELECT '2011-12-13 22:45:41.113' UNION all

  SELECT '2011-12-07 22:58:45.990' UNION all

  SELECT '2010-10-27 17:20:15.560' UNION all

  SELECT '2011-12-08 13:17:58.263' UNION all

  SELECT '2011-12-08 13:18:42.743' UNION all

  SELECT '2010-11-16 15:16:17.273' UNION all

  SELECT '2010-11-19 14:53:38.863' UNION all

  SELECT '2011-12-09 18:09:39.967'

  ) dt(LoginDateTime)

  GROUP BY

  DATEADD(mi, (DATEDIFF(mi, 0, LoginDateTime) / 30) * 30, 0)

  )

  SELECT

  tb.TimeReport,

  ISNULL(td.Cnt,0) LoginCnt

  FROM

  TimeBase tb

  LEFT OUTER JOIN TestData td

  ON (tb.TimeReport = td.TimeGroup);

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 3, 2012 at 11:59 am

  #1483247

  Actually, in my code above, just insert your source table for the derived table in the from clause of the cte I marked for replacement.

  You may have to make some adjustments to the SELECT part as well.