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 210.01.16 C GK1 GK2 115.01.16 D GK1 GK2 003.02.16 E GK1 GK2 120.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.
reset when --????
Event_Date between Event_Date + 1 and Event_Date + 31