CODEWITHSUNDEEP
sql-serversql-server-2008date
DemonLlama
DemonLlama

Reputation: 47

Conditional counting based on comparison to previous row sql

Let's start with a sample of the 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 to write code in SQL Server 2008 that counts a few things. The principle is that the policyholder's earliest start date is when the policy began. Every three years an increase is offered to the client. If they agree to the increase, the start date is refreshed with the same date as the original, three years later. If they decline, nothing is added to the database at all.

I'm trying to not only count the number of times a customer accepted the offer (or increased the start date by three years), but separate it out by first offer or second offer. Taking the original start date and dividing the number of days between now and then by 1095 gets me the total number of offers, so I've gotten that far. What I really want it to do is compare each policy number to the one before it to see if it's the same (it's already ordered by policy number), then count the date change in a new "accepted" column and count the times it didn't change but could have as "declined".

Is this a case where I would need to self-join the table to itself to compare the dates? Or is there an easier way?

Upvotes: 0

Views: 95

Answers (2)

SouravA
SouravA

Reputation: 5243

Probably below could help:

Set Nocount On;

Declare @Test Table
(
     PolicyNo       Int
    ,StartDate      Date
)

Insert Into @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')

select PolicyNo, StartDate, dateadd(yy, 3, StartDate)Offer1, dateadd(yy, 6, StartDate)Offer2, dateadd(yy, 9, StartDate)Offer3  from 
(select * , row_number() over (partition by PolicyNo order by StartDate) rn from @Test)A
where rn = 1

select 
    count(*) * 3 TotalOffersMade,
    count(Data1.StartDate) FirstOfferAccepted, 
    count(Data2.StartDate) SecondOfferAccepted, 
    count(Data3.StartDate) ThirdOfferAccepted,
    count(*) - count(Data1.StartDate) FirstOfferDeclined,
    count(*) - count(Data2.StartDate) SecondOfferDeclined,
    count(*) - count(Data3.StartDate) ThirdOfferDeclined
from 
    (
    select PolicyNo, StartDate, dateadd(yy, 3, StartDate)Offer1, dateadd(yy, 6, StartDate)Offer2, dateadd(yy, 9, StartDate)Offer3  from 
    (select * , row_number() over (partition by PolicyNo order by StartDate) rn from @Test)A
    where rn = 1
    )Offers 
LEFT JOIN
    @Test Data1 
    on Offers.PolicyNo = Data1.PolicyNo and Offers.Offer1 = Data1.StartDate
LEFT JOIN
    @Test Data2
    on Offers.PolicyNo = Data2.PolicyNo and Offers.Offer2 = Data2.StartDate
LEFT JOIN
    @Test Data3
    on Offers.PolicyNo = Data3.PolicyNo and Offers.Offer3 = Data3.StartDate

Upvotes: 1

Mihir Shah
Mihir Shah

Reputation: 988

are you looking for this :-

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 Into @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 Into @PolicyWithInc(PolicyNo,StartDate)
Select   t.PolicyNo
        ,t.StartDate
From    @Test As t

Select   pw.PolicyNo
        ,Sum(Case When Datediff(Year,t.StartDate, pw.StartDate) = 3 Then 1 Else 0 End) As DateArrived
        ,Sum(Case When Datediff(Year,t.StartDate, pw.StartDate) > 3 Then 1 Else 0 End) As DateNotArrived
        ,Sum(Case When Isnull(Datediff(Year,t.StartDate,pw.StartDate),0) = 3 Then 1 Else 0 End) As Years3IncrementCount
From    @PolicyWithInc As pw
        Left Join @PolicyWithInc As t On pw.PolicyNo = t.PolicyNo And pw.RowId = (t.RowId + 1)
Group By pw.PolicyNo

Upvotes: 1

Related Questions