Re: Need query help to create intervals

[veerabhadrarao gandikota]

Hi Peter,

Thanks for your response. Can you please provide a query.

We are using below query but its not working properly.



SELECT ACTIVITY_LABEL, date_sub(min(ACTIVITY_TS),interval 1 second) as start_time, max(ACTIVITY_TS) as end_time from (

select ACTIVITY_LABEL,ACTIVITY_TS,

ROW_NUMBER() OVER (PARTITION BY DATE(ACTIVITY_TS) ORDER BY ACTIVITY_TS) AS seqnum,

ROW_NUMBER() OVER (PARTITION BY DATE(ACTIVITY_TS),activity_label ORDER BY ACTIVITY_TS) AS seqnum_2 from MyTable

order by ACTIVITY_TS

) group by ACTIVITY_LABEL, date(ACTIVITY_TS),(seqnum - seqnum_2)

order by start_time



Thanks,
Veera