Project Budget Analysis: Prorating Salaries

1. Why Do We Need This? (Problem Statement & Status Quo)

In corporate finance and resource management, evaluating whether a project stays within its allocated financial limits is critical. Often, organizations assign multiple employees to work on a specific project for a limited duration (e.g., a few months). However, employee salary data in databases is typically stored on an annual basis.

The core challenge is calculating the exact portion of the annual salary consumed by the project team during their specific engagement window and comparing that prorated cost against the project's fixed budget. Failing to calculate this correctly leads to inaccurate cost forecasting and budget overruns.

2. Core Mental Model & Real-Life Analogy

Real-Life Analogy: The Subcontractor Team

Imagine hiring a specialized construction crew for a 4-month kitchen renovation. The crew members have yearly salaries (or annual rates), but they are only working on your specific house for a fraction of that year.

To determine if your $100,000 renovation budget covers the labor, you cannot simply add up their full yearly salaries. Instead, you must:

1. Aggregate the total annual cost of all assigned workers.
2. Determine what fraction of the year the project spans (e.g., 4 months out of 12, or roughly $1/3$).
3. Scale the total yearly salary down to match that exact working window before comparing it to your budget.

3. The Conceptual Math: Naive vs. Prorated Approach

Let's look at the numbers discussed in the lecture to understand the exact financial discrepancy:

• Project Budget: $100,000
• Project Duration: ~4 months (March, April, May, etc.)
• Assigned Employees & Yearly Salaries:
  • Employee 2: $120,000
  • Employee 3: $90,000
  • Employee 4: $110,000
  • Total Combined Yearly Salary: $320,000

If we fail to prorate and mistakenly look at a fraction of the total yearly salary:

Since $106,666.67exceeds the$100,000` budget, the project is over budget.

4. Step-by-Step Table Synthesis: Schema Relationships

To compute this via SQL, we must bridge multiple relational tables containing projects, project assignments, and employee salaries.

5. Constructing the Base Query: Joining Tables

Before performing aggregations, we must gather all necessary attributes by joining the three tables together. We connect projects to the junction table, and then join the junction table to the employee records.

SELECT 
    p.id AS project_id,
    p.title,
    p.start_date,
    p.end_date,
    p.budget,
    e.id AS employee_id,
    e.salary
FROM projects p
INNER JOIN project_employees pe 
    ON p.id = pe.project_id
INNER JOIN employees e 
    ON pe.employee_id = e.id;

Data Flow Walkthrough

6. Simplifying with Common Table Expressions (CTEs)

To keep our code clean, maintainable, and easy to debug, we wrap our base retrieval query inside a Common Table Expression (CTE) named city (or project_data).

WITH city AS (
    SELECT 
        p.title,
        p.start_date,
        p.end_date,
        p.budget,
        e.salary
    FROM projects p
    INNER JOIN project_employees pe 
        ON p.id = pe.project_id
    INNER JOIN employees e 
        ON pe.employee_id = e.id
)
SELECT * 
FROM city;

7. Grouping and Aggregating Yearly Salaries

When grouping by project title, multiple employees assigned to the same project will collapse into a single summary row.

To find the total yearly salary pool for all employees on a given project, we use the SUM() aggregate function:

WITH city AS (
    SELECT 
        p.title,
        p.start_date,
        p.end_date,
        p.budget,
        e.salary
    FROM projects p
    INNER JOIN project_employees pe 
        ON p.id = pe.project_id
    INNER JOIN employees e 
        ON pe.employee_id = e.id
)
SELECT 
    title,
    SUM(salary) AS total_yearly_salary
FROM city
GROUP BY title;

8. Calculating Project Duration Without Duplication Pitfalls

When computing the duration of a project in days using date difference functions (such as DATEDIFF), a common SQL pitfall occurs during aggregation.

Note: Because a project has multiple rows in our joined dataset (one row per assigned employee), applying SUM(DATEDIFF(...)) would incorrectly multiply the number of days by the number of employees.

To prevent this distortion, we use MAX() or MIN() because the start and end dates are identical across all rows for a specific project.

WITH city AS (
    SELECT 
        p.title,
        p.start_date,
        p.end_date,
        p.budget,
        e.salary
    FROM projects p
    INNER JOIN project_employees pe 
        ON p.id = pe.project_id
    INNER JOIN employees e 
        ON pe.employee_id = e.id
)
SELECT 
    title,
    SUM(salary) AS total_yearly_salary,
    MAX(DATEDIFF(end_date, start_date)) AS project_duration_days
FROM city
GROUP BY title;

9. Summary and Key Takeaways

• Problem Context: Budget analysis requires comparing fixed project funds against the prorated labor costs of assigned staff.
• Data Fragmentation: Employee salaries are stored annually, requiring careful scaling based on exact project durations.
• Relational Joins: Connecting projects, project_employees, and employees tables is mandatory to assemble the complete salary pool.
• Code Maintainability: Utilizing Common Table Expressions (CTEs) simplifies complex multi-step queries and debugging.
• Aggregation Best Practices: Use SUM() for accumulating financial figures like salaries across multiple team members.
• Avoiding Multiplication Bugs: Use MAX() or MIN() when extracting project dates and durations within grouped queries to prevent duplicate counting across employee rows.
• Financial Validation: Dividing aggregated yearly salaries by project duration ratios reveals whether an initiative is operating over or under budget.