You are given a table, Tasks, containing three columns: Task Id. Start Date and

Question

You are given a table, Tasks, containing three columns: Task Id. Start Date and End Date. Each task requires only 1 day in order to complete. That is, the difference between the End Date and the Start Date is equal to 1 day for each row in the table. If the Start Date of the tasks are consecutive, then they are put of the same project. The projects table. PROJECTS, was accidentally deleted. Write a query to product a summary table of the projects undertaken. The summary table should include the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then output is ordered by the start date of the project.

Explanation / Answer

Query to create table:

create table tasks(
taskid number(3),
startdate date,
enddate date
);

Query to insert values :

insert into tasks (taskid, startdate, enddate) values (1, to_date('10/1/2015', 'mm/dd/yyyy'), to_date( '10/2/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (3, to_date('10/11/2015', 'mm/dd/yyyy'), to_date( '10/12/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (4, to_date('10/15/2015', 'mm/dd/yyyy'), to_date( '10/16/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (5, to_date('10/19/2015', 'mm/dd/yyyy'), to_date( '10/20/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (6, to_date('10/25/2015', 'mm/dd/yyyy'), to_date( '10/26/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (7, to_date('10/27/2015', 'mm/dd/yyyy'), to_date( '10/28/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (8, to_date('10/29/2015', 'mm/dd/yyyy'), to_date( '10/30/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (9, to_date('11/1/2015', 'mm/dd/yyyy'), to_date( '11/2/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (10, to_date('11/7/2015', 'mm/dd/yyyy'), to_date( '10/8/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (11, to_date('11/5/2015', 'mm/dd/yyyy'), to_date( '11/6/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (12, to_date('11/12/2015', 'mm/dd/yyyy'), to_date( '11/13/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (13, to_date('11/17/2015', 'mm/dd/yyyy'), to_date( '11/18/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (14, to_date('11/11/2015', 'mm/dd/yyyy'), to_date( '11/12/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (15, to_date('11/6/2015', 'mm/dd/yyyy'), to_date( '11/7/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (16, to_date('11/4/2015', 'mm/dd/yyyy'), to_date( '11/5/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (17, to_date('10/30/2015', 'mm/dd/yyyy'), to_date( '10/31/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (18, to_date('10/28/2015', 'mm/dd/yyyy'), to_date( '10/29/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (19, to_date('10/26/2015', 'mm/dd/yyyy'), to_date( '10/27/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (20, to_date('10/21/2015', 'mm/dd/yyyy'), to_date( '10/22/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (21, to_date('10/17/2015', 'mm/dd/yyyy'), to_date( '10/18/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (22, to_date('10/12/2015', 'mm/dd/yyyy'), to_date( '10/13/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (23, to_date('10/4/2015', 'mm/dd/yyyy'), to_date( '10/5/2015', 'mm/dd/yyyy'));
insert into tasks (taskid, startdate, enddate) values (24, to_date('10/2/2015', 'mm/dd/yyyy'), to_date( '10/3/2015', 'mm/dd/yyyy'));

Query to get summary:

SELECT SD,ED, (ed-sd) duration
FROM
(SELECT MIN(STARTDATE) SD, MAX(ENDDATE) ED, ( MAX(ENDDATE)-MIN(STARTDATE)) dd
FROM
(
SELECT STARTDATE, ENDDATE, ENDDATE - ROW_NUMBER() OVER (ORDER BY ENDDATE) DIST
FROM tasks
ORDER BY 1)
GROUP BY DIST
ORDER BY 3,1);

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.