Refer the below query.
DECLARE @table AS TABLE(EmpNo INT,WorkingDate DATETIME)
INSERT INTO @table VALUES(1,'08/01/2015'),(2,'08/02/2015'),(3,'08/03/2015'),(4,'08/05/2015'),(5,'08/07/2015'),(6,'08/08/2015'),
(7,'08/09/2015'),(8,'08/12/2015'),(9,'08/15/2015'),(10,'08/16/2015'),(11,'08/17/2015'),(12,'08/03/2015'),
(13,'08/03/2015'),(14,'08/03/2015'),(15,'08/03/2015'),(16,'08/03/2015')
;WITH CTE
AS(
SELECT * FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY WorkingDate ORDER BY EmpNo) rn,* FROM @table
) as tbl
WHERE rn = 1
)
SELECT DISTINCT a.EmpNo,a.WorkingDate FROM CTE a
,CTE b
WHERE (a.WorkingDate = DATEADD(DAY,1,b.WorkingDate) OR a.WorkingDate = DATEADD(DAY,-1,b.WorkingDate))
ORDER BY a.EmpNo
OutPut
| EmpNo |
WorkingDate |
| 1 |
2015-08-01 00:00:00.000 |
| 2 |
2015-08-02 00:00:00.000 |
| 3 |
2015-08-03 00:00:00.000 |
| 5 |
2015-08-07 00:00:00.000 |
| 6 |
2015-08-08 00:00:00.000 |
| 7 |
2015-08-09 00:00:00.000 |
| 9 |
2015-08-15 00:00:00.000 |
| 10 |
2015-08-16 00:00:00.000 |
| 11 |
2015-08-17 00:00:00.000 |
