Home Forums SQL Server 2005 T-SQL (SS2K5) finding gaps between termination date and next enrollment date and filter by gap >= 45 days for year 2013 RE: finding gaps between termination date and next enrollment date and filter by gap >= 45 days for year 2013

  • 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