Determining gaps in dates

Question

Determining gaps in dates

Brad Allison

Hall of Fame

Points: 3550
More actions
March 18, 2016 at 5:38 am

#305745

I don't know why I cannot figure this one out and I don't need anyone to write the procedure for me, but point me in a direction. We have a table with thousands of clients and are trying to determine lapses in coverage over 30 days. So we have the clientID with some information like name, etc. Then we have a startDate and an endDate. I need to return rows where there is 30+ days between the endDate and the beginning of the next startDate, if that makes sense. So if a client stops coverage at the end of a certain month, but then 60 days later starts coverage again I need to select that. So I am trying to do a datediff on that endDate in a prior row and compare it to the next instance of the startDate.
Thanks for any help anyone can give here

Viewing 5 posts - 1 through 4 (of 4 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

I would start with looking up Gaps and Islands. There's Itzik Ben-Gan's code, which is a good starting point. Dig around there. Dwain Camps wrote some. how you do it will depend in part on the version of SQL Server you're using.

Here[/url]'s an article by Jeff Moden... happy reading!

Brad Allison Hall of Fame Points: 3550 More actions · Answer 2

Thanks. It is 2008R2 (as to why I am posting here in SQL Server 2008). I did some searching online and have something actually running now so now I just need to clean it up a bit. What I did is something like this:

WITH rows AS

(

select d.*, ROW_NUMBER() over (Order by d.clientid, d.coverageplanname, d.StartDate) as rn

from

(

select

p.ClientId

,h.StartDate

,h.EndDate

,cp.CoveragePlanName

from ClientCoverageHistory h

join ClientCoveragePlans p on p.ClientCoveragePlanId = h.ClientCoveragePlanId

join CoveragePlans cp on p.CoveragePlanId = cp.CoveragePlanId

group by p.ClientId, cp.CoveragePlanName, h.StartDate, h.EndDate

) as d)

select

d.clientid

,d.CoveragePlanName

,d.StartDate

,d.EndDate

,DATEDIFF(day, d.EndDate , mp.StartDate) as dateLag

from rows d

join rows mp

on d.rn = mp.rn-1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Couple of minor tweaks:

WITH SequencedRows AS (

SELECT

p.ClientId,

h.StartDate,

h.EndDate,

cp.CoveragePlanName,

rn = ROW_NUMBER() OVER (PARTITION BY d.clientid, d.coverageplanname ORDER BY d.StartDate)

FROM ClientCoverageHistory h

INNER JOIN ClientCoveragePlans p

ON p.ClientCoveragePlanId = h.ClientCoveragePlanId

INNER JOIN CoveragePlans cp

ON p.CoveragePlanId = cp.CoveragePlanId

GROUP BY p.ClientId, cp.CoveragePlanName, h.StartDate, h.EndDate

)

SELECT

tr.clientid,

tr.CoveragePlanName,

tr.StartDate,

tr.EndDate,

dateLag = DATEDIFF(day, tr.EndDate, nr.StartDate)

FROM SequencedRows tr -- this row

LEFT JOIN SequencedRows nr -- next row

ON nr.ClientId = tr.ClientId

AND nr.CoveragePlanName = tr.CoveragePlanName

AND nr.rn = tr.rn + 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Brad Allison Hall of Fame Points: 3550 More actions · Answer 4

Thanks for this! This does start me all in the direction I need to go.