sql - 需要在sql中删除连续三天不包括周末和节假日

Question

我有一个要求从表中删除连续三天的行(如果周末在这几天则不包括在内)

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')

输出:

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

我们只能从 datescanned 字段中获取日期，然后在上面的示例中我们应该删除连续 3 个日期从“2016-04-28”到“2016-05-02”的行(2016-04-30 和 31 是weekends 所以我们可以忽略)并删除连续 3 个日期从“2016-05-26”到“2016-05-31”的行(2016-05-29 和 30 是周末所以我们可以忽略)。因此只有结果应显示包含 2016-04-04、2016-04-11、2016-04-18 天的行，这些天之前或之后没有连续 3 天。

Answer 1

这是您想要的确切输出..

我可以看出您的问题中有一个错误，[即。删除连续 3 个日期从“2016-05-26”到“2016-05-31”的行(2016-05-29 和 30th 是周末，所以我们可以忽略)']，那些周末不正确..正确的日期是 2016-05-28和 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