Hi J!
Here is the script for the create and insert of the table:
CREATE TABLE [dbo].[nj_members](
[universalmemberid] [varchar](100) not NULL,
[memberid] [varchar](52) NULL,
[enrollmentdate] datetime not NULL,
[terminationdate] datetime NULL
)
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate) VALUES ('7777JANE','1234','12/19/2011','10/31/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate) VALUES ('7777JANE','3245','1/1/2014', '12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate) VALUES ('8888MARK','1212','10/1/2013','12/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('8888MARK','1212','1/1/2014','12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('10000JANE','5647','8/1/2013','9/11/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('10000JANE','9870','9/12/2013','12/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('22222MIKE','1215','2/1/2013','8/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('22222MIKE','3098','9/1/2013','12/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','9/1/2011','4/8/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','4/9/2012','1/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','2/1/2013','5/1/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','6/2/2013','12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('66666JANE','9033','10/1/2011','4/30/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('66666JANE','9033','5/1/2012','1/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('66666JANE','9033','2/1/2013','12/31/2013 ')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('77777MARY','0912','3/1/2012','8/31/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('77777MARY','0912','9/1/2012','12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('88888ANTHONY','5467','8/1/2011','5/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('88888ANTHONY','5467','10/1/2013','12/31/2199')
select * from nj_members
and the result is:
universalmemberidmemberidenrollmentdateterminationdate
7777JANE12342011-12-19 00:00:00.0002012-10-31 00:00:00.000
7777JANE32452014-01-01 00:00:00.0002199-12-31 00:00:00.000
8888MARK12122013-10-01 00:00:00.0002013-12-31 00:00:00.000
8888MARK12122014-01-01 00:00:00.0002199-12-31 00:00:00.000
10000JANE56472013-08-01 00:00:00.0002013-09-11 00:00:00.000
10000JANE98702013-09-12 00:00:00.0002013-12-31 00:00:00.000
22222MIKE12152013-02-01 00:00:00.0002013-08-31 00:00:00.000
22222MIKE30982013-09-01 00:00:00.0002013-12-31 00:00:00.000
55555MARTHA56712011-09-01 00:00:00.0002012-04-08 00:00:00.000
55555MARTHA56712012-04-09 00:00:00.0002013-01-31 00:00:00.000
55555MARTHA56712013-02-01 00:00:00.0002013-05-01 00:00:00.000
55555MARTHA56712013-06-02 00:00:00.0002199-12-31 00:00:00.000
66666JANE90332011-10-01 00:00:00.0002012-04-30 00:00:00.000
66666JANE90332012-05-01 00:00:00.0002013-01-31 00:00:00.000
66666JANE90332013-02-01 00:00:00.0002013-12-31 00:00:00.000
77777MARY09122012-03-01 00:00:00.0002012-08-31 00:00:00.000
77777MARY09122012-09-01 00:00:00.0002199-12-31 00:00:00.000
88888ANTHONY54672011-08-01 00:00:00.0002013-05-31 00:00:00.000
88888ANTHONY54672013-10-01 00:00:00.0002199-12-31 00:00:00.000
The members that meet my requirements:
22222MIKE
55555MARTHA
66666JANE
77777MARY
The member 88888ANTHONY doesn't meet the requirements, because even though he is enrolled for 2013, he has a bigger than 45 days gap between 2013-05-31 00:00:00.000 and 2013-10-01 00:00:00.000.
That is the problem I need to solve, how do I check for gaps like that when I can have one record per member or multiple.
Thanks a lot in advanced.
Tammy