Counting Consecutive values

Database
N/A

Counting Consecutive values

Hi,

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

Thanks

2 REPLIES
Enthusiast

Re: Counting Consecutive values

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

,mytable b

where a.premium_flag='y'

and b.premium_flag='n'

and a.date <b.month

and  not exists

(sel 1

from mytable c

where b.name=c.name

and c.date >a.date

and c.date<b.date

)

Enthusiast

Re: Counting Consecutive values

in inner correalted query you'd prolly need a filter c.premium_flag='n'