Re: SQL count by time intervals

Yes Hans, that's a possibility.

I'm using Oracle 8i.

By the way all, my initial approach was borrowed from a SQLServer group which offered the following example which counts the number of times different people worked in each of three shifts:

  DECLARE @temp TABLE
  (
   UserName VARCHAR(100),
   StartDTM DATETIME,
   EndDTM DATETIME
  )

  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob',
'22-Jan-2005 9:26am', '22-Jan-2005 3:15pm');
  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob',
'21-Jan-2005 3:05pm', '21-Jan-2005 9:15pm');
  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob',
'20-Jan-2005 3:05pm', '20-Jan-2005 9:15pm');
  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob',
'19-Jan-2005 3:05pm', '19-Jan-2005 9:15pm');
  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob',
'10-Jan-2005 4:05pm', '19-Jan-2005 10:06pm');

  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Gary',
'22-Jan-2005 7:45am', '22-Jan-2005 2:25pm');
  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Gary',
'21-Jan-2005 9:08am', '21-Jan-2005 2:10pm');

  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Joe',
'23-Jan-2005 3:16am', '23-Jan-2005 1:11pm');

  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Fred',
'23-Jan-2005 11:47am', '23-Jan-2005 5:15pm');
  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Fred',
'24-Jan-2005 12:05am', '24-Jan-2005 7:34am');
  INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Fred',
'25-Jan-2005 12:05pm', '25-Jan-2005 9:10pm');

  SELECT
   UserName,

   SUM((CASE
    WHEN CONVERT(DATETIME, CONVERT(CHAR(10), StartDTM, 101) + ' 11:00:00 am') BETWEEN StartDTM AND EndDTM

     THEN 1
    ELSE 0
   END)) AS MorningShifts,

   SUM((CASE
    WHEN CONVERT(DATETIME, CONVERT(CHAR(10), StartDTM, 101) + ' 7:00:00 pm') BETWEEN StartDTM AND EndDTM

     THEN 1
    ELSE 0
   END)) AS AfternoonShift,

   SUM((CASE
    WHEN CONVERT(DATETIME, CONVERT(CHAR(10), StartDTM, 101) + ' 3:00:00 am') BETWEEN StartDTM AND EndDTM

     THEN 1
    ELSE 0
   END)) AS MidnightShift
  FROM
   @temp
  WHERE StartDTM BETWEEN '01/20/2005 12:00:00 am' AND '01/25/2005 11:59:59 pm'
  GROUP BY
   UserName

Running the script produces the following result:

Username	MorningShifts	AfternoonShift	MidnightShift
Bob	1	2	0
Fred	0	1	1
Gary	2	0	0
Joe	1	0	0