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
rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.