Hi there,
I need to get the hourly counts of users between given date for which the following sql suggested here works great
WITH date_range AS
(
SELECT TRUNC (MIN (Date_Col), 'HH') AS first_date_wanted
, TRUNC (MAX (date_col), 'HH') AS last_date_wanted
FROM Table1
WHERE date_col between to_Date('01-June-2019','dd-Month-yyyy') and to_Date('08-June-2019','dd-Month-yyyy')
)
, times_wanted AS
(
SELECT first_date_wanted + ((LEVEL - 1) / 24) AS start_time
, first_date_wanted + ( LEVEL / 24) AS end_time
FROM date_range
CONNECT BY LEVEL <= 1 + (24 * (last_date_wanted - first_date_wanted))
)
SELECT to_Char(w.start_time,'dd-mon-yy hh24:mi:ss')
, COUNT (temp.date_col) AS users_accepted
FROM times_wanted w
LEFT OUTER JOIN table 1 temp ON temp.date_col >= w.start_time
AND date_col < w.end_time
GROUP BY w.start_time
ORDER BY 2 desc
This shows by the hour in a day the counts. I want to tweak this to get the counts by every 5 mins instead of the hour. I think we need to use something like 5/(24*60).
Do we need to modify this part? - CONNECT BY LEVEL <= 1 + (24 * (last_date_wanted - first_date_wanted))
thanks
Ryan