sql - 如果连续缺席 10 天，如何获取 FromDate 和 ToDate？

Question

员工连续缺勤 10 天。

以下是我拥有的数据。

我需要获取FromDate(缺少开始日期)和ToDate(缺少结束日期)

条件是这样的

if the 10 days Continuous where STATUS='A' Included STATUS='R' 

仅供引用:

“A”不存在

“R”作为休息日

“L”表示离开

    DECLARE @SomeTable TABLE ([EmpId] INT,[Date] DATETIME,[STATUS] char(1))

    --YOUR SAMPLE DATA
    INSERT INTO @SomeTable 
        ([EmpId],[Date],[STATUS])
    SELECT 999,'2016-07-11 00:00:00.000','A'
    UNION SELECT 999,'2016-07-10 00:00:00.000','A'
    UNION SELECT 999,'2016-07-09 00:00:00.000','R'
    UNION SELECT 999,'2016-07-08 00:00:00.000','R'
    UNION SELECT 999,'2016-07-07 00:00:00.000','A'
    UNION SELECT 999,'2016-07-06 00:00:00.000','A'
    UNION SELECT 999,'2016-07-05 00:00:00.000','A'
    UNION SELECT 999,'2016-07-04 00:00:00.000','A'
    UNION SELECT 999,'2016-07-03 00:00:00.000','A'
    UNION SELECT 999,'2016-07-02 00:00:00.000','R'
    UNION SELECT 999,'2016-07-01 00:00:00.000','R'
    UNION SELECT 999,'2016-06-30 00:00:00.000','A'
    UNION SELECT 999,'2016-06-29 00:00:00.000','A'
    UNION SELECT 999,'2016-06-28 00:00:00.000','A'
    UNION SELECT 999,'2016-06-27 00:00:00.000','L'
    UNION SELECT 999,'2016-06-26 00:00:00.000','A'
    UNION SELECT 999,'2016-06-25 00:00:00.000','R'

SELECT * FROM @SomeTable

我想得到以下输出:

EmpId   FromDate    ToDate
999 2016-06-28 00:00:00.000 2016-07-11 00:00:00.000

Answer 1

您可以使用以下查询:

SELECT EmpId, 
       MAX(CASE WHEN rn = 1 THEN [Date] END) AS FromDate,
       MAX(CASE WHEN rn = 10 THEN [Date] END) AS ToDate
FROM (
   SELECT EmpId, [Date], STATUS, grp,
          ROW_NUMBER() OVER (PARTITION BY EmpId, STATUS, grp 
                             ORDER BY [Date]) AS rn
   FROM (
      SELECT EmpId, [Date], STATUS,
             ROW_NUMBER() OVER (PARTITION BY EmpId ORDER BY [Date]) -
             ROW_NUMBER() OVER (PARTITION BY EmpId, STATUS 
                                ORDER BY [Date]) AS grp
      FROM @SomeTable
      WHERE STATUS <> 'R') AS t) AS s
GROUP BY EmpId, STATUS, grp
HAVING COUNT(*) >= 10

Demo here

编辑:

如果要求获取任意数量的连续'A'记录，前提是连续记录片的总数等于或大于10 ，查询可以大大简化为:

SELECT EmpId, 
       MIN([Date]) AS FromDate,
       MAX([Date]) AS ToDate
FROM (
   SELECT EmpId, [Date], STATUS,
          ROW_NUMBER() OVER (PARTITION BY EmpId ORDER BY [Date]) -
          ROW_NUMBER() OVER (PARTITION BY EmpId, STATUS ORDER BY [Date]) AS grp
   FROM @SomeTable
   WHERE STATUS <> 'R') AS t
GROUP BY EmpId, STATUS, grp
HAVING COUNT(*) >= 10