Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


finding gaps between termination date and next enrollment date and filter by gap >= 45 days for...


finding gaps between termination date and next enrollment date and filter by gap >= 45 days for year 2013

Author
Message
Tammy-274861
Tammy-274861
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 177
Hi autoexcrement!

Just to make sure I'm on the right path. This will not be a 2 or 3 step solution, right?

You saw my sample data and it is very convoluted.

I have like 20 steps until now including what you gave and still cannot see that I am doing the right thing here. :-(

Getting a bit depressed that I cannot get this to work properly!!

Thank you so much for the explanation though, it was much clearer than what I've come across online.


Tammy
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
Tammy-274861 (7/10/2014)

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


Tammy, I would like to clarify your requirements, because what you've shown is not exactly what I'd have expected from above. You want any person enrolled in 2013 who has never had more than a 45 day gap in their enrollment, ever, crossing over year lines but not looking past the 2013 enrollment year? That's what I understand from your data results.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 777
Tammy, the code that I posted should work by itself for the DDL you provided (below). The rest of the code you provided (20 steps) I unfortunately don't have time to review and I don't know what all the data looks like beneath it. If you are starting with the data below, then my code (I think) will find the records with >=45 day gaps during 2013.

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')




"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Tammy-274861
Tammy-274861
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 177
Hi Craig!

Acually I need to apply the 45 days gap ONLY for the members that have enrollment throughout 2013. e.g.

See the member below, I don't care the gap between the first termination date and the second enrollment date since it is not during 2013, but I do care for the gap between second termination date and third enrollment date and since it is less than 45 days this member would be considered enrolled continuously for 2013.



universalmemberid enrollmentdate terminationdate

1234JANE 1/1/2010 5/30/2012
1234JANE 8/1/2012 2/1/2013
1234JANE 3/1/2013 12/31/2013





Thank you for reading my issue, Craig!

Tammy
Tammy-274861
Tammy-274861
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 177
Hi autoexcrement!

If I use the code you provided to get rid of the ones that have more than 45 days gap from my temp table for 2013. Can I also use the same but in the reverse to find the ones that actually have < 45 days?

Would that be a fair statement?

Thanks a lot!! :-D

Tammy
autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 777
Maybe something like this?

;WITH 
CTE1 AS
(
   SELECT
      RN = ROW_NUMBER() OVER
         (PARTITION BY universalmemberid ORDER BY enrollmentdate),
      *
   FROM #nj_members
   WHERE
      (enrollmentdate >= '1/1/2013' AND enrollmentdate < '2014-1-1')
      OR
      (terminationdate >= '1/1/2013' AND terminationdate < '2014-1-1')
)

SELECT * FROM #nj_members
WHERE universalmemberid NOT IN
(
SELECT
   CTE1.universalmemberid
FROM CTE1
INNER JOIN CTE1 AS CTE2 ON
   CTE1.universalmemberid = CTE2.universalmemberid
   AND CTE1.RN + 1 = CTE2.RN
WHERE
   DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) >= 45
)




"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Tammy-274861
Tammy-274861
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 177
Hi autoexcrement!

I would change this


WHERE
   DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) >= 45





to this



WHERE
   DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) < 45



to get the members with gap less than 45 days for 201, do you agree?


I have to try to get my head around this.....sorry for being a bother!! :-(

Tammy
autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 777
Sorry, I just corrected my last post. Can you try that code? It should work.

If you want to do it the way you are suggesting, it will get a bit more complicated, because you not only need to find people with <45, you also need to find people with NO gap. So you would need to do a LEFT OUTER JOIN instead of INNER JOIN, and add a condition at the end for <45 or NULL.

I think my version is simpler. But let me know if it works for you.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3445 Visits: 33007
for clarification....what would you expect for these :



INSERT INTO [nj_members] VALUES('abc','123',2013-01-01,2013-01-31)
INSERT INTO [nj_members] VALUES('abc','123',2013-02-14,2013-03-31)
INSERT INTO [nj_members] VALUES('abc','123',2013-04-14,2013-04-30)
INSERT INTO [nj_members] VALUES('abc','123',2013-05-14,2013-12-31)
INSERT INTO [nj_members] VALUES('xyz','999',2013-01-01,2013-06-30)
INSERT INTO [nj_members] VALUES('xyz','999',2013-08-01,2013-11-30)



________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Tammy-274861
Tammy-274861
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 177
good morning autoexcrement!

ok, I have tested the latest one you posted:



;WITH
CTE1 AS
(
   SELECT
      RN = ROW_NUMBER() OVER
         (PARTITION BY universalmemberid ORDER BY enrollmentdate),
      *
   FROM #nj_members
   WHERE
      (enrollmentdate >= '1/1/2013' AND enrollmentdate < '2014-1-1')
      OR
      (terminationdate >= '1/1/2013' AND terminationdate < '2014-1-1')
)

SELECT * FROM #nj_members
WHERE universalmemberid NOT IN
(
SELECT
   CTE1.universalmemberid
FROM CTE1
INNER JOIN CTE1 AS CTE2 ON
   CTE1.universalmemberid = CTE2.universalmemberid
   AND CTE1.RN + 1 = CTE2.RN
WHERE
   DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) > 45
)





and is giving me false positives on the live data like the following examples:




universalmemberid enrollmentdate terminationdate
1998JOHNNY   2010-10-01 00:00:00.000   2011-06-30 00:00:00.000
1998JOHNNY   2011-07-01 00:00:00.000   2012-06-30 00:00:00.000
1998JOHNNY   2012-07-01 00:00:00.000   2012-07-31 00:00:00.000
1998JOHNNY   2012-09-01 00:00:00.000   2013-12-31 00:00:00.000
0824RICHARD   2010-11-01 00:00:00.000   2011-02-28 00:00:00.000
0824RICHARD   2012-04-01 00:00:00.000   2013-11-11 00:00:00.000
0824RICHARD   2013-11-12 00:00:00.000   2013-12-31 00:00:00.000



Okay, I modified your query by removing the = sign, because I'm looking for gap higher than 45 days.

The member 1998JOHNNY has no gap bigger than 45 days. The member 0824RICHARD
has correctly a gap bigger than 45 days where termination date is '2011-02-28' and the next enrollment date is '2012-04-01', but since it is outside of the desired year 2013.

Shouldn't that be excluded?

Thanks a lot, autoexcrement! Cool

Tammy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search