Re: Takers for a Friday SQL puzzle? Timestamps and recursion...

[Dave Hughes]

Oops - there goes me not looking at the spec properly again ... while
it may be pointless (the recursive solution is obviously soundly beaten
by Tonkuma and Lennart's elegant solutions), if you want pure times
without the dates out of the recursive version, just stick TIME()
around I.TS:

WITH SOURCE(ID, NAME, TS) AS (
VALUES
(12179, 'CSR INQRY', '2008-05-09 16:12:46.946471 '),
(12182, 'CSR INQRY', '2008-05-09 16:14:40.484615 '),
(12193, 'CSR INQRY', '2008-05-09 16:41:15.513504 '),
(12278, 'PURCHASE', '2008-05-10 08:58:48.528591 '),
(12281, 'CSR INQRY', '2008-05-10 09:02:27.578658 '),
(12292, 'CSR INQRY', '2008-05-10 09:10:18.026515 '),
(12331, 'CSR INQRY', '2008-05-10 14:33:07.467567 '),
(12377, 'CSR INQRY', '2008-05-12 09:08:40.549885 '),
(12430, 'PURCHASE', '2008-05-12 11:07:34.635433 '),
(12441, 'CSR INQRY', '2008-05-12 11:19:39.898771 '),
(12452, 'PURCHASE', '2008-05-12 12:05:26.131342 '),
(12476, 'PURCHASE', '2008-05-12 13:16:43.275927 ')
),
DATA AS (
SELECT ID, NAME, TIMESTAMP(TS) AS TS FROM SOURCE
),
RANGE AS (
-- Zero out the time so we wind up with nice 00,15,30,45
-- increments below; INTERVALS will generate rather more
-- intervals than needed with this, but that's okay
SELECT
TIMESTAMP(DATE( MIN(TS)), '00:00:00') AS MIN_TS,
TIMESTAMP(DATE( MAX(TS)) + 1 DAY, '00:00:00') AS MAX_TS
FROM DATA
),
INTERVALS(I, TS) AS (
-- I included to prevent infinite recursion warnings
SELECT 1, MIN_TS FROM RANGE
UNION ALL
SELECT I + 1, TS + 15 MINUTES FROM INTERVALS, RANGE
WHERE I < 100000 AND TS + 15 MINUTES < MAX_TS
)
SELECT
TIME(I.TS) AS TS,
S.NAME,
COUNT(*) AS COUNT
FROM
INTERVALS I
INNER JOIN DATA S
ON S.TS BETWEEN I.TS AND I.TS + 15 MINUTES - 1 MICROSECOND
GROUP BY
-- With apologies to Celko... ;-)
TIME(I.TS),
S.NAME;


Resulting in:

TS NAME COUNT······
-------- --------- -----------
08:45:00 PURCHASE 1
09:00:00 CSR INQRY 3
11:00:00 PURCHASE 1
11:15:00 CSR INQRY 1
12:00:00 PURCHASE 1
13:15:00 PURCHASE 1
14:30:00 CSR INQRY 1
16:00:00 CSR INQRY 2
16:30:00 CSR INQRY 1
9 record(s) selected.


Cheers,

Dave.