got an sample query from web as below, but not able to standardize is it with my requirements.
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
TIME(I.TS),
S.NAME;
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
any of the column having having max value during the 5mins interval.
What does this tell us?
If there are 5 columns, i'd expect 5 max values? You mentoin a 5mins interval?
It is up to you to provide a complete and clear explanation.
Suggest you re-post the "data" using the "Code" tag to improve readability. Use Preview to see how your post will appear to the forum and when you are satisfied with the way the post appears, Submit.
the above table/view having 6 columns as described earlier,
one with time stamp and rest are with INT values.
table having data with 1min durations. Now i need to select the data within 5mins say from above
