Write a query to output the start and end dates listed by the number of days it took to complete in-CodePudding

Query for creating table

CREATE TABLE "HR"."PROJECT" 
("TASK_ID" NUMBER NOT NULL ENABLE, 
"START_DATE" DATE, 
"END_DATE" DATE, 
CONSTRAINT "CITI_PK" PRIMARY KEY ("TASK_ID")
)

Query for inserting data

Insert into HR.PROJECT (TASK_ID,START_DATE,END_DATE) values (1,to_date('01-11-21','DD-MM-RR'),to_date('02-11-21','DD-MM-RR'));
Insert into HR.PROJECT (TASK_ID,START_DATE,END_DATE) values (2,to_date('02-11-21','DD-MM-RR'),to_date('03-11-21','DD-MM-RR'));
Insert into HR.PROJECT (TASK_ID,START_DATE,END_DATE) values (3,to_date('03-11-21','DD-MM-RR'),to_date('04-11-21','DD-MM-RR'));
Insert into HR.PROJECT (TASK_ID,START_DATE,END_DATE) values (4,to_date('13-11-21','DD-MM-RR'),to_date('14-11-21','DD-MM-RR'));
Insert into HR.PROJECT (TASK_ID,START_DATE,END_DATE) values (5,to_date('14-11-21','DD-MM-RR'),to_date('15-11-21','DD-MM-RR'));
Insert into HR.PROJECT (TASK_ID,START_DATE,END_DATE) values (6,to_date('28-11-21','DD-MM-RR'),to_date('29-11-21','DD-MM-RR'));
Insert into HR.PROJECT (TASK_ID,START_DATE,END_DATE) values (7,to_date('30-11-21','DD-MM-RR'),to_date('01-12-21','DD-MM-RR'));

Here is the Table

Output required

Explanation

Project 1: Tasks 1, 2 and 3 are completed on consecutive days, so these are part of the project. Thus start date of project is 01-11-2021 and end date is 04-11-2021, so it took 3 days to complete the project.

Project 2: Tasks 4 and 5 are completed on consecutive days, so these are part of the project. Thus, the start date of project is 13-11-2021 and end date is 15-11-2021, so it took 2 days to complete the project.

Project 3: Only task 6 is part of the project. Thus, the start date of project is 28-11-2021 and end date is 29-11-2021, so it took 1 day to complete the project.

Project 4: Only task 7 is part of the project. Thus, the start date of project is 30-11-2021 and end date is 01-12-2021, so it took 1 day to complete the project.

Note : If there is more than one project that have the same number of completion days, then order by the start date of the project.

My approach was to use lead and lag function but i am not even close to get answer. Is my approach wrong or any other best way to solve this.

this is what i have tried

select * from 
(select lag(end_date) over (order by start_date) as nx_dt1 , start_date from project )
where to_date(start_date,'DD-MM-YYYY') <> to_date(nx_dt1,'DD-MM-YYYY')
order by start_date asc;

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE:

SELECT start_date,
       end_date,
       end_date - start_date AS days_to_complete
FROM   HR.project
MATCH_RECOGNIZE(
  ORDER BY start_date
  MEASURES
    FIRST(start_date) AS start_date,
    LAST(end_date)    AS end_date
  PATTERN (first_date successive_dates*)
  DEFINE
    successive_dates AS PREV(end_date) = start_date
)
ORDER BY start_date DESC

Which, for your sample data, outputs:

START_DATE END_DATE DAYS_TO_COMPLETE

2021-11-30 00:00:00 2021-12-01 00:00:00 1

2021-11-28 00:00:00 2021-11-29 00:00:00 1

2021-11-13 00:00:00 2021-11-15 00:00:00 2

2021-11-01 00:00:00 2021-11-04 00:00:00 3

START_DATE	END_DATE	DAYS_TO_COMPLETE
2021-11-30 00:00:00	2021-12-01 00:00:00	1
2021-11-28 00:00:00	2021-11-29 00:00:00	1
2021-11-13 00:00:00	2021-11-15 00:00:00	2
2021-11-01 00:00:00	2021-11-04 00:00:00	3

db<>fiddle here

CodePudding user response：

I gave this a try too MATCH_RECOGNIZE in the previous answer is interesting and something I have never seen before. I came up with something very different.

with P (TASK_ID,START_DATE,END_DATE) as( 

    select 1,to_date('01-11-21','DD-MM-RR'),to_date('02-11-21','DD-MM-RR') from dual union all
    select 2,to_date('02-11-21','DD-MM-RR'),to_date('03-11-21','DD-MM-RR') from dual union all
    select 3,to_date('03-11-21','DD-MM-RR'),to_date('04-11-21','DD-MM-RR') from dual union all
    select 4,to_date('13-11-21','DD-MM-RR'),to_date('14-11-21','DD-MM-RR') from dual union all
    select 5,to_date('14-11-21','DD-MM-RR'),to_date('15-11-21','DD-MM-RR') from dual union all
    select 6,to_date('28-11-21','DD-MM-RR'),to_date('29-11-21','DD-MM-RR') from dual union all
    select 7,to_date('30-11-21','DD-MM-RR'),to_date('01-12-21','DD-MM-RR') from dual 
    )
    select task_id, START_DATE,substr(SYS_CONNECT_BY_PATH(end_date, '/'),2,10) end_date,level days_took_to_complete 
    from p 
    where 
    CONNECT_BY_ISLEAF =1 
    start with   end_date not in (select start_date from p)
    connect by nocycle end_date = prior start_date
    order by level,START_DATE desc