Help needed with Reset in partition

Database
Enthusiast

Help needed with Reset in partition

I'm trying to work out how to get a row number to reset inside a partition when a specified amount of time has passed between some timestamps. It doesn't seem to be working as intended.. Below is the statement I have and the table shows the actual row_number (TEST col) and the desired row number result I'm trying to get. Can anyone offer suggestions on how to get this to work?

,ROW_NUMBER() OVER(
PARTITION BY CUST_ACCT_NUMBR, CE_DT ORDER BY SR_OPEN_DT ASC
RESET WHEN ((SR_OPEN_DT - MIN(SR_OPEN_DT) OVER(PARTITION BY CUST_ACCT_NUMBR, CE_DT
ORDER BY SR_OPEN_DT ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) ) DAY(4) TO SECOND(0) >= '00 00:35:00'
)) AS TEST

EVENT_ID ID NUMBR OPEN_TS CE_DT ROW_CNT TEST Since Prev Row (Desired Result)
5809323 2077845 1-175676170359R2 5/5/2016 11:11 5/5/2016 1 1 0 00:00:00 1
5815416 2077845 1-175682895549R2 5/5/2016 13:26 5/5/2016 2 1 0 02:14:40 1
5820268 2077845 1-175688262765R2 5/5/2016 14:18 5/5/2016 3 1 0 00:52:32 1
5821723 2077845 1-175689890865R2 5/5/2016 14:18 5/5/2016 4 2 0 00:00:07 2
5826369 2077845 1-175694557613R2 5/5/2016 14:27 5/5/2016 5 3 0 00:08:21 3
5829083 2077845 1-175697304119R2 5/5/2016 15:16 5/5/2016 6 1 0 00:49:38 1
5834272 2077845 1-175701743290R2 5/5/2016 15:39 5/5/2016 7 2 0 00:22:08 2
5837190 2077845 1-175704282261R2 5/5/2016 16:01 5/5/2016 8 3 0 00:22:38 1
5844874 2077845 1-175709697872R2 5/5/2016 17:40 5/5/2016 9 1 0 01:38:18 1
5852754 2077845 1-175714480993R2 5/5/2016 17:42 5/5/2016 10 2 0 00:02:26 2
5852467 2077845 1-175714299623R2 5/5/2016 18:16 5/5/2016 11 3 0 00:33:51 1
5853743 2077845 1-175715087504R2 5/5/2016 18:24 5/5/2016 12 4 0 00:08:14 2
5867205 2077845 1-175721423591R2 5/5/2016 19:50 5/5/2016 13 1 0 01:25:33 1
5870740 2077845 1-175722981424R2 5/5/2016 20:09 5/5/2016 14 2 0 00:19:30 2
5865390 2077845 1-175720716789R2 5/5/2016 20:18 5/5/2016 15 3 0 00:08:51 3
2 REPLIES
Enthusiast

Re: Help needed with Reset in partition

Shamless bump

Junior Contributor

Re: Help needed with Reset in partition

The result matches exactly your description :)

Seems like you want to apply the 35 minutes based on the timestamp of the "first" row of a group.

You need a recursive query for that.