I am trying to work out a calculation using SQL.
My database has the first 3 columns below and I need to work out the fourth:
Month Name Monthly Premium Paid Number of consecutive months paid
Jan 2010 Bob Y 1
Feb 2010 Bob Y 2
Mar 2010 Bob N 0
Apr 2010 Bob Y 1
May2010 Bob Y 2
Jul 2010 Bob N 0
I was hoping to do this by way of a sliding window using a single sql statement. Is this possible?
Any ideas would be greatly appreciated
I think you can do it rather easily using CSUM or you can try something like following "approach"
SEL name,month ,sum(1)
from mytable a
and a.date <b.month
and not exists
from mytable c
and c.date >a.date