We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. SQL Project Planning
  5. Discussions

SQL Project Planning

  • marcusreaiche
     + 0 comments

    MS SQL Server solution * Uses two CTEs for improving readability * Uses the lag window function to determine the start of a new project (first CTE) * Uses the sum window function to compute the project groups (second CTE) * Group by proj_grp to find the start_date and end_date of each project and order by number of days (count(1)) and start_date

    with
enriched_projects as (
    select
        *,
        case
            when lag(end_date, 1) over(order by end_date) <> start_date then 1
            else 0
        end as proj_start
    from projects),
projects_grps as (
    select
        *,
        sum(proj_start) over (
            order by end_date
        ) as proj_grp
    from enriched_projects)
select
    min(start_date) as start_date,
    max(end_date) as end_date
from projects_grps
group by proj_grp
order by count(1), start_date;