Find consecutive dates spanning a weekend

Question

I have a job list that indicates the work performed on any particular job. When work is done during the day then just one record is added and a work_type is included. Work is not performed on a weekend. Jobs can have work done over a long period of time with the odd day here and there but at some point in its lifecycle it should have a period of work where it is being worked on consistently. Our management would like to be able to highlight on a report any jobs where this longer period of work hasn't happened. There are some other conditions around type of work and the team name but the main sticking point is the time issue.

So ... how do I find jobs that have not had a period of at least two consecutive weeks (10 working days) consistent work performed ?

In the following, job 164353 will not be included as it has the necessary 10 consecutive days (ignoring weekends), while job 214325 will be flagged as there is a gap on the 9th that broke the sequence of consecutive days.

JOB_ID     W ACTION_DATE
---------- - -----------
164354     H 10-FEB-17
164354     H 13-FEB-17
164354     H 14-FEB-17
164354     H 15-FEB-17
164354     H 16-FEB-17
164354     H 17-FEB-17
164354     H 20-FEB-17
164354     H 21-FEB-17
164354     H 22-FEB-17
164354     H 23-FEB-17
164354     H 24-FEB-17

214325     H 01-MAR-17
214325     H 02-MAR-17
214325     H 03-MAR-17
214325     H 06-MAR-17
214325     H 07-MAR-17
214325     H 08-MAR-17
214325     H 10-MAR-17
214325     H 13-MAR-17
214325     H 14-MAR-17
214325     H 15-MAR-17

I have this query where I can produce consecutive groups with a number of days against each group but I am struggling to adapt it to span over the weekends. In other words the results below would ideally show a number of consecutive days of 10.

WITH  
  groups AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY action_date) AS rn,
      action_date -ROW_NUMBER() OVER (ORDER BY action_date) AS grp,
      action_date
    FROM test_job_list
    WHERE job_id = 164354
  )
SELECT count(*) AS num_consec_dates,
       min(action_date) AS earliest,
       max(action_date) AS latest
FROM groups
group by grp
ORDER BY num_consec_dates desc, earliest desc


NUM_CONSEC
DATES      EARLIEST  LATEST
---------- --------- ---------
         5 20-FEB-17 24-FEB-17
         5 13-FEB-17 17-FEB-17
         1 10-FEB-17 10-FEB-17

Answer 1

10 days = 2 full weeks. For 11 days, you can look at the date 14 days ago and see if it is exactly two weeks ago:

select tjl.*,
       lag(action_date, 10) over (partition by job id order by action_date) as minad_2weeks
from test_job_list;

A simple trick works for 10 days:

Then you can get jobs with no such period by using aggregation:

select job_id
from (select tjl.*,
             lag(action_date, 9) over (partition by job_id order by action_date) as lag9_ad
      from test_job_list tjl
     ) tjl
group by job_id
having max(action_date - lag9_ad) > action_date - 14;

That is, if the 9th date back is within the past two weeks, then there are two full weeks of dates.

Answer 2

You can determine which day of the week it is using (monday = 0, sunday = 6):

TRUNC( action_date ) - TRUNC( action_date, 'IW' )

And, using the LAG analytic function you can then compare whether the previous entry is the previous working day and use this to determine the group:

Oracle Setup:

CREATE TABLE test_job_list ( JOB_ID,     W, ACTION_DATE ) AS
SELECT 164354, 'H', DATE '2017-02-10' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-13' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-14' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-15' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-16' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-17' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-20' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-21' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-22' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-23' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-24' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-01' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-02' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-03' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-06' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-07' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-08' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-10' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-13' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-14' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-15' FROM DUAL;

Query:

SELECT job_id,
       MIN( action_date ) AS start_date,
       MAX( action_date ) AS end_date,
       COUNT( 1 ) AS num_days
FROM   (
  SELECT job_id,
         action_date,
         SUM( has_changed_group ) OVER ( PARTITION BY job_id ORDER BY action_date )
           AS group_id
  FROM   (
    SELECT job_id,
           action_date,
           CASE WHEN
             LAG( action_date ) OVER ( PARTITION BY job_id ORDER BY action_date )
             = action_date - CASE TRUNC( action_date ) - TRUNC( action_date, 'IW' )
                             WHEN 0 THEN 3 ELSE 1 END
             THEN 0
             ELSE 1
           END AS has_changed_group
    FROM   test_job_list
  )
)
GROUP BY job_id, group_id
-- HAVING COUNT(1) >= 10;

Output:

    JOB_ID START_DATE          END_DATE              NUM_DAYS
---------- ------------------- ------------------- ----------
    164354 2017-02-10 00:00:00 2017-02-24 00:00:00         11
    214325 2017-03-10 00:00:00 2017-03-15 00:00:00          4
    214325 2017-03-01 00:00:00 2017-03-08 00:00:00          6

Alternative:

If you just want the jobs where there has never been a period of 10 consecutive working days then you can use the COUNT() analytic function and specify a RANGE window:

SELECT job_id
FROM   (
  SELECT job_id,
         COUNT( 1 ) OVER ( PARTITION BY job_id
                           ORDER BY action_date
                           RANGE BETWEEN INTERVAL '13' DAY PRECEDING
                                 AND     INTERVAL  '0' DAY FOLLOWING )
           AS num_days
  FROM   test_job_list
)
GROUP BY job_id
HAVING MAX( num_days ) < 10;

Output:

    JOB_ID
----------
    214325

Answer 3

I know this solution too long , but you can see all details on query by executing step by step

create table calendar1 as
select day_id,WEEK_DAY_SHORT,day_num_of_week  from  VITDWH.DW_MIS_TAKVIM as calendar order by day_id;

CREATE TABLE JOB_LIST (JOB_ID NUMBER,ACTION_DATE DATE);

INSERT INTO JOB_LIST VALUES(164354,TO_DATE('10-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(164354,TO_DATE('13-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(164354,TO_DATE('14-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(164354,TO_DATE('15-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(164354,TO_DATE('16-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(164354,TO_DATE('17-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(164354,TO_DATE('20-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(164354,TO_DATE('21-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(164354,TO_DATE('22-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(164354,TO_DATE('23-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(164354,TO_DATE('24-FEB-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(214325,TO_DATE('01-MAR-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(214325,TO_DATE('02-MAR-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(214325,TO_DATE('03-MAR-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(214325,TO_DATE('06-MAR-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(214325,TO_DATE('07-MAR-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(214325,TO_DATE('08-MAR-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(214325,TO_DATE('10-MAR-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(214325,TO_DATE('13-MAR-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(214325,TO_DATE('14-MAR-17','DD-MON-YY'));
INSERT INTO JOB_LIST VALUES(214325,TO_DATE('15-MAR-17','DD-MON-YY'));

COMMIT;

---

with a1 as 
(
select A.JOB_ID,A.ACTION_DATE,B.DAY_ID,
       (case when action_date is not null and lag(action_date) over(partition by job_id order by day_id) is null then action_date else null end) start_date,
       (case when action_date is not null and lead(action_date) over(partition by job_id order by day_id) is  null then action_date else null end) max_date 
  from
(
select * from  calendar1 
 WHERE DAY_ID >=(select MIN(ACTION_DATE) from  JOB_LIST) 
   AND DAY_ID <= (select MAX(ACTION_DATE) from  JOB_LIST)
ORDER BY DAY_ID   
)
B  LEFT OUTER JOIN
 JOB_LIST A 
 PARTITION BY (A.JOB_ID) ON (A.ACTION_DATE= B.DAY_ID) 
ORDER BY A.JOB_ID,DAY_ID
)
,a2 as 
(
select * from  a1 where start_date is not null or max_date is not null
)
,a3 as 
(
select a2.*,lead(max_date) over(partition by job_id order by day_id)  end_date
 from  a2
)
select a.job_id,a.start_date,nvl(a.maX_date,a.end_date) end_date, (nvl(a.maX_date,a.end_date) -a.start_date) +1 date_count 
 from a3 a where start_date is not null;

Answer 4

Edit 2

First version had many issues, this one should work.

An option is to join the table with itself on the job_id, and filtering on the right side only the rows of the two weeks preceding the date on the left side. Then you can count the reimaining dates.

select  JOB_ID
from    (
            select  g1.JOB_ID, count(g2.ACTION_DATE) CNT
            from    GROUPS g1
            join    GROUPS g2
            on      g1.JOB_ID = g2.JOB_ID
            where   g2.ACTION_DATE between g1.ACTION_DATE - 13 and g1.ACTION_DATE
            group by g1.JOB_ID, g1.ACTION_DATE
        ) t1
group by JOB_ID
having   max(CNT) < 10