Continuous Eligibility - 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 2000 Forums

SQL Server Development (2000)

Continuous Eligibility

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-20 : 09:17:15

Jane writes "I have a table that contains insurance eligibility information for all insurance members. A new eligibility record is created on a monthly basis for each member if the member is eligible for the next month. For example:

Member_no    cov_start     cov_stop
1            8/1/2001      8/31/2001
1            9/1/2001      9/30/2001
2            8/1/2001      8/31/2001
2            10/1/2001     10/31/2001

I need to see one record per member per continuous length of eligibility. Below is the output I would like to see:

Member_no    cov_start     cov_stop
1            8/1/2001      9/30/2001
2            8/1/2001      8/31/2001
2            10/1/2001     10/31/2001

I have over 3 years of eligibility information for members. How can I view the continuous eligibility date ranges for each member?
I am using SQL Server 7 and Windows 2000.

Thanks for any help."

mono
Starting Member

36 Posts

Posted - 2001-11-26 : 08:13:15

Jane,

Here is a solution that solves the problems above. The performance will depend strongly on there being a clustered index on (member_no, cov_start). The conditions were arranged written for such an index, but I'm not claiming they couldn't be bettered.

I tried this will date conditions of the form "ALL a <> b" instead of "not exists ... a = b" which is equivalent but it wouldn't work.


SELECT     TOP 100 PERCENT P1.Member_no, P1.cov_start, P2.cov_stop
FROM         dbo.Cover P1 INNER JOIN
                      dbo.Cover P2 ON P1.cov_start <= P2.cov_start AND P1.Member_no = P2.Member_no
WHERE     (NOT EXISTS
                    (SELECT     1 AS pre1
                      FROM          Cover AS Pre
                      WHERE      (Pre.member_no = P1.member_no) AND (Pre.cov_stop = DateAdd([d], - 1, P1.cov_start)))) AND (NOT EXISTS
                    (SELECT     1 AS post1
                      FROM          Cover AS Post
                      WHERE      (Post.member_no = P1.member_no) AND (Post.cov_start = DateAdd([d], 1, P2.cov_stop)))) AND (NOT EXISTS
                    (SELECT     1 AS inter1
                      FROM          Cover AS Inter
                      WHERE      ((Inter.member_no = P1.member_no) AND (Inter.cov_stop <= P2.cov_stop) AND (Inter.cov_start > P1.cov_start) AND (NOT EXISTS
                             (SELECT     1 AS preint1
                               FROM          Cover AS PreInt
                               WHERE      (PreInt.member_no = P1.member_no) AND (PreInt.cov_stop = DateAdd([d], - 1, Inter.cov_start)))))))
ORDER BY P1.Member_no, P1.cov_start

michael

jane
Starting Member

3 Posts

Posted - 2001-11-26 : 13:21:09

Michael,
Sorry for the delayed response. Yes, you can assume that there are no overlaps between periods for a given member. I have run the solution that you posted and the results look correct!! I really appreciate your help.

Cheers,
jane

Shawn
Starting Member

9 Posts

Posted - 2002-06-28 : 19:33:11

I have a problem related to this sort of in reverse. Given the data in the original post, is it possible to create a statement which identifies the dates for which coverage is not provided.

For instance, I would like a query which returns for an arbitrary period of interest (say 01/01/2001 through 12/31/2001):



Member_no   not_covered_start  not_covered_end
1           1/1/2001           7/31/2001
1           10/1/2001          12/31/2001
2           1/1/2001           7/31/2001
2           9/1/2001           9/30/2001
2           11/1/2001          12/31/2001

The actual problem I have relates to information that is missing and requires further research to fill in the date range holes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources