Evaluating records to identify >=3 months of consecutive service and exclude first and last month

[eshie003]

Hi all,

I need to evaluate the records to:

1. Find persons who have >=3 months of consecutive data
2. Exclude the first and last month of the consecutive dates
3. The dates span multiple years

Here's some test data:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#test','U') IS NOT NULL
DROP TABLE #test

--Create the test table
CREATE TABLE #test
(
PERSON_ID nvarchar(20),
DOS_SPAN DATETIME,
MONTH INT,
YEAR INT
)

--POPULATE WITH TEST DATA
SELECT '108398123','8','2014','Aug 1 2014 12:00AM' UNION ALL
SELECT '108398123','9','2014','Sep 1 2014 12:00AM' UNION ALL
SELECT '106300298','1','2014','Jan 1 2014 12:00AM' UNION ALL
SELECT '106300298','2','2014','Feb 1 2014 12:00AM' UNION ALL
SELECT '106300298','3','2014','Mar 1 2014 12:00AM' UNION ALL
SELECT '106300298','4','2014','Apr 1 2014 12:00AM' UNION ALL
SELECT '106497664','1','2014','Jan 1 2014 12:00AM' UNION ALL
SELECT '106497664','1','2015','Jan 1 2015 12:00AM' UNION ALL
SELECT '106497664','2','2015','Feb 1 2015 12:00AM' UNION ALL
SELECT '106497664','3','2014','Mar 1 2014 12:00AM' UNION ALL
SELECT '106497664','3','2015','Mar 1 2015 12:00AM' UNION ALL
SELECT '106497664','4','2014','Apr 1 2014 12:00AM' UNION ALL
SELECT '106497664','5','2014','May 1 2014 12:00AM' UNION ALL
SELECT '106497664','6','2014','Jun 1 2014 12:00AM' UNION ALL
SELECT '106497664','7','2014','Jul 1 2014 12:00AM' UNION ALL
SELECT '106497664','8','2014','Aug 1 2014 12:00AM' UNION ALL
SELECT '106497664','9','2014','Sep 1 2014 12:00AM' UNION ALL
SELECT '106497664','10','2014','Oct 1 2014 12:00AM' UNION ALL
SELECT '106497664','11','2014','Nov 1 2014 12:00AM' UNION ALL
SELECT '106497664','12','2014','Dec 1 2014 12:00AM'

I've tried the code below and it works when I am only evaluating the month within a given year, however the data spans 2 years.
WITH CTE AS(
SELECT *,
DOS_MONTH - ROW_NUMBER() OVER(PARTITION BY PERSON_ID ORDER BY DOS_MONTH) grouper,
ROW_NUMBER() OVER(PARTITION BY PERSON_ID ORDER BY DOS_MONTH) rnfirst,
ROW_NUMBER() OVER(PARTITION BY PERSON_ID ORDER BY DOS_MONTH DESC) rnlast
FROM #test
)
SELECT PERSON_ID,
DOS_MONTH
INTO #test_1
FROM CTE
WHERE rnfirst > 1
AND rnlast > 1
AND PERSON_ID IN(SELECT PERSON_ID
FROM CTE
GROUP BY PERSON_ID, grouper
HAVING COUNT(*) >= 3)
ORDER BY PERSON_ID, DOS_MONTH

When I substitute field DOS_MONTH to DOS_SPAN using the above code, I get 0 records.

 

[TamarGranor]

Add the year field to the ORDER BY clauses.

Tamar