Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

finding gaps between termination date and next enrollment date and filter by gap >= 45 days for year 2013 Expand / Collapse
Author
Message
Posted Thursday, July 10, 2014 5:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:39 AM
Points: 19, Visits: 124
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
Post #1591375
Posted Thursday, July 10, 2014 6:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:15 PM
Points: 5,383, Visits: 7,454
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
Post #1591379
Posted Thursday, July 10, 2014 6:50 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 153, Visits: 586
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
Post #1591397
Posted Thursday, July 10, 2014 7:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:39 AM
Points: 19, Visits: 124
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
Post #1591401
Posted Thursday, July 10, 2014 7:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:39 AM
Points: 19, Visits: 124
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!!

Tammy
Post #1591402
Posted Thursday, July 10, 2014 8:25 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 153, Visits: 586
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
Post #1591406
Posted Thursday, July 10, 2014 8:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:39 AM
Points: 19, Visits: 124
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
Post #1591407
Posted Thursday, July 10, 2014 10:30 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 153, Visits: 586
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
Post #1591426
Posted Friday, July 11, 2014 4:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 1,912, Visits: 19,376
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
Post #1591555
Posted Friday, July 11, 2014 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:39 AM
Points: 19, Visits: 124
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!

Tammy
Post #1591646
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse