You are given a table, Tasks, containing three columns: TaskId, StartDate and En

Question

You are given a table, Tasks, containing three columns: TaskId, StartDate and EndDate. Each task requires only 1 day in order to complete. That is, the difference between the End Date and the StartDate is equal to 1 day for each row in the table. If the StartDate of the tasks are consecutive, then they are part of the same project. The projects table, Projects, was accidentally deleted. Write a query to produce 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

Queries:

SELECT START_DATE, END_DATE, END_DATE - ROW_NUMBER() OVER (ORDER BY END_DATE) DIST

FROM PROJECTS;

SELECT SD,ED FROM

(SELECT MIN(START_DATE) SD, MAX(END_DATE) ED, ( MAX(END_DATE)-MIN(START_DATE)) dd

FROM

(

SELECT START_DATE, END_DATE, END_DATE - ROW_NUMBER() OVER (ORDER BY END_DATE) DIST

FROM PROJECTS

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.