I have a list of individual subscribers to a health plan. Each subscriber has a range of dates to indicate eligibility with the plan. To help illustrate, here is a mock list of records for eligible subscribers during 2018-Q1, written in T-SQL for MS SQL Server:
-- qryEligibleSubscribers2018-Q1Simple
WITH
S
AS
(
-- MOCK LIST OF SUBSCRIBERS TO HEALTH PLAN
-- WITH ELIGIBILITY TIME FRAMES
SELECT
-- CONTINUOUSLY ELIGIBLE SINCE 2017
'0001' AS ID,
'MOUSE' AS LASTNAME,
'MICKEY' AS FIRSTNAME,
'1/1/2017' AS EFFECTIVE,
NULL AS TERM
UNION ALL SELECT
-- CURRENT SUBSCRIBER SINCE FEB. 2018
'0002' AS ID,
'MOUSE' AS LASTNAME,
'MINNIE' AS FIRSTNAME,
'2/1/2018' AS EFFECTIVE,
NULL AS TERM
UNION ALL SELECT
-- SUBSCRIBED TO PLAN FOR JAN. 2018 ONLY
'0003' AS ID,
'DUCK' AS LASTNAME,
'DONALD' AS FIRSTNAME,
'1/1/2018' AS EFFECTIVE,
'1/31/2018' AS TERM
UNION ALL SELECT
-- SUBSCRIBED TO PLAN STARTING IN 2018-Q2
'0004' AS ID,
'GOOF' AS LASTNAME,
'GOOFY' AS FIRSTNAME,
'4/1/2018' AS EFFECTIVE,
NULL AS TERM
)
-- LIST OF SUBSCRIBERS
-- WHO WERE ELIGIBLE IN 2018-Q1 ONLY
SELECT
S.ID,
S.LASTNAME,
S.FIRSTNAME,
S.EFFECTIVE,
S.TERM
FROM
S
WHERE
-- LIMIT LIST TO 2018-Q1
S.EFFECTIVE <= '3/31/2018'
AND
ISNULL(S.TERM,GETDATE()) >= '1/1/2018'
The results, when run, are these three (out of four) subscribers:
ID LASTNAME FIRSTNAME EFFECTIVE TERM
-----------------------------------------------------
0001 MOUSE MICKEY 1/1/2017
0002 MOUSE MINNIE 2/1/2018
0003 DUCK DONALD 1/1/2018 1/31/2018
-----------------------------------------------------
We can see from the results that Mickey Mouse was eligible all three months for 2018-Q1, Minnie Mouse was eligible for Feb. & March only, Donald Duck was eligible for the first month of the quarter only, and Goofy came on to the plan in 2018-Q2 and so is not included in the result set.
I need to report on the total subscriber population for each month. I can do so with this more complex query, also written in T-SQL for MS-SQL Server:
-- qryEligibleSubscribers2018-Q1Complex
WITH
-- LIST OF MONTHS IN 2018-Q1
R(BOR,EOR)
AS
(
-- FIXED RANGE OF MONTHS IN 2018-Q1
SELECT
DATEADD(M,DATEDIFF(M,0,'1/1/2018'),0) AS BOR,
DATEADD(M,DATEDIFF(M,0,'3/31/2018'),0) AS EOR
-- RECURSIVELY ADD A MONTH
UNION ALL SELECT
DATEADD(M,1,R.BOR) AS BOR,
R.EOR
FROM
R
WHERE
R.BOR < R.EOR
),
S
AS
(
-- MOCK LIST OF SUBSCRIBERS TO HEALTH PLAN
-- WITH ELIGIBILITY TIME FRAMES
SELECT
-- CONTINUOUSLY ELIGIBLE SINCE 2017
'0001' AS ID,
'MOUSE' AS LASTNAME,
'MICKEY' AS FIRSTNAME,
'1/1/2017' AS EFFECTIVE,
NULL AS TERM
UNION ALL SELECT
-- CURRENT SUBSCRIBER SINCE FEB. 2018
'0002' AS ID,
'MOUSE' AS LASTNAME,
'MINNIE' AS FIRSTNAME,
'2/1/2018' AS EFFECTIVE,
NULL AS TERM
UNION ALL SELECT
-- SUBSCRIBED TO PLAN FOR JAN. 2018 ONLY
'0003' AS ID,
'DUCK' AS LASTNAME,
'DONALD' AS FIRSTNAME,
'1/1/2018' AS EFFECTIVE,
'1/31/2018' AS TERM
UNION ALL SELECT
-- SUBSCRIBED TO PLAN STARTING IN 2018-Q2
'0004' AS ID,
'GOOF' AS LASTNAME,
'GOOFY' AS FIRSTNAME,
'4/1/2018' AS EFFECTIVE,
NULL AS TERM
),
X
AS
(
-- EXTENDED LIST OF SUBSCRIBERS
-- WHO WERE ELIGIBLE IN 2018-Q1 ONLY
-- LISTING EACH MONTH OF ELIGIBILITY
SELECT
R.BOR AS MONTH,
FORMAT(R.BOR,'MMMM yyyy') AS LABEL,
S.ID,
S.LASTNAME,
S.FIRSTNAME,
S.EFFECTIVE,
S.TERM
FROM
S
INNER JOIN
R
ON
S.EFFECTIVE < DATEADD(M,1,R.BOR)
AND
ISNULL(S.TERM,GETDATE()) >= R.BOR
)
SELECT
X.LABEL AS MONTH,
COUNT(X.ID) AS TOTAL
FROM
X
GROUP BY
X.LABEL,
X.MONTH
ORDER BY
X.MONTH
The results, when run, show that a total of two subscribers were eligible for each month in Q1:
MONTH TOTAL
-----------------
January 2018 2
February 2018 2
March 2018 2
-----------------
To see the detailed/unaggregated results -- specifically, which subscriber was eligible during which months, we can run this query (also written in T-SQL for MS-SQL Server):
-- qryEligibleSubscribers2018-Q1Detailed
WITH
-- LIST OF MONTHS IN 2018-Q1
R(BOR,EOR)
AS
(
-- FIXED RANGE OF MONTHS IN 2018-Q1
SELECT
DATEADD(M,DATEDIFF(M,0,'1/1/2018'),0) AS BOR,
DATEADD(M,DATEDIFF(M,0,'3/31/2018'),0) AS EOR
-- RECURSIVELY ADD A MONTH
UNION ALL SELECT
DATEADD(M,1,R.BOR) AS BOR,
R.EOR
FROM
R
WHERE
R.BOR < R.EOR
),
S
AS
(
-- MOCK LIST OF SUBSCRIBERS TO HEALTH PLAN
-- WITH ELIGIBILITY TIME FRAMES
SELECT
-- CONTINUOUSLY ELIGIBLE SINCE 2017
'0001' AS ID,
'MOUSE' AS LASTNAME,
'MICKEY' AS FIRSTNAME,
'1/1/2017' AS EFFECTIVE,
NULL AS TERM
UNION ALL SELECT
-- CURRENT SUBSCRIBER SINCE FEB. 2018
'0002' AS ID,
'MOUSE' AS LASTNAME,
'MINNIE' AS FIRSTNAME,
'2/1/2018' AS EFFECTIVE,
NULL AS TERM
UNION ALL SELECT
-- SUBSCRIBED TO PLAN FOR JAN. 2018 ONLY
'0003' AS ID,
'DUCK' AS LASTNAME,
'DONALD' AS FIRSTNAME,
'1/1/2018' AS EFFECTIVE,
'1/31/2018' AS TERM
UNION ALL SELECT
-- SUBSCRIBED TO PLAN STARTING IN 2018-Q2
'0004' AS ID,
'GOOF' AS LASTNAME,
'GOOFY' AS FIRSTNAME,
'4/1/2018' AS EFFECTIVE,
NULL AS TERM
)
-- EXTENDED LIST OF SUBSCRIBERS
-- WHO WERE ELIGIBLE IN 2018-Q1 ONLY
-- LISTING EACH MONTH OF ELIGIBILITY
SELECT
FORMAT(R.BOR,'MMMM yyyy') AS LABEL,
S.ID,
S.LASTNAME,
S.FIRSTNAME,
S.EFFECTIVE,
S.TERM
FROM
S
INNER JOIN
R
ON
S.EFFECTIVE < DATEADD(M,1,R.BOR)
AND
ISNULL(S.TERM,GETDATE()) >= R.BOR
ORDER BY
R.BOR,
S.ID
The detailed, month-by-month results are:
MONTH ID LASTNAME FIRSTNAME EFFECTIVE TERM
---------------------------------------------------------------------
January 2018 0001 MOUSE MICKEY 1/1/2017
January 2018 0003 DUCK DONALD 1/1/2018 1/31/2018
February 2018 0001 MOUSE MICKEY 1/1/2017
February 2018 0002 MOUSE MINNIE 2/1/2018
March 2018 0001 MOUSE MICKEY 1/1/2017
March 2018 0002 MOUSE MINNIE 2/1/2018
---------------------------------------------------------------------
With the original, simple query, the result set delivers three records, one for each eligible subscriber over the entire date range of interest (2018-Q1.)
ID LASTNAME FIRSTNAME EFFECTIVE TERM
-----------------------------------------------------
0001 MOUSE MICKEY 1/1/2017
0002 MOUSE MINNIE 2/1/2018
0003 DUCK DONALD 1/1/2018 1/31/2018
-----------------------------------------------------
With the more complex, detailed query, the result set delivers six records, one for each month in which the subscriber was eligible for that specific month in the quarter of interest.
MONTH ID LASTNAME FIRSTNAME EFFECTIVE TERM
---------------------------------------------------------------------
January 2018 0001 MOUSE MICKEY 1/1/2017
January 2018 0003 DUCK DONALD 1/1/2018 1/31/2018
February 2018 0001 MOUSE MICKEY 1/1/2017
February 2018 0002 MOUSE MINNIE 2/1/2018
March 2018 0001 MOUSE MICKEY 1/1/2017
March 2018 0002 MOUSE MINNIE 2/1/2018
---------------------------------------------------------------------
In practice, we have several hundreds of thousands of subscribers. To use the more complex query delivers an unwieldy result set (for example, over a 12-month period, each of the hundreds of thousands of records could be mulitiplied up to 12 times ... one for each month.) I need to keep my simple query as the engine that drives my SSRS report. Using the simple result set ...:
ID LASTNAME FIRSTNAME EFFECTIVE TERM
-----------------------------------------------------
0001 MOUSE MICKEY 1/1/2017
0002 MOUSE MINNIE 2/1/2018
0003 DUCK DONALD 1/1/2018 1/31/2018
-----------------------------------------------------
... I can't seem to figure out how, in SSRS, to build a report that would deliver a month-by-month aggregate count of subscribers. In other words, I want and need SSRS Reporting Services to do the work in multiplying/splitting/duplicating the records into the respective eligible months, and not the underlying SQL query:
MONTH TOTAL
-----------------
January 2018 2
February 2018 2
March 2018 2
-----------------
The essence of the issue is figuring out, if at all possible, how to split up and group over a date range in SSRS, as I am doing in the more complex SQL queries.
I've tried using various flavors of Lookup() in Report Builder, but these require a one-to-one match, for example, MultiLookup() retrieves a set of values from a dataset for name-value pairs where each pair has a 1-to-1 relationship.
In my example, a specific month in the quarter must match a specific month (within a range of months) in the subscriber set. Here, as we can see in the simple query, a subscriber can be eligible over a range of months. I believe I could successfully build an SSRS report in Report Builder if there existed some variation of Lookup() which turned on whether a specific key in one dataset (for example, January 2018) existed within a range of keys in another dataset (August 2016 - May 2018) as opposed to an exact, 1-to-1 match.
The format of the results is immaterial. Could be a chart or tablix. Just looking to get the results, if possible, for now.
