Date breaks for insurance coverage

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date breaks for insurance coverage

Author

Topic

Floyd Bird
Starting Member

2 Posts

Posted - 2009-01-17 : 13:36:48

I have a query that I want to use to find breaks in coverage for a medicare participant.

This query uses only one table, does a self-join, and uses a sub query. The way it is written now,

it will show full-year periods that HAVE coverage. What I want is to show the number of days

where there is no coverage. The only one in the result set should be from 1/1/2004 to 3/1/2004.

Here is the SQL:

CREATE TABLE Medicare

(Client varchar(30)

, StartDate datetime

, EndDate datetime

)

INSERT INTO Medicare VALUES ("Murphy","2001-01-01", "2001-12-31")

INSERT INTO Medicare VALUES ("Murphy","2002-01-01", "2002-12-31")

INSERT INTO Medicare VALUES ("Murphy","2003-01-01", "2003-12-31")

INSERT INTO Medicare VALUES ("Murphy","2004-03-01", "2004-12-31")

INSERT INTO Medicare VALUES ("Murphy","2005-01-01", "2005-12-31")

INSERT INTO Medicare VALUES ("Murphy","2006-01-01", "2006-12-31")

SELECT DISTINCT m1.Client

, m1.EndDate As m1END

, m2.StartDate AS m2START

, DATEDIFF("d",m1.EndDate,m2.Startdate) AS LapseDays

FROM Medicare AS m1

INNER JOIN Medicare m2 ON (m1.Client = m2.Client)

WHERE

(

DATEADD("d",1,m1.EndDate) =

(SELECT MAX(m3.StartDate)

FROM Medicare m3

WHERE (m3.Client = m1.Client) AND

DATEADD("d",1,m3.StartDate) <= DATEADD("d",-1,m2.EndDate))

)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 13:56:14

[code]
SELECT DATEADD(dd,1,tmp.EndDate),t.StartDate
FROM Table t
CROSS APPLY(SELECT TOP 1 EndDate
FROM Table
WHERE Client=t.Client
AND EndDate<t.StartDate
ORDER BY EndDate DESC) tmp
WHERE EndDate<> DATEADD(dd,-1,StartDate)
[/code]

Floyd Bird
Starting Member

2 Posts

Posted - 2009-01-17 : 14:06:36

Thank you! :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 15:49:14

[code]DECLARE @Medicare TABLE
(
Client varchar(30),
StartDate datetime,
EndDate datetime
)

INSERT @Medicare
SELECT 'Visakh', '2003-01-01', '2003-09-30' UNION ALL
SELECT 'Visakh', '2003-10-15', '2004-12-31' UNION ALL
SELECT 'Visakh', '2004-12-21', '2006-12-31' UNION ALL
SELECT 'Peso', '2003-01-01', '2003-09-30' UNION ALL
SELECT 'Peso', '2003-09-15', '2006-12-31' UNION ALL
SELECT 'Murphy', '2001-01-01', '2001-12-31' UNION ALL
SELECT 'Murphy', '2002-01-01', '2002-12-31' UNION ALL
SELECT 'Murphy', '2003-01-01', '2003-12-31' UNION ALL
SELECT 'Murphy', '2004-03-01', '2004-12-31' UNION ALL
SELECT 'Murphy', '2005-01-01', '2005-12-31' UNION ALL
SELECT 'Murphy', '2006-01-01', '2006-12-31'

-- Peso
SELECT m.Client,
1 + DATEDIFF(DAY, MIN(m.StartDate), MAX(m.EndDate)) AS totDays,
COUNT(DISTINCT DATEADD(DAY, v.Number, m.StartDate)) AS uniqueCovered,
COUNT(v.Number) AS totCovered
FROM @Medicare AS m
INNER JOIN master..spt_values AS v ON v.Type = 'P'
AND v.Number <= DATEDIFF(DAY, m.StartDate, m.EndDate)
GROUP BY m.Client

-- Visakh16
SELECT t.Client,
DATEADD(DAY, 1, tmp.EndDate),
t.StartDate
FROM @Medicare AS t
CROSS APPLY (
SELECT TOP 1 x.EndDate
FROM @Medicare AS x
WHERE x.Client = t.Client
AND x.EndDate < t.StartDate
ORDER BY x.EndDate DESC
) AS tmp
WHERE tmp.EndDate <> DATEADD(DAY, -1, t.StartDate)[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 15:54:27

Also see http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources