SQLTeam.com | Weblogs | Forums

Evaluating rows to identify >=6 months of consecutive service

tsql

#1

HI,

I need to do the following:

  1. Identify persons who have >=6 months of consecutive service
  2. The data spans 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
)
;
--===== Populate the test table with data
INSERT INTO #Test
(Person_ID,DOS_Span)
SELECT '7601446','2014-05-01 00:00:00.000' UNION ALL
SELECT '7601446','2014-08-01 00:00:00.000' UNION ALL
SELECT '7601446','2014-09-01 00:00:00.000' UNION ALL
SELECT '7601446','2014-10-01 00:00:00.000' UNION ALL
SELECT '7601446','2014-11-01 00:00:00.000' UNION ALL
SELECT '7601446','2014-12-01 00:00:00.000' UNION ALL
SELECT '7601446','2015-01-01 00:00:00.000' UNION ALL
SELECT '7601446','2015-02-01 00:00:00.000' UNION ALL
SELECT '7601446','2015-03-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-01-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-02-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-03-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-04-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-05-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-06-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-07-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-08-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-09-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-10-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-11-01 00:00:00.000' UNION ALL
SELECT '7636750','2014-12-01 00:00:00.000' UNION ALL
SELECT '7636750','2015-01-01 00:00:00.000' UNION ALL
SELECT '7636750','2015-02-01 00:00:00.000' UNION ALL
SELECT '7636750','2015-03-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-01-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-02-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-03-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-04-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-06-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-07-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-08-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-09-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-10-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-11-01 00:00:00.000' UNION ALL
SELECT '7661758','2014-12-01 00:00:00.000' UNION ALL
SELECT '7661758','2015-01-01 00:00:00.000' UNION ALL
SELECT '7661758','2015-02-01 00:00:00.000' UNION ALL
SELECT '7661758','2015-03-01 00:00:00.000' UNION ALL
;


#2

Does DOS_Span mean that the person has service for that month? Assuming that is the case,

;WITH cte AS
(
	SELECT 
		DATEDIFF(mm,0,DOS_Span)
		-ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY DOS_Span) GroupId,
	*
    FROM #test
)
SELECT Person_ID, 
	MIN(DOS_Span) AS ConsecutiveStart,
	MAX(DOS_Span) AS ConsecutiveEnd,
	COUNT(*) AS ConsecutiveMonths,
	CASE WHEN YEAR(MIN(DOS_Span)) <> YEAR(MAX(DOS_Span)) THEN
		'SpansMultipleYears'
	ELSE 
		'Does not span multiple years'
	END AS Spans
FROM 
	cte
GROUP BY 
	Person_Id,GroupId
HAVING 
	COUNT(*) >= 6;

#3

I tested this and it works great! Thanks James! :smiley: