Query Help

• Wednesday, January 09, 2013 7:43 PM

   

   
  

  0

  I'm having a hard time with a SQL query.  The column of data I have is in a date / time stamp format (YYYY-MM-DD HH:MM:SS)
  
  Example of the column of data:
  
  2011-11-28 10:08:35.000
  2011-12-07 03:25:30.787
  2011-12-07 03:40:48.357
  2011-12-08 23:30:19.363
  2011-12-10 10:28:34.673
  2011-12-12 19:51:36.523
  2012-01-04 09:03:17.947
  2012-01-04 14:51:22.000
  2012-01-05 16:46:36.770
  2012-01-05 16:55:15.583
  
  
  What I'm trying to do is query this one column to get a count of the fields between a certain time range.  For example, the query would be returned as:
  
  --------------------------------
  | TIME | COUNT
  --------------------------------
  | 12 - 12:59 AM | 256
  | 01 - 01:59 AM | 123
  | 02 - 02:59 AM | 54
  | 03 - 03:59 AM | 400
  | 04 - 04:59 AM | 423
  | 05 - 05:59 AM | 400
  | 06 - 06:59 AM | 423
  
  .... And so on all the way around the clock
  
  The only thing I've gotten so far is 
  
  SELECT
  CASE
  WHEN CAST(SubmitTime as TIME) BETWEEN '00:00:00' AND '00:59:59' THEN '12 - 12:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '01:00:00' AND '01:59:59' THEN '1 - 1:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '02:00:00' AND '02:59:59' THEN '2 - 2:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '03:00:00' AND '03:59:59' THEN '3 - 3:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '04:00:00' AND '04:59:59' THEN '4 - 4:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '05:00:00' AND '05:59:59' THEN '5 - 5:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '06:00:00' AND '06:59:59' THEN '6 - 6:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '07:00:00' AND '07:59:59' THEN '7 - 7:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '08:00:00' AND '08:59:59' THEN '8 - 8:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '09:00:00' AND '09:59:59' THEN '9 - 9:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '10:00:00' AND '10:59:59' THEN '10 - 10:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '11:00:00' AND '11:59:59' THEN '11 - 11:59 AM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '12:00:00' AND '12:59:59' THEN '12 - 12:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '13:00:00' AND '13:59:59' THEN '1 - 1:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '14:00:00' AND '14:59:59' THEN '2 - 2:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '15:00:00' AND '15:59:59' THEN '3 - 3:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '16:00:00' AND '16:59:59' THEN '4 - 4:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '17:00:00' AND '17:59:59' THEN '5 - 5:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '18:00:00' AND '18:59:59' THEN '6 - 6:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '19:00:00' AND '19:59:59' THEN '7 - 7:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '20:00:00' AND '20:59:59' THEN '8 - 8:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '21:00:00' AND '21:59:59' THEN '9 - 9:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '22:00:00' AND '22:59:59' THEN '10 - 10:59 PM'
  WHEN CAST(SubmitTime as TIME) BETWEEN '23:00:00' AND '23:59:59' THEN '11 - 11:59 PM'
  END
  FROM table
  
  Any help / guidance would be greatly appreciated.

  • Reply
  • Quote