Get FromDate and ToDate if they have 10 continues days of absent?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Board and Card Games

Q&A for people who like playing board games, designing board games or modifying the rules of existing board games

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results