April 27, 2011 at 12:39 pm
I have a requirement of searching for enrollment spans with our company, where the member is enrolled for 11 out of 12 months. The missing month can be in any position except for December (finding Dec enrollment is easy), and 'enrollment' for that month means that even one day out of the month constitutes enrollment for the full month.
I have the below functions to help me find whether someone is enrolled for the month, which I have conveniently hacked up and re-written so you can just execute and see pseudo-results. This works great for one member, but scales like cold molasses. Is there another method than the one I'm using, that will allow me to evaluate this type of requirement?
Here's how I would LIKE to use it to identify the people to who DO NOT meet the requirement (this won't work for you, but so you can get the gist):
SELECT carriermemid
FROM #enrollment
WHERE
-- active sometime in 2010
#enrollment.effdate<'1/1/2011' AND #enrollment.termdate>'12/31/2009'
-- active in Dec 2010
AND EXISTS(SELECT * FROM #enrollment AS e2
WHERE e2.carriermemid=#enrollment.carriermemid
AND e2.termdate>'11/30/2010'
AND e2.effdate<'1/1/2011'
)
-- less than 11 months of enrollment
AND dbo.fEnrolledSpan(#enrollment.carriermemid,'1/1/2010','12/31/2010')<11
Sample data:
IF OBJECT_ID('tempdb..#enrollment') IS NOT NULL BEGIN DROP TABLE #enrollment END
CREATE TABLE #enrollment (carriermemid VARCHAR(15),effdate DATETIME,termdate DATETIME,desiredOutcome VARCHAR(25))
INSERT INTO #enrollment
SELECT 'mem1','12/1/2009','5/1/2010','fail' UNION ALL
SELECT 'mem2','1/1/2009','12/31/2078','pass' UNION ALL
SELECT 'mem3','1/1/2010','11/1/2010','fail' UNION ALL
SELECT 'mem4','2/1/2010','1/1/2011','pass' UNION ALL
SELECT 'mem5','3/1/2010','1/1/2011','fail'
SELECT *
FROM #enrollment AS e
--CREATE FUNCTION [dbo].[fEnrolled](@carriermemid varchar(15),@firstOfMonth DATETIME)
--RETURNS INT
--AS
--BEGIN
--=====================================================
-- TESTING ONLY
DECLARE @carriermemid VARCHAR(15),@firstOfMonth DATETIME
SET @firstOfMonth='12/1/2010'
SET @carriermemid='mem5'
--SET @carriermemid='mem2'
--=====================================================
--DECLARE @returnValue int
SELECT
--@returnValue=
CASE
WHEN EXISTS(
SELECT * FROM #enrollment
WHERE #enrollment.carriermemid=@carriermemid
--effdate less than or equal to last day of the month, termdate greater than or equal to first of the month
AND #enrollment.effdate <=DATEADD(d,-1,DATEADD(mm,DATEDIFF(mm,'1/1/1900',@firstOfMonth)+1,'1/1/1900'))
AND #enrollment.termdate >=@firstOfMonth
)
THEN 1
ELSE 0
END AS enrolledInDecember
--RETURN @returnValue
--END
--CREATE FUNCTION [dbo].[fEnrolledSpan](@carriermemid VARCHAR(15),@startdate DATETIME,@enddate DATETIME)
--RETURNS INT
--AS
--BEGIN
--====================================================================
-- TESTING ONLY
DECLARE
--@carriermemid VARCHAR(15),
@startdate DATETIME,@enddate DATETIME
--SET @carriermemid='mem1'
SET @startdate='1/1/2010'
SET @enddate = '12/31/2010'
--====================================================================
--DECLARE @returnValue INT
SELECT
--@returnValue =
DATEADD(mm,N-1,@startdate) AS monthNum,--used for sample data only
--SUM( -- normally I would sum the fEnrolled function as I fed in the months, can't SUM() the aggregate here in my sample data
CASE
WHEN EXISTS(
SELECT * FROM #enrollment
WHERE #enrollment.carriermemid=@carriermemid
--effdate less than or equal to last day of the month, termdate greater than or equal to first of the month
AND #enrollment.effdate <=DATEADD(d,-1,DATEADD(mm,DATEDIFF(mm,'1/1/1900',DATEADD(mm,N-1,@startdate))+1,'1/1/1900'))
AND #enrollment.termdate >=DATEADD(mm,N-1,@startdate)
)
THEN 1
ELSE 0
END
--)
AS monthsEnrolled
FROM dbo.Tally AS t
WHERE t.N<=DATEDIFF(mm,@startdate,@enddate)+1
--RETURN @returnValue
--END
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 27, 2011 at 1:27 pm
Are you looking for something like this?
SELECT
carriermemid,
CASE WHEN
DATEDIFF(
mm,
CASE WHEN effdate<@startdate THEN @startdate ELSE effdate END,
CASE WHEN termdate<@enddate THEN termdate ELSE @enddate END
)
>10 THEN 'pass' ELSE 'fail' END as desiredOutcome
FROM #enrollment
As a side note: I decided to use @enddate = '1/1/2011' instead of '12/31/2010' since this makes the query a little easier (I could have added +1 after the DATEDIFF calculation but I usually try to avoid adding such "hardcoded adjustment values" if possible (even though it's not a big deal in this case).
April 27, 2011 at 1:45 pm
Dammit! Yes, I think I am, although that's not quite perfect. Here's some better info from me to build the #enrollment table, with a member who has two separate spans but still qualifies, and an adjusted CASE to yours to take the December dates into account. I'm falling back on 12/31/2010, because that is how the folks here will think about it, so I added the +1, not (intentionally) trying to be a snot 😀
INSERT INTO #enrollment
SELECT 'mem1','12/1/2009','5/1/2010','fail' UNION ALL
SELECT 'mem2','1/1/2009','12/31/2078','pass' UNION ALL
SELECT 'mem3','1/1/2010','11/1/2010','fail' UNION ALL
SELECT 'mem4','2/1/2010','1/1/2011','pass' UNION ALL
SELECT 'mem5','3/1/2010','1/1/2011','fail' UNION ALL
SELECT 'mem6','1/1/2009','3/1/2010','pass' UNION ALL
SELECT 'mem6','5/1/2010','2/1/2011','pass'
SELECT
carriermemid,
CASE
WHEN NOT(effdate<'1/1/2011' AND termdate>'11/30/2010') THEN 'fail'
WHEN
DATEDIFF(
mm,
CASE WHEN effdate<@startdate THEN @startdate ELSE effdate END,
CASE WHEN termdate<@enddate THEN termdate ELSE @enddate END
)+1
>10 THEN 'pass' ELSE 'fail' END as desiredOutcome
FROM #enrollment
mem6 should pass, because we need to count the months in both spans. I'll see if I can beat your lightning reflexes to a solution...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 27, 2011 at 1:52 pm
Excellent!
SELECT
carriermemid,
CASE
WHEN NOT EXISTS(SELECT * FROM #enrollment AS e2 WHERE e2.carriermemid=#enrollment.carriermemid AND e2.effdate<'1/1/2011' AND e2.termdate>'11/30/2010') THEN 'fail'
WHEN
SUM( DATEDIFF(
mm,
CASE WHEN effdate<@startdate THEN @startdate ELSE effdate END,
CASE WHEN termdate<@enddate THEN termdate ELSE @enddate END
)+1)
>10 THEN 'pass'
ELSE 'fail'
END as desiredOutcome
FROM #enrollment
GROUP BY #enrollment.carriermemid
Lutz, thanks so much!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 27, 2011 at 2:24 pm
Glad I could help 😀
I'm not sure if the addtl. subquery to the sourcxe table is required.
Couldn't you just use another condition like
WHEN min(effdate)<=@startdate and max(termdate)<=dateadd(mm,-1,@enddate) then 'fail'
Before the SUM() condition?
April 27, 2011 at 2:58 pm
Yep, that works even better! Thanks again!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 27, 2011 at 3:30 pm
April 29, 2011 at 5:38 am
SELECTe.CarrierMemID,
CASE
WHEN COUNT(*) >= 11 THEN 'Pass'
ELSE 'Fail'
END AS Result
FROM(
SELECTDATEADD(MONTH, Number, @StartDate) AS theMonth
FROMmaster..spt_values
WHEREType = 'P'
AND Number BETWEEN 0 AND 11
) AS v
INNER JOIN#Enrollment AS e ON e.EffDate <= v.theMonth
AND e.TermDate >= v.theMonth
GROUP BYe.CarrierMemID
N 56°04'39.16"
E 12°55'05.25"
April 29, 2011 at 6:16 am
Thanks for the alternative, Peso
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply