July 11, 2014 at 3:47 pm
Hi Lynn, J and autoexcrement!
Lynn is correct, and that is why I had clarified by saying that as long as the sum of the gaps for the measurement year which is 2013 is <= 45 days then the member is considered continuously enrolled.
Lynn, I will try your script now and will let you know how it goes.
Thank you so much for taking the time to answer me. You all rock!!!
Tammy
July 11, 2014 at 4:12 pm
Tammy-274861 (7/11/2014)
Hi Lynn, J and autoexcrement!Lynn is correct, and that is why I had clarified by saying that as long as the sum of the gaps for the measurement year which is 2013 is <= 45 days then the member is considered continuously enrolled.
Lynn, I will try your script now and will let you know how it goes.
Thank you so much for taking the time to answer me. You all rock!!!
Tammy
If a 45 day gap is still valid you may need to adjust my code slightly, change the < 45 to <= 45 in the HAVING clause.
July 11, 2014 at 4:45 pm
Hi Lynn!
I will test it on Monday with the real data and will let you know.
It works with the test data which is great 🙂
Have a nice weekend!
Tammy
July 11, 2014 at 4:49 pm
Tammy-274861 (7/11/2014)
Hi Lynn!I will test it on Monday with the real data and will let you know.
It works with the test data which is great 🙂
Have a nice weekend!
Tammy
I would if I could but I can't because I don't have weekends where I am at. 😉
Thanks.
July 11, 2014 at 5:35 pm
Everybody has weekends LOL.
Anyhow, enjoy your weekend even if a bit 😎
Tammy
July 11, 2014 at 5:49 pm
Tammy-274861 (7/11/2014)
Everybody has weekends LOL.Anyhow, enjoy your weekend even if a bit 😎
Tammy
Nope. Civilian contractor currently deployed in Afghanistan. I work 7 days a week, 12+ hours a day. Came back from 3 weeks on R & R on February 27th and have been working every day since. Don't go home again until December 20th for a fly through 2 week vacation before coming back out here again.
Oh, and I first came out here at the end of June 2013.
But thanks for the sentiment. 🙂
July 12, 2014 at 4:34 pm
awww....so far away. Sorry and take care there!
I am looking at your solution, sorry to bother again, but can you explain what you are doing here:
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
Also, I just ran the query with the prod data that I had in a temp table, the following scenario went through, but it shouldn't since there are 46 days between 2013-10-16
and 2013-12-01:
UniversalMemberID EnrollmentDate TerminationDate
1920DESSA 2011-12-01 2013-02-28
1920DESSA 2013-03-01 2013-10-16
1920DESSA 2013-12-01 2199-12-31
The only thing I changed on your query is this <= 45.
I don't understand why would that one go though?
Thank you so much, Lynn!!!! 😀
July 12, 2014 at 5:12 pm
Tammy-274861 (7/12/2014)
awww....so far away. Sorry and take care there!I am looking at your solution, sorry to bother again, but can you explain what you are doing here:
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
Also, I just ran the query with the prod data that I had in a temp table, the following scenario went through, but it shouldn't since there are 46 days between 2013-10-16
and 2013-12-01:
UniversalMemberID EnrollmentDate TerminationDate
1920DESSA 2011-12-01 2013-02-28
1920DESSA 2013-03-01 2013-10-16
1920DESSA 2013-12-01 2199-12-31
The only thing I changed on your query is this <= 45.
I don't understand why would that one go though?
Thank you so much, Lynn!!!! 😀
I do have a change to make, but it doesn't reflect the question regarding the 46. I have a question, though, regarding the enrollment and termination dates.
Is a client considered enrolled on the enrollment date?
Is a client considered enrolled on the termination date? Policy ends on 10/31/2013, are they still enrolled until midnight of 11/01/2013? Most policies that I know of for any type of insurance usually include the termination date as part of the active policy. My car insurance is for 5/5/2014 - 10/4/2014 for instance. 5/5/2014 is the enrollment date, 10/4/2014 is the termination date. I would be uninsured on 10/5/2014.
Adding to this by looking at just 2 records from you sample data:
select 9999 ,'10/1/2012','10/8/2012' union all
select 9999 ,'10/9/2012','12/31/2199' union all
If the TerminationDate is not counted then there us a 1 day gap between these two periods.
For the individual you showed as having a gap of 46 days you are subtracting the termination date of the previous row from the enrollment date of the next for that individual. The actual gap should be the day following the termination date to the date prior to the next enrollment date. This would only be 45 days in this case (2013-11-30 - 2013-10-17 + 1).
July 12, 2014 at 5:17 pm
First change, I had the number of days in the active year hard coded. Wouldn't work for a leap year:
/*
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' union all
select 1920,'2011-12-01','2013-02-28' union all
select 1920,'2013-03-01','2013-10-16' union all
select 1920,'2013-12-01','2199-12-31';
go
declare @WorkingYear int;
set @WorkingYear = 2013;
select
UNIVERSALMEMBERID,
datediff(day,dateadd(year,@WorkingYear - 1900,0),dateadd(year,@WorkingYear - 1900 + 1,0)) DaysInYear,
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,
datediff(day,dateadd(year,@WorkingYear - 1900,0),dateadd(year,@WorkingYear - 1900 + 1,0)) - 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) DaysLapsed
from
dbo.Members
where
TERMINATIONDATE >= dateadd(year,@WorkingYear - 1900,0) and
ENROLLMENTDATE < dateadd(year,@WorkingYear - 1900 + 1,0)
group by
UNIVERSALMEMBERID
having
datediff(day,dateadd(year,@WorkingYear - 1900,0),dateadd(year,@WorkingYear - 1900 + 1,0)) - 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 12, 2014 at 5:36 pm
Here are some comments regarding the code. Of course, the best way to learn what is happening is to break things down and display the parts in the query.
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
case when ENROLLMENTDATE < dateadd(year,@WorkingYear - 1900,0)
then dateadd(year,@WorkingYear - 1900,0)
else ENROLLMENTDATE
end -- This case statement identifies the start of an enrollment period. If the enrollmentdate is less than the first day of the year
-- we will substitute the first day of the active year for the enrollment date.
case when TERMINATIONDATE >= dateadd(year,@WorkingYear - 1900 + 1,0)
then dateadd(year,@WorkingYear - 1900 + 1,-1)
else TERMINATIONDATE
end -- This case statement identifies the termination date of the enrollment period. If the termination date is equal to or greater than
-- the first day of the next year we substitute the last day of the active year for the termination date.
-- we then add one to this difference to include the termination date. For instance datediff(day,'2013-01-01','2013-01-31) returns 30, but
-- the number of days is 31, we need to add 1.
dateadd(year,@WorkingYear - 1900,0) -- returns the first day of the working year
dateadd(year,@WorkingYear - 1900 + 1,0) -- returns the first day of the following year
dateadd(year,@WorkingYear - 1900 + 1,-1) -- returns the last day of the working year
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 -- As part of the GROUP BY, the HAVING clause is summing all the differences between
-- EnrollmentDate and TerminationDate + 1 for each UNIVERSALMEMBERID. This is then subtracted
-- from the total number of days for the year and we keep those rows where this difference is 45 or less
July 12, 2014 at 8:25 pm
Hi Lynn!
That is a good question. I was assuming that if the enrollment date is for instance 2/1/2013, that's when the enrollment starts and if termination date is on 12/31/2013, that day is still counted as being enrolled, but I'm not sure if it's actually so. I would say yes, so then We have to add 1 day, correct?
I will have to confirm on Monday.
Sorry for not having a definitive answer:(
I will review the explanation of your code, I really appreciate you tsking the time to help me!!!
I hsve found you and autoexcrement to be so helpful!!!
Thank you so much!!! 😀
Tammy
July 12, 2014 at 8:35 pm
Apologies that I misunderstood what you needed. Hopefully Lynn will be able to see you to the finish line. 🙂
July 12, 2014 at 8:45 pm
Tammy-274861 (7/12/2014)
Hi Lynn!That is a good question. I was assuming that if the enrollment date is for instance 2/1/2013, that's when the enrollment starts and if termination date is on 12/31/2013, that day is still counted as being enrolled, but I'm not sure if it's actually so. I would say yes, so then We have to add 1 day, correct?
I will have to confirm on Monday.
Sorry for not having a definitive answer:(
I will review the explanation of your code, I really appreciate you tsking the time to help me!!!
I hsve found you and autoexcrement to be so helpful!!!
Thank you so much!!! 😀
Tammy
Well, I am going with my experience with insurance and say the termination date is the last day of coverage.
Did you also read my explanation regarding the ID you said was 46 and was actually 45 when you look at the dates knowing (or assuming) what are dates of coverage and what aren't.
July 12, 2014 at 9:36 pm
Hi Lynn and autoexcrement!
@autoexcrement, thank you so much for your time in helping me!!! 😀
@Lynn, yes, I saw your explanation, and regarding the 46 days it does make sense what you are saying and that's what I will go by!
So by changing the code to the last one you posted to account for leap year, that should make it work for any working year I use, correct?
Thanks a lot for such quick replies!!!! :w00t:
Tammy
July 13, 2014 at 7:19 am
Tammy-274861 (7/12/2014)
Hi Lynn and autoexcrement!@autoexcrement, thank you so much for your time in helping me!!! 😀
@Lynn, yes, I saw your explanation, and regarding the 46 days it does make sense what you are saying and that's what I will go by!
So by changing the code to the last one you posted to account for leap year, that should make it work for any working year I use, correct?
Thanks a lot for such quick replies!!!! :w00t:
Tammy
Yes, now that I programmatically calculate the number of days in the year it will work for any working year you use.
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply