Determining Enrollment Spans - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Determining Enrollment Spans

Author

Topic

laxthis22
Starting Member

1 Post

Posted - 2012-07-06 : 08:28:18

Morning Everyone,
I have been trying to brainstorm so coding for a while now and have had no luck. I need to know if a person has been signed up for a continous amount of time based on a coverage table. For example say I wanted to pull people who were signed up with the company for all of 2011. The tricky part is people can have multiple sign up dates and I would want to exclude anyone who has a certain amount of gap coverage say 45 days. I have finished the coding that determines what the gap is based on the next enrollment line. I am at a loss trying to figure out a way to do this. It seems like it should be simple but it doesn't appear to be. Thanks for any help

Person ID StartDate EndDate Gap
8216548 2011-12-12 NULL NULL
8216548 2010-12-18 2011-11-30 12
8216548 2010-09-01 2010-11-30 18
8949999 2012-06-01 NULL NULL
8949999 2007-01-13 2012-05-31 1
9685324 2001-01-01 2012-05-31 Null

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-06 : 09:26:04

If you have a calendar table that makes it much easier to do this. If you don't have one, you can create it like shown below. Change 20010101 to whatever start date you want to use.

CREATE TABLE #Calendar(Dt date NOT NULL PRIMARY KEY CLUSTERED, RN INT);
;WITH cte AS
(
	SELECT CAST('20000101' AS DATE) AS Dt, 1 AS RN
	UNION ALL
	SELECT DATEADD(dd,1,dt), RN+1 FROM cte WHERE dt < GETDATE()
)
INSERT INTO #Calendar SELECT dt,RN FROM cte OPTION (MAXRECURSION 0);

Once you have the calendar table, then your query against that table - but, first I want to create the test data that you posted like this:

CREATE TABLE #tmp([Person Id] INT, StartDate date, EndDate date);
INSERT INTO #tmp VALUES
('8216548','2011-12-12',NULL),('8216548','2010-12-18','2011-11-30'),
('8216548','2010-09-01','2010-11-30'),('8949999','2012-06-01',NULL),
('8949999','2007-01-13','2012-05-31'),('9685324','2001-01-01','2012-05-31')

Now the query would be something like this:

;WITH cte AS
(
	SELECT
		t.[Person Id],
		c.Dt,
		RN-ROW_NUMBER() OVER (PARTITION BY [Person Id] ORDER BY Dt) AS Grp
	FROM
		#Calendar c
		CROSS JOIN (SELECT DISTINCT [Person Id] FROM #tmp ) t
	WHERE
		NOT EXISTS (SELECT * FROM #tmp t2 WHERE t2.[Person Id] = t.[Person Id] AND 
		t2.StartDate <= c.Dt AND COALESCE(t2.EndDate,GETDATE()) > c.Dt)
)
SELECT
	[Person Id],
	Grp,
	COUNT(*) GapDays,
	MIN(Dt) GapStart,
	MAX(Dt) GapEnd
FROM
	cte
GROUP BY
	[Person Id],
	Grp
ORDER BY
	1,2

Ignore that Grp column in the output - that is just for grouping gaps together.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 09:26:52

you 45 days on a stretch or 45 days total in a year?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources