Query to report on claim frequency per year and quarter

Question

Post reply

Query to report on claim frequency per year and quarter

jamie_collins

Ten Centuries

Points: 1192
More actions
October 17, 2019 at 6:56 pm

#3689888

I have claims data in a database that I would like to report frequency for based on the life of the warranty.
4 year warranty has 16 quarters in it (Y1Q1, Y1Q2, Y1Q3, Y1Q4, Y2Q1, Y2Q2 and so on)
So if a warranty was purchased July 15th 2018 Y1Q1 would be July 15 2018 - Oct 14 2018 and Y1Q2 would be Oct 15 2018 - Jan 14 2019 and so on.
If claims were made against the warranty on:
Aug 5 2018
Oct 2 2018
May 3 2019
Sep 24 2019
I Need to show claims made during lifetime of the warranty...
Warranty started on July 15 2018
Given the claims data, that has them being made in
Aug 5 2018 - Y1Q1
Oct 2 2018 - Y1Q1
May 3 2019 - Y1Q4
Sep 24 2019 - Y2Q1
My report would then have output like:
Warranty: 111111
Y1Q1Claims: 2
Y1Q2Claims: 0
Y1Q3Claims: 0
Y1Q4Claims: 1
Y2Q1Claims: 1
Y2Q2Claims: 0
Y2Q3Claims: 0
Y2Q4Claims: 0
My goal is to show frequency of claims for the lifetime of a warranty.
What would be the best approach?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

I think the following is the best approach:

Use a tally table add a specific number of quarters to the purchase date.
- Use range 0-17 so that you include both end points.
Use LEAD() to calculate the end date of the quarter.
- You want to use half-open intervals where the starting date is included in the interval, but the ending date is not.
LEFT JOIN these results to the claim table.
- The conditions should include that the claim date is greater than or equal to the start of the quarter (end point included) and less than the end of the quarter (end point excluded).
Group base on claim identifier and quarter start date.

If you want a tested script, please provide sample data for both the warranties and the claims tables and expected results.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

First - you have to calculate the quarter start and end dates and name for all relevant quarters for that warranty start date. Once you have that you left join in the claims data to categorize each claim into one of the defined quarters.

Declare @warrantyStart date = '20180715';

Declare @claimsData Table (ClaimDate date);
 Insert Into @claimsData (ClaimDate)
 Values ('20180805'), ('20181002'), ('20190503'), ('20190924');

 Select *
   From @claimsData cd;

   With quarterDates
     As (
 Select StartDate = dateadd(month, t.n, @warrantyStart)
      , EndDate = dateadd(day, -1, dateadd(month, t.n + 3, @warrantyStart))
      , QuarterName = concat('Y', t.n / 12 + 1, 'Q', t.n / 3 % 4 + 1) 
   From (Values (0),(3),(6),(9),(12),(15)) As t(n)
        )
 Select qd.QuarterName
      , Claims = count(cd.ClaimDate)
   From quarterDates            qd 
   Left Join @claimsData        cd On cd.ClaimDate Between qd.StartDate And qd.EndDate
  Group By
        qd.QuarterName;

This is an example for a single given date...it can be incorporated into your final query, something like this:

Select wq.QuarterName
     , Claims = count(cd.ClaimDate)
  From WarrantStartDates  w
 Cross Apply (Select StartDate = dateadd(month, t.n, w.WarrantyStartDate)
                   , EndDate = dateadd(day, -1, dateadd(month, t.n + 3, w.WarrantyStartDate))
                   , QuarterName = concat('Y', t.n / 12 + 1, 'Q', t.n / 3 % 4 + 1) 
                From (Values (0),(3),(6),(9),(12),(15)) As t(n)
             )           wq
  Left Join ClaimsData   cd On cd.ClaimsDate Between wq.StartDate And wq.EndDate
 Group By
       wq.QuarterName;

This reply was modified 5 years, 1 month ago by Jeffrey Williams.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jamie_collins Ten Centuries Points: 1192 More actions · Answer 3

Here's some sample data.

DECLARE @Warranty TABLE (WarrantyId int, EffectiveDate Date)
DECLARE @Claims TABLE (WarrantyId int, ClaimDate Date)

INSERT INTO @Warranty VALUES (1,'2016-11-21'),(2,'2017-01-04'),(3,'2017-05-13'),(4,'2018-10-28')
INSERT INTO @Claims VALUES (1,'2016-12-08'),(1,'2017-9-24'),(1,'2018-03-06'),(2,'2018-06-03'),(2,'2018-06-21'),(2,'2018-07-11'),(2,'2019-02-16'),(2,'2019-10-12'),(4,'2018-11-02'),(4,'2019-5-30')

I'd like to show output such as:

WarrantyId Y1Q1Claims Y1Q2Claims Y1Q3Claims

1 1 0 0

and so on...

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

You just have to use what I provided and build a cross-tab:

Declare @Warranty Table (WarrantyId int, EffectiveDate Date);
Declare @Claims Table (WarrantyId int, ClaimDate Date);

 Insert Into @Warranty Values (1, '2016-11-21'), (2, '2017-01-04'), (3, '2017-05-13'), (4, '2018-10-28');

 Insert Into @Claims Values (1, '2016-12-08'), (1, '2017-09-24'), (1, '2018-03-06')
                          , (2, '2018-06-03'), (2, '2018-06-21'), (2, '2018-07-11'), (2, '2019-02-16'), (2, '2019-10-12')
                          , (4, '2018-11-02'), (4, '2019-05-30');

 Select w.WarrantyId
      , WarrantyStartDate = min(wq.StartDate)
      , WarrantyEndDate = max(wq.EndDate)
      , Y1Q1Claims = sum(Case When wq.QuarterName = 'Y1Q1' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y1Q2Claims = sum(Case When wq.QuarterName = 'Y1Q2' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y1Q3Claims = sum(Case When wq.QuarterName = 'Y1Q3' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y1Q4Claims = sum(Case When wq.QuarterName = 'Y1Q4' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y2Q1Claims = sum(Case When wq.QuarterName = 'Y2Q1' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y2Q2Claims = sum(Case When wq.QuarterName = 'Y2Q2' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , TotalClaims = (Select count(tc.ClaimDate) From @Claims tc Where tc.WarrantyId = w.WarrantyID)
   From @Warranty                w
  Cross Apply (Select StartDate = dateadd(month, t.n, w.EffectiveDate)
                    , EndDate = dateadd(day, -1, dateadd(month, t.n + 3, w.EffectiveDate))
                    , QuarterName = concat('Y', t.n / 12 + 1, 'Q', t.n / 3 % 4 + 1)
                 From (Values (0), (3), (6), (9), (12), (15)) As t(n)
              )                 wq
   Left Join @Claims            cd On cd.WarrantyId = w.WarrantyId
                                  And cd.ClaimDate Between wq.StartDate And wq.EndDate
  Group By
        w.WarrantyId;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jamie_collins Ten Centuries Points: 1192 More actions · Answer 5

That's great...

How about if there is an explicit expiry date set?

So an example would be for Warranty1 effective date is 2016-11-21 and expirydate is 2021-11-21.

So it would need 5 years (4 quarters each) anything into the future would be 0 but I would still be showing that in the output...

This would be required as there are multiple warranties that have already expired but need the calculation for the lifetime of that warranty...

Thanks.

jamie_collins Ten Centuries Points: 1192 More actions · Answer 6

I was able to get it by adding the following to the CASE:

And cd.ClaimsDateClosed between wq.StartDate and dateadd(q,1,wq.StartDate)

Also the longest warranties we offer is 7 years so I added the appropriate values to my array in the cross apply and modified the StartDate and EndDate accordingly:

StartDate = dateadd(month, t.n, w.EffectiveDate)
                    , EndDate = w.ExpiryDate

Thanks for all your help.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

Not sure those changes will be enough...you need to expand the following:

From (Values (0), (3), (6), (9), (12), (15)) As t(n)

This needs to cover the full length of the longest warranty period...you can either build out the list of values or generate the list of values.

Declare @Warranty Table (WarrantyId int, EffectiveDate Date, ExpiryDate date);
Declare @Claims Table (WarrantyId int, ClaimDate Date);

 Insert Into @Warranty Values (1, '2016-11-21', '2021-11-21'), (2, '2017-01-04', '2020-01-04')
                            , (3, '2017-05-13', '2022-05-13'), (4, '2018-10-28', '2021-10-28');

 Insert Into @Claims Values (1, '2016-12-08'), (1, '2017-09-24'), (1, '2018-03-06')
                          , (2, '2018-06-03'), (2, '2018-06-21'), (2, '2018-07-11'), (2, '2019-02-16'), (2, '2019-10-12')
                          , (4, '2018-11-02'), (4, '2019-05-30');

   With t(n)
     As (
 Select t.n 
   From (
 Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
        )
 Select w.WarrantyId
      , WarrantyStartDate = min(wq.StartDate)
      , WarrantyEndDate = max(w.ExpiryDate) -- max(wq.EndDate)
      , Y1Q1Claims = sum(Case When wq.QuarterName = 'Y1Q1' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y1Q2Claims = sum(Case When wq.QuarterName = 'Y1Q2' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y1Q3Claims = sum(Case When wq.QuarterName = 'Y1Q3' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y1Q4Claims = sum(Case When wq.QuarterName = 'Y1Q4' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y2Q1Claims = sum(Case When wq.QuarterName = 'Y2Q1' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y2Q2Claims = sum(Case When wq.QuarterName = 'Y2Q2' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y2Q3Claims = sum(Case When wq.QuarterName = 'Y2Q3' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y2Q4Claims = sum(Case When wq.QuarterName = 'Y2Q4' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y3Q1Claims = sum(Case When wq.QuarterName = 'Y3Q1' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y3Q2Claims = sum(Case When wq.QuarterName = 'Y3Q2' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y3Q3Claims = sum(Case When wq.QuarterName = 'Y3Q3' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , Y3Q4Claims = sum(Case When wq.QuarterName = 'Y3Q4' And cd.ClaimDate Is Not Null Then 1 Else 0 End)
      , TotalClaims = (Select count(tc.ClaimDate) From @Claims tc Where tc.WarrantyId = w.WarrantyID)
   From @Warranty                w
  Cross Apply (Select StartDate = dateadd(month, t.n, w.EffectiveDate)
                    , EndDate = dateadd(day, -1, dateadd(month, t.n + 3, w.EffectiveDate))
                    , QuarterName = concat('Y', t.n / 12 + 1, 'Q', t.n / 3 % 4 + 1)
                 From (Select Top (datediff(quarter, w.EffectiveDate, w.ExpiryDate) + 1) 
                              (row_number() over(Order By @@spid) - 1) * 3 As rn
                         From t t1, t t2
                      ) As t(n)
              )                 wq
   Left Join @Claims            cd On cd.WarrantyId = w.WarrantyId
                                  And cd.ClaimDate Between wq.StartDate And wq.EndDate
  Group By
        w.WarrantyId;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs