Task 2

Write a SQL query that lets us compare planned hours to actual hours worked for each deliverable & project. This will then be used to alert us of instances where a project or deliverable is at the risk of exceeding its time-budget.


SELECT 
    d.ProjectId AS ProjectID,
    d.Id AS DeliverableID,
    CASE 
        WHEN lvl.Level = 4 THEN 'Level 4'
        WHEN lvl.Level = 3 THEN 'Level 3'
        WHEN lvl.Level = 2 THEN 'Level 2'
        WHEN lvl.Level = 1 THEN 'Level 1'
    END AS Level,
    lvl.PlannedDays AS TotalPlannedDays,
    DATEDIFF(day, d.StartDate, d.[Expected/Actual End Date]) AS TotalActualDaysWorked,
    lvl.PlannedDays - DATEDIFF(day, d.StartDate, d.[Expected/Actual End Date]) AS TotalDaysRemaining,
    (lvl.PlannedDays - DATEDIFF(day, d.StartDate, d.[Expected/Actual End Date])) * 100.0 / lvl.PlannedDays AS PercentDaysRemaining
FROM 
    Deliverable d
CROSS APPLY (
    VALUES 
        (4, d.[Planned Days Level 4]),
        (3, d.[Planned Days Level 3]),
        (2, d.[Planned Days Level 2]),
        (1, d.[Planned Days Level 1])
) lvl(Level, PlannedDays)
WHERE 
    d.[Expected/Actual End Date] IS NOT NULL
ORDER BY 
    ProjectID, DeliverableID, Level;