HI,
I need to do the following:
- Identify persons who have >=6 months of consecutive service
- 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
;