sql server - Conditional counting based on comparison to previous row sql -

let's start sample of data i'm working with:

    policy no | start date     1         | 2/15/2006     1         | 2/15/2009     1         | 2/15/2012              2         | 3/15/2006     3         | 3/19/2006     3         | 3/19/2012     4         | 3/31/2006     4         | 3/31/2009 

i'm trying write code in sql server 2008 counts few things. principle policyholder's earliest start date when policy began. every 3 years increase offered client. if agree increase, start date refreshed same date original, 3 years later. if decline, nothing added database @ all.

i'm trying not count number of times customer accepted offer (or increased start date 3 years), separate out first offer or second offer. taking original start date , dividing number of days between , 1095 gets me total number of offers, i've gotten far. want compare each policy number 1 before see if it's same (it's ordered policy number), count date change in new "accepted" column , count times didn't change have "declined".

is case need self-join table compare dates? or there easier way?

are looking :-

set nocount on;  declare @test table (      policyno       int     ,startdate      date )  declare @policywithinc table (      rowid          int identity(1,1) primary key     ,policyno       int     ,startdate      date )  insert @test(policyno,startdate) values  (1,'2/15/2006') ,(1,'2/15/2009') ,(1,'2/15/2012')          ,(2,'3/15/2006') ,(3,'3/19/2006') ,(3,'3/19/2012') ,(4,'3/31/2006') ,(4,'3/31/2009')  insert @policywithinc(policyno,startdate) select   t.policyno         ,t.startdate    @test t  select   pw.policyno         ,sum(case when datediff(year,t.startdate, pw.startdate) = 3 1 else 0 end) datearrived         ,sum(case when datediff(year,t.startdate, pw.startdate) > 3 1 else 0 end) datenotarrived         ,sum(case when isnull(datediff(year,t.startdate,pw.startdate),0) = 3 1 else 0 end) years3incrementcount    @policywithinc pw         left join @policywithinc t on pw.policyno = t.policyno , pw.rowid = (t.rowid + 1) group pw.policyno