All geek questions in one place

Find consecutive dates spanning a weekend

I have a list of tasks that indicates the work done on any specific task. When work is performed during the day, only one entry is added and the work_type parameter is enabled. Work is not performed on weekends. Jobs can work for a long period of time with an odd day here and there, but at some point in his life cycle he should have a period of work where he constantly works. Our management would like to highlight in the report any jobs where this longer period of work did not happen. There are other conditions related to the type of work and the name of the team, but the main point is the problem of time.

So ... how do I find assignments that have not had coordinated work for at least two consecutive weeks (10 working days)?

In the following case, work 164353 will not be included, since it has the necessary 10 consecutive days (excluding holidays), while task 214325 will be marked, because in the 9th place there is a gap that disrupted 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 create consecutive groups with several days against each group, but I'm struggling to adapt it to the weekend. In other words, the results below ideally show the 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
+4
source share
4 answers

, ( = 0, = 6):

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

, LAG, , ​​ :

Oracle:

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;


    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

, 10 , COUNT() RANGE:

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;


    JOB_ID
----------
    214325
+2

2

, .

, job_id , . .

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

10 days = 2 full weeks. Within 11 days, you can see the date 14 days ago and see 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 receive tasks without such a period 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 day ago in the last two weeks, that is, two full weeks of dates.

0
source

I know this solution for too long, but you can see all the details of the request by following 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;
0
source

Source: https://habr.com/ru/post/1673894/

More articles:


All Articles