April 23, 2018 at 10:12 am
Sorry,
I am trapped in my own SQL here...
The following SQl works fine!. Finds out the different spans that a member has.
So some members will have more than one line ( That is fine )
OK, so what I need is 2 more columns to this sql statement.
It should give the startdate of enrollment and the next should give the end date of enrollment.
If object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t( BeneficiaryID VARCHAR(10), EligYear INT, EligMonth INT );
INSERT INTO #t(BeneficiaryID, EligYear, EligMonth )
Select '12345678',2016,6 UNION
Select '12345678',2016,7 UNION
Select '12345678',2016,8 UNION
Select '12345678',2016,9 UNION
Select '12345678',2016,10 UNION
Select '12345678',2017,2 UNION
Select '12345678',2017,3 UNION
Select '12345678',2017,4 UNION
Select '82345678',2016,6 UNION
Select '82345678',2016,7 UNION
Select '82345678',2016,8 UNION
Select '82345678',2016,9 UNION
Select '82345678',2016,10 UNION
Select '82345678',2016,11 UNION
Select '82345678',2016,12 UNION
Select '82345678',2017,1 UNION
Select '82345678',2017,2 UNION
Select '82345678',2017,3 UNION
Select '82345678',2017,4 UNION
Select '82345678',2017,5 UNION
Select '92345678',2016,12 UNION
Select '92345678',2017,1
;
With START_POINT as
(
Select
BeneficiaryID, MIN(EligYear) MIN_YEAR, MIN(EligMonth) MIN_MONTH
FROM #t
GROUP BY BeneficiaryID
)
,
MONTH_POSITION as
(
Select T.*, B.MIN_YEAR, B.MIN_MONTH,
((T.ELIGYear - B.MIN_YEAR )*(12) + (EligMonth)) as MONTH_SEQ
FROM
#t T
INNER JOIN START_POINT B ON ( B.BeneficiaryID = T.BeneficiaryID )
)
,
T AS
(
SELECT *
,DENSE_RANK() OVER (PARTITION BY BeneficiaryID ORDER BY MONTH_SEQ) - MONTH_SEQ AS Grp2
FROM MONTH_POSITION
)
,
CONSECUTIVE_MONTHS_TOGETHER as
(
SELECT
BeneficiaryID,
MIN(MONTH_SEQ) AS RangeStart,
MAX(MONTH_SEQ) AS RangeEnd,
MAX(MONTH_SEQ) - MIN(MONTH_SEQ) as DIFF
FROM T
GROUP BY BeneficiaryID, Grp2
)
Select *
FROM
CONSECUTIVE_MONTHS_TOGETHER
WHERE DIFF >= 2 -- Continuous Months for 3 or more
ORDER BY 1,2
April 23, 2018 at 12:27 pm
Sounds like a "gaps and islands" type of problem. However, I think you're going to find a problem with your very first CTE. If you take a MIN value for year and month separately, then you are going to run into odd results. Any beneficiaryID value that spans a year end but doesn't actually start in an early month is going to be problematic. I would suggest starting your CTE chain with one that creates date values using the year and month and just uses 1 for the day. At least when you then take the MIN of the date value, it will actually be the correct date value, as both have to be considered together as opposed to separately.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 23, 2018 at 2:01 pm
Here's an idea query that you should be able to adapt: IF OBJECT_ID(N'tempdb..#t') IS NOT NULL
BEGIN
DROP TABLE #t;
END;
GO
CREATE TABLE #t (
BeneficiaryID varchar(10),
EligYear int,
EligMonth int,
EligDate AS (CONVERT(char(8), (EligYear * 10000) + (EligMonth * 100) + 1)) PERSISTED
);
INSERT INTO #t (BeneficiaryID, EligYear, EligMonth)
SELECT '12345678',2016,6 UNION
SELECT '12345678',2016,7 UNION
SELECT '12345678',2016,8 UNION
SELECT '12345678',2016,9 UNION
SELECT '12345678',2016,10 UNION
SELECT '12345678',2017,2 UNION
SELECT '12345678',2017,3 UNION
SELECT '12345678',2017,4 UNION
SELECT '82345678',2016,6 UNION
SELECT '82345678',2016,7 UNION
SELECT '82345678',2016,8 UNION
SELECT '82345678',2016,9 UNION
SELECT '82345678',2016,10 UNION
SELECT '82345678',2016,11 UNION
SELECT '82345678',2016,12 UNION
SELECT '82345678',2017,1 UNION
SELECT '82345678',2017,2 UNION
SELECT '82345678',2017,3 UNION
SELECT '82345678',2017,4 UNION
SELECT '82345678',2017,5 UNION
SELECT '92345678',2016,12 UNION
SELECT '92345678',2017,1;
DECLARE @MinDate AS date = (SELECT MIN(EligDate) FROM #t);
DECLARE @MaxDate AS date = (SELECT MAX(EligDate) FROM #t);
WITH E1 AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
Dates AS (
SELECT TOP (DATEDIFF(month, @MinDate, @MaxDate) + 1)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum,
DATEADD(month, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @MinDate) AS EligDate
FROM E1 AS a, E1 AS b, E1 AS c
),
ALL_DATA AS (
SELECT T.BeneficiaryID, T.EligDate, T.EligYear, T.EligMonth, D.RowNum
FROM #t AS T
INNER JOIN Dates AS D
ON T.EligDate = D.EligDate
),
GROUPED_DATA AS (
SELECT BeneficiaryID,
EligDate,
RowNum - ROW_NUMBER() OVER(PARTITION BY BeneficiaryID ORDER BY RowNum) AS grp
FROM ALL_DATA
)
SELECT BeneficiaryID,
CONVERT(date, MIN(EligDate)) AS RangeStart,
CONVERT(date, MAX(EligDate)) AS RangeEnd
FROM GROUPED_DATA
GROUP BY BeneficiaryID, grp
ORDER BY BeneficiaryID;
DROP TABLE #t;
The elements of it come from Itzik Ben-Gan, and I just adapted it to add the grouping by beneficiary. You can find the base query I adapted in the solution #3 for Islands, at the following link: https://livebook.manning.com/#!/book/sql-server-mvp-deep-dives/chapter-5/1
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply