Magic Query

To me this query is magic.

We were asked to summarize the number of requests going through our system for a month, but split into daytime (8am – 8pm), and nighttime (8pm – 8am).

Immediately I was thinking about writing a program, but this handy query (Oracle) does it in one go, including backtracking to the last day of the previous month to get values. (i.e. For December, we must get the values from midnight November 30, to 8am Dec1)

SELECT E.interface
, CASE WHEN TO_CHAR(time, 'HH24MISS') < '080000' THEN TO_CHAR(time-1, 'YYYY/MM/DD') ELSE TO_CHAR(time, 'YYYY/MM/DD') END "REPORT_DATE"
, COUNT(CASE WHEN TO_CHAR(time, 'HH24MISS') > '080000' AND TO_CHAR(time, 'HH24MISS') <= '200000' THEN '1' ELSE NULL END) "08AM_08PM"
, COUNT(CASE WHEN TO_CHAR(time, 'HH24MISS') > '200000' OR TO_CHAR(time, 'HH24MISS') <= '080000' THEN '1' ELSE NULL END) "08PM_08AM"
FROM transactions E
,projects P
WHERE E.project_name = P.Project_Name
AND P.seq = 1
AND E.status = 'Entry'
AND E.time > TO_DATE('20101201000000','YYYYMMDDHH24MISS')
AND E.time < TO_DATE('20101231235959','YYYYMMDDHH24MISS')
GROUP BY E.interface
, CASE WHEN TO_CHAR(TIME, 'HH24MISS') < '080000' THEN TO_CHAR(TIME-1, 'YYYY/MM/DD') ELSE TO_CHAR(TIME, 'YYYY/MM/DD') END
ORDER BY 1,2