I have data showing employee, cost code and WeekEnd (Sunday). WeekEnd is the last day of working week, from Monday thru Sunday:

EmpID   CostCode   WeekEnd
=============================
    1   1          01/02/2022  
    1   1          01/09/2022
    Employee skipped working in week of 1/16/2022 ...
    1   1          01/23/2022
    1   1          01/30/2022
    1   2          02/06/2022 
    1   3          02/13/2022
    1   3          02/20/2022

Need to get result like this:

EmpID   CostCode   FirstWeekEnd    LastWeekEnd
==============================================
    1   1          01/02/2022      01/09/2022
    1   1          01/23/2022      01/30/2022
    1   2          02/06/2022      02/06/2022
    1   3          02/13/2022      02/20/2022

I need to get all periods (start - end) when employee worked per cost code?

CodePudding user response：

It can be solved by calculating a ranking for sequencial weeks.

Then aggregate also on that ranking.

SELECT EmpId, CostCode
, MIN(WeekEnd) AS FirstWeekEnd
, MAX(WeekEnd) AS LastWeekEnd
FROM
(
  SELECT *
  , SUM(Flag) OVER (PARTITION BY EmpID ORDER BY WeekEnd) AS Rnk
  FROM
  (
    SELECT *
    , IIF(7>=DATEDIFF(day, LAG(WeekEnd) OVER (PARTITION BY EmpID ORDER BY WeekEnd), WeekEnd),0,1) AS Flag
    FROM your_table
  ) q1
) q2
GROUP BY EmpId, CostCode, Rnk
ORDER BY EmpId, FirstWeekEnd;

Test on db<>fiddle here