Vamsi Vamsi - 3 months ago 9
SQL Question

Need to delete three consecutive days excluding weekends and holidays in sql

I have a requirement to delete rows from a table with three consecutive days (exclude the days if weekends come in between)

CREATE TABLE [dbo].[Test]
(
[Scanid] [bigint] NULL,
[Employeeid] [int] NULL,
[Datescanned] [datetime] NULL
)

INSERT INTO [dbo].[Test]([Scanid], [Employeeid], [Datescanned])
VALUES (108639, 3820, '2016-04-28 17:12:33.000'),
(108639, 3820, '2016-04-28 18:05:46.000'),
(108639, 3820, '2016-04-28 20:58:36.000'),
(999999, 3820, '2016-04-29 10:08:00.000'),
(999999, 3820, '2016-04-29 10:12:10.000'),
(777777, 3820, '2016-05-02 10:12:00.000'),
(111111, 3820, '2016-04-04 10:12:00.000'),
(33333, 3820, '2016-04-11 17:23:00.000'),
(987623, 3820, '2016-04-18 11:12:00.000'),
(1234, 3820, '2016-05-26 10:00:00.000'),
(5678, 3820, '2016-05-27 10:00:00.000'),
(8920, 3820, '2016-05-31 10:00:00.000')


Output:

Scanid Employeeid Datescanned
----------------------------------------
108639 3820 2016-04-28 17:12:33.000
108639 3820 2016-04-28 18:05:46.000
108639 3820 2016-04-28 20:58:36.000
999999 3820 2016-04-29 10:08:00.000
999999 3820 2016-04-29 10:12:10.000
777777 3820 2016-05-02 10:12:00.000
111111 3820 2016-04-04 10:12:00.000
33333 3820 2016-04-11 17:23:00.000
987623 3820 2016-04-18 11:12:00.000
1234 3820 2016-05-26 10:00:00.000
5678 3820 2016-05-27 10:00:00.000
8920 3820 2016-05-31 10:00:00.000


We can take date only from datescanned field and then in the above example we should delete rows with 3 consecutive date from '2016-04-28' to '2016-05-02' (2016-04-30 and 31 are weekends so we can ignore) and also delete rows with 3 consecutive date from '2016-05-26' to '2016-05-31' (2016-05-29 and 30th are weekends so we can ignore). so only results should display row with days 2016-04-04,2016-04-11,2016-04-18 which don't have 3 consecutive days before or after them.

Answer

Here is the exact output that you want..

I could see one mistake in your question,[ie. delete rows with 3 consecutive date from '2016-05-26' to '2016-05-31' (2016-05-29 and 30th are weekends so we can ignore)'],those weekends days are not correct..and the correct dates are 2016-05-28 and 2016-05-29.

DROP TABLE [TestDates]
GO
CREATE TABLE [dbo].[TestDates](
        [Scanid] [bigint] NULL,
        [Employeeid] [int] NULL,
        [Datescanned] [datetime] NULL
        )

    INSERT INTO [dbo].[TestDates]  ([Scanid] ,[Employeeid],[Datescanned])
    VALUES   (108639,3820,'2016-04-28 17:12:33.000'),(108639,3820,'2016-04-28 18:05:46.000'),
             (108639,3820,'2016-04-28 20:58:36.000'),(999999,3820,'2016-04-29 10:08:00.000'),
             (999999,3820,'2016-04-29 10:12:10.000'),(777777,3820,'2016-05-02 10:12:00.000'),
             (111111,3820,'2016-04-04 10:12:00.000'),(33333,3820,'2016-04-11 17:23:00.000'),
             (987623,3820,'2016-04-18 11:12:00.000'),(1234,3820,'2016-05-26 10:00:00.000'),
             (5678,3820,'2016-05-27 10:00:00.00'), (8920, 3820, '2016-05-30 10:00:00.000')
GO

DROP TABLE #t
GO
SELECT DISTINCT Employeeid,CONVERT(date,Datescanned) Datescanned INTO #T
FROM [TestDates]
GO


;WITH cte_cnt
AS
(
 SELECT Employeeid, MIN(Datescanned) AS FROM_DATE
       ,MAX(Datescanned) AS TO_DATE
       , COUNT('A') AS DayDiff
 FROM (
      SELECT Employeeid,Datescanned,
            ROW_NUMBER() OVER(ORDER BY Datescanned) AS ROW_NUMBER,
            DATEDIFF(D, ROW_NUMBER() OVER(ORDER BY Datescanned)
            ,CASE WHEN DATENAME(dw, cast (Datescanned as datetime)-1) = 'Sunday' THEN DATEADD(DAY, -2, Datescanned)  ELSE Datescanned END) AS Diff
        FROM #t) AS dt
 GROUP BY Employeeid, Diff )
DELETE t
--SELECT *
FROM  cte_cnt  c
      JOIN [TestDates] t
            ON c.Employeeid=t.Employeeid
WHERE CAST(t.Datescanned as DATE) BETWEEN c.FROM_DATE AND  c.TO_DATE and c.DayDiff=3
GO

SELECT *
FROM [TestDates]
GO
Comments