July 10, 2014 at 8:25 pm
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
)
July 10, 2014 at 8:29 pm
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
July 10, 2014 at 10:30 pm
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.
July 11, 2014 at 4:59 am
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
July 11, 2014 at 8:01 am
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
July 11, 2014 at 8:14 am
All these examples you're posting now aren't in 2013.
July 11, 2014 at 8:19 am
Hi J Livingston!
The member abc would be continuously enrolled, since adding the gaps, he has a total of 42 days gap, which is less than the 45 days allowed.
The member yxz would be discarded since he has more than the 45 days allowed per year as gap to be continuously enrolled. Notice that I am checking the gap between '12/31/2013' which is not in the data, against the last termination date. This is because the member has to be continuously enrolled from '1/1/2013' until '12/31/2013'.
Thanks J!
Tammy 🙂
July 11, 2014 at 8:44 am
Tammy-274861 (7/11/2014)
Hi J Livingston!The member yxz would be discarded since he has more than the 45 days allowed per year as gap to be continuously enrolled. Notice that I am checking the gap between '12/31/2013' which is not in the data, against the last termination date. This is because the member has to be continuously enrolled from '1/1/2013' until '12/31/2013'.
Tammy 🙂
so if someone enrols on 2nd Jan and terminates on 30th Dec...with no gaps.......they are to be excluded?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 11, 2014 at 9:37 am
If you are wanting to ADD all the gaps together per customer per year, then my code WILL NOT work. I thought you were looking for ANY SINGLE GAP of 45 days or larger.
July 11, 2014 at 10:49 am
Tammy-274861 (7/11/2014)
Hi J Livingston!The member abc would be continuously enrolled, since adding the gaps, he has a total of 42 days gap, which is less than the 45 days allowed.
The member yxz would be discarded since he has more than the 45 days allowed per year as gap to be continuously enrolled. Notice that I am checking the gap between '12/31/2013' which is not in the data, against the last termination date. This is because the member has to be continuously enrolled from '1/1/2013' until '12/31/2013'.
Thanks J!
Tammy 🙂
try this...it excludes MIKE cos he wasn't enrolled on 1/1/2013
me thinks you need to provide a testing script that covers all your possible permutations.....
DECLARE @year_start AS datetime;
DECLARE @year_end AS datetime;
SET @year_start = '2013-01-01';
SET @year_end = DATEADD(year , 1 , @year_start) - 1;
WITH ctemembers as (
SELECT
universalmemberid
, CASE
WHEN enrollmentdate < @year_start
AND terminationdate >= @year_start THEN @year_start
WHEN enrollmentdate > @year_end THEN NULL
ELSE enrollmentdate
END AS new_ED
, CASE
WHEN terminationdate > @year_end THEN @year_end
ELSE terminationdate
END AS new_TD
, ROW_NUMBER() OVER (PARTITION BY universalmemberid ORDER BY enrollmentdate) AS memrn
FROM nj_members
WHERE (terminationdate >= @year_start)
AND (enrollmentdate <= @year_end)
)
, cteselect as (
SELECT universalmemberid
FROM ctemembers
GROUP BY universalmemberid
HAVING (MIN(new_ED) = @year_start) AND (MAX(new_TD) = @year_end)
)
SELECT c1.universalmemberid
FROM ctemembers AS c1
INNER JOIN
cteselect AS cs ON c1.universalmemberid = cs.universalmemberid
LEFT OUTER JOIN
ctemembers AS c2 ON c1.universalmemberid = c2.universalmemberid
AND c1.memrn = c2.memrn + 1
GROUP BY c1.universalmemberid
HAVING (SUM(ISNULL(DATEDIFF(day , c2.new_TD , c1.new_ED) - 1 , 0)) <= 45);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 11, 2014 at 11:58 am
First, to clarify. A person is considered continuously enrolled as long as the total of any gaps in enrollment are less than 45 days in the given calendar year.
Based on this, try this:
/*
Member ids that should be returned from the initial sample data provided:
memberid
9999
22222
55555
66666
77777
*/
create table dbo.Members(
UNIVERSALMEMBERID int,
ENROLLMENTDATE datetime,
TERMINATIONDATE datetime
);
go
insert into dbo.Members
select 7777 ,'12/19/2011','10/31/2012' union all
select 7777 ,'1/1/2014','12/31/2199' union all
select 8888 ,'10/1/2013','12/31/2013' union all
select 8888 ,'1/1/2014','12/31/2199' union all
select 9999 ,'10/1/2012','10/8/2012' union all
select 9999 ,'10/9/2012','12/31/2199' union all
select 10000,'8/1/2013','9/11/2013' union all
select 10000,'9/12/2013','12/31/2013' union all
select 10000,'1/1/2014','12/31/2199' union all
select 11111,'1/1/2014','12/31/2199' union all
select 22222,'2/1/2013','8/31/2013' union all
select 22222,'9/1/2013','12/31/2013' union all
select 22222,'1/1/2014','12/31/2199' union all
select 33333,'1/1/2014','12/31/2199' union all
select 44444,'1/1/2014','12/31/2199' union all
select 55555,'9/1/2011','4/8/2012' union all
select 55555,'4/9/2012','1/31/2013' union all
select 55555,'2/1/2013','5/1/2013' union all
select 55555,'6/2/2013','12/31/2199' union all
select 66666,'10/1/2011','4/30/2012' union all
select 66666,'5/1/2012','1/31/2013' union all
select 66666,'2/1/2013','12/31/2013' union all
select 66666,'1/1/2014','12/31/2199' union all
select 77777,'8/30/2011','2/29/2012' union all
select 77777,'3/1/2012','8/31/2012' union all
select 77777,'9/1/2012','12/31/2199' union all
select 88888,'8/1/2011','5/31/2013' union all
select 88888,'10/1/2013','12/31/2199';
go
declare @WorkingYear int;
set @WorkingYear = 2013;
select
UNIVERSALMEMBERID,
sum(datediff(day,case when ENROLLMENTDATE < dateadd(year,@WorkingYear - 1900,0) then dateadd(year,@WorkingYear - 1900,0) else ENROLLMENTDATE end,case when TERMINATIONDATE >= dateadd(year,@WorkingYear - 1900 + 1,0) then dateadd(year,@WorkingYear - 1900 + 1,-1) else TERMINATIONDATE end) + 1) as NumDays
from
dbo.Members
where
TERMINATIONDATE >= dateadd(year,@WorkingYear - 1900,0) and
ENROLLMENTDATE < dateadd(year,@WorkingYear - 1900 + 1,0)
group by
UNIVERSALMEMBERID
having
365 - sum(datediff(day,case when ENROLLMENTDATE < dateadd(year,@WorkingYear - 1900,0) then dateadd(year,@WorkingYear - 1900,0) else ENROLLMENTDATE end,case when TERMINATIONDATE >= dateadd(year,@WorkingYear - 1900 + 1,0) then dateadd(year,@WorkingYear - 1900 + 1,-1) else TERMINATIONDATE end) + 1) < 45;
go
drop table dbo.Members;
go
July 11, 2014 at 12:23 pm
Lynn Pettis (7/11/2014)
First, to clarify. A person is considered continuously enrolled as long as the total of any gaps in enrollment are less than 45 days in the given calendar year.Based on this, try this:
Lynn....should we be including 22222....wasn't enrolled at 01/01/2013 ???.......really think we need clarification
select 22222,'2/1/2013','8/31/2013' union all
select 22222,'9/1/2013','12/31/2013' union all
select 22222,'1/1/2014','12/31/2199' union all
OP has said previously
This is because the member has to be continuously enrolled from '1/1/2013' until '12/31/2013'.
,,,still pondering
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 11, 2014 at 12:40 pm
J Livingston SQL (7/11/2014)
Lynn Pettis (7/11/2014)
First, to clarify. A person is considered continuously enrolled as long as the total of any gaps in enrollment are less than 45 days in the given calendar year.Based on this, try this:
Lynn....should we be including 22222....wasn't enrolled at 01/01/2013 ???.......really think we need clarification
select 22222,'2/1/2013','8/31/2013' union all
select 22222,'9/1/2013','12/31/2013' union all
select 22222,'1/1/2014','12/31/2199' union all
OP has said previously
This is because the member has to be continuously enrolled from '1/1/2013' until '12/31/2013'.
,,,still pondering
From the original post:
I cannot have more than 1 gap of 45 days or less to count these members as continuously enrolled. To clarify the gap only applies to the measurement year i.e. any time between 1/1/2013 and 12/31/2013.
Memberid 22222 was not enrolled from 2013-01-01 to 2013-01-31, but was enrolled from 2013-02-01 to 2013-08-31 and 2013-09-01 to 2013-12-31. The only gap is January and that is only 31 days, which is below the 45 day threshold.
July 11, 2014 at 12:47 pm
Memberid 22222 was not enrolled from 2013-01-01 to 2013-01-31, but was enrolled from 2013-02-01 to 2013-08-31 and 2013-09-01 to 2013-12-31. The only gap is January and that is only 31 days, which is below the 45 day threshold.
fair enough...follow your logic.....now for OP to confirm I spose.
thanks for feed back
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 11, 2014 at 12:57 pm
Looking back it looks like the OP may have been on the forums when I posted my solution. Will have to wait and see.
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply