"Adanved" OLAP Window Query

Database
Enthusiast

"Adanved" OLAP Window Query

Hi,

I am trying to figure out, how to count "remaining/following" rows of a certain (logical) group in a table.

 

Think of a table like this

Event_Date	unique_Key	GroupKey1	GroupKey2	Following_in_Period
01.01.16  	A  		GK1		GK2		3
01.01.16 	B  		GK1  		GK2		2
10.01.16 C GK1 GK2 1
15.01.16 D GK1 GK2 0
03.02.16 E GK1 GK2 1
20.02.16 F GK1 GK2 0

My goal is to add the "Following_in_Period" field. This is the number of rows that follow a row, whithin a period of 30 days (of Event_Date), matching all logical Group Keys.

 

Notice the blue event (of 15.01.16) it receives "following 0" because the next row is out of that 30 days period, which would reset the counter.

 

I was playing with row_number() etc. but couldn't find a solution so far.

 

row_number() over(
		partition by
			GK1,
			GK2
		order by
			Event_Date desc
		reset when --????
			Event_Date between Event_Date + 1 and Event_Date + 31

 

Any ideas?

 

Thanks,

Roger