Viewing 15 posts - 1 through 15 (of 18 total)
Lynn,
Thank you so much for all your help!
I will mark your last query as the solution to my problem!!! :w00t:
I really appreciate all your time invested in helping me and...
July 14, 2014 at 7:56 am
Good morning, Lynn!!! 😀
I'm happy to say it works like a charm!!!!
Your explanation is also really useful so I can understand what is going on.
I have a final question, what...
July 14, 2014 at 6:57 am
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...
July 12, 2014 at 9:36 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,...
July 12, 2014 at 8:25 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...
July 12, 2014 at 4:34 pm
Everybody has weekends LOL.
Anyhow, enjoy your weekend even if a bit 😎
Tammy
July 11, 2014 at 5:35 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:45 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...
July 11, 2014 at 3:47 pm
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...
July 11, 2014 at 8:19 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 >=...
July 11, 2014 at 8:01 am
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...
July 10, 2014 at 8:29 pm
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...
July 10, 2014 at 7:49 pm
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...
July 10, 2014 at 7:47 pm
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...
July 10, 2014 at 5:45 pm
Hi autoexcrement!
Thank you again for your quick response.
Could this be done in more steps but easier to follow?
I tried reading upon what CTE and (SELECT RN = ROW_NUMBER() OVER (PARTITION...
July 10, 2014 at 3:44 pm
Viewing 15 posts - 1 through 15 (of 18 total)