Identifying Over-Budget Projects and Computing Salaries with SQL Joins

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

In enterprise database management, raw tables rarely contain the exact business metrics required for executive dashboards. Business intelligence tools and finance teams frequently demand aggregated calculations across multiple relational tables—such as determining whether a project's total personnel labor cost exceeds its allocated budget, or calculating an employee's compounding current salary after a series of dynamic promotions.

In this chapter, we explore advanced SQL querying techniques to:

• Translate annual aggregate metrics into pro-rata project duration costs using date arithmetic.
• Categorize financial statuses dynamically using conditional logic (CASE WHEN).
• Solve generic relational scaling problems where entities undergo variable numbers of historical events (e.g., employee promotions).

2. Core Mental Model: Pro-Rata Cost Distribution & Compounding Events

When tackling financial and relational SQL problems, it helps to break complex transformations down into modular stages:

Real-Life Analogy: The Rental Car & Mileage Breakdown

Think of calculating a project's labor cost like calculating car rental expenses. An employee's salary is quoted as an annual rate (like an annual lease fee), but a project only lasts a specific number of days (like a 3-day weekend rental). You cannot charge the full annual lease for a 92-day project; you must break it down into a daily rate (Annual Salary / 365), multiply by the exact project duration in days, and then compare that total against the project's allocated budget cap to label it as Over Budget or Within Budget.

3. Step-by-Step Evolution: Calculating Project Labor Costs

To evaluate whether a project is over budget, we must first compute the total payroll expense allocated to the personnel working on that project over its exact duration.

Step 3.1: Extracting Project Durations and Base Aggregates

Assuming we have a query pulling project metadata, we compute project duration using database-specific date functions (such as DATEDIFF in MS SQL Server):

SELECT 
    p.title,
    p.budget,
    DATEDIFF(day, p.start_date, p.end_date) AS project_duration,
    SUM(e.salary) AS total_yearly_salary
FROM projects p
JOIN project_assignments pa ON p.id = pa.project_id
JOIN employees e ON pa.employee_id = e.id
GROUP BY p.title, p.budget, p.start_date, p.end_date;

Step 3.2: Transforming Annual Salary into Pro-Rata Project Cost

Because employee salaries are stored as yearly figures (365 days), we derive a one-day salary rate for all assigned personnel, then multiply by the project duration:

SELECT 
    title,
    budget,
    project_duration,
    (SUM(total_yearly_salary) / 365.0) * project_duration AS project_salaries
FROM project_cost_summary
GROUP BY title, budget, project_duration;

4. Applying Conditional Logic with CASE WHEN

Once we have calculated the total project salary (project_salaries) and possess the allocated budget, we must label each project as either Over Budget or Within Budget.

Naive vs. Production Query Structure

When utilizing aggregated calculations or subqueries in standard ANSI SQL, remember that derived tables or subqueries must be provided with an explicit table alias (e.g., AS sub), otherwise the query engine will throw a syntax error.

SELECT 
    title,
    budget,
    CASE 
        WHEN project_salaries > budget THEN 'Over Budget'
        ELSE 'Within Budget'
    END AS label
FROM (
    SELECT 
        p.title,
        p.budget,
        (SUM(e.salary) / 365.0) * DATEDIFF(day, p.start_date, p.end_date) AS project_salaries
    FROM projects p
    JOIN project_assignments pa ON p.id = pa.project_id
    JOIN employees e ON pa.employee_id = e.id
    GROUP BY p.title, p.budget, p.start_date, p.end_date
) AS sub;

5. Industry Reality & Database Dialect Pitfalls

When writing SQL across different relational database management systems (RDBMS), minor syntax variations can break queries instantly.

6. Multi-Table Relational Challenge: Employee Current Salary with Promotions

Let us move on to our second complex scenario: computing the current salary of employees after factoring in a history of percentage-based promotions.

Table Schema Breakdown

1. employees Table:
   • employee_id (Primary Key)
   • name (Employee name: Alice, Bob, Charlie)
   • joining_salary (Base starting salary)
2. promotions Table:
   • employee_id (Foreign Key referencing employees)
   • promotion_date (Timestamp of promotion event)
   • percentage_increase (Numeric value representing percentage raise, e.g., 10, 20)

The Business Rule & Scalability Constraint

• Generic Solution Required: An employee can have zero promotions, 1 promotion, or $n$ promotions ($100+$ promotions). Hardcoding joins (e.g., LEFT JOIN promotion p1 ... LEFT JOIN promotion p2) is unacceptable and fails architectural standards.
• Compounding Calculation: Each promotion percentage must be applied sequentially to the running salary total.
• Rounding: Final output must be rounded to one decimal place.
• Zero-Promotion Edge Case: If an employee has no entries in the promotions table, their current salary remains precisely equal to their joining_salary.

7. Complete Flow Walkthrough: Project Budget Evaluation Output

Below is the representative execution flow and expected output matrix from our budget evaluation query:

8. Summary and Key Takeaways

• Pro-Rata Conversions: Always convert annual figures into daily rates using exact date arithmetic (DATEDIFF) before multiplying by project lifespans.
• Mandatory Subquery Aliasing: In ANSI-compliant SQL, every derived subquery table must be assigned an alias (e.g., AS sub) to prevent engine syntax errors.
• Conditional Formatting via CASE WHEN: Use CASE WHEN condition THEN 'Result' ELSE 'Alternative' END blocks to categorize numerical metrics into human-readable business labels.
• String Literal Standard: Always enclose text labels in single quotes ('Over Budget') rather than double quotes to maintain cross-platform compatibility.
• Generic Relational Architecture: When dealing with variable event histories (like employee promotions), avoid hardcoded sequential joins; design queries that scale dynamically to handle $n$ historical records per entity.
• Edge Case Resilience: Ensure queries gracefully handle entities with zero historical events (such as employees with no promotions defaulting back to their base joining_salary).
• Precision Rounding: Financial and salary computations frequently require explicit rounding (ROUND(val, 1)) to maintain clean decimal outputs for reporting layers.