CODEWITHSUNDEEP
sql-serverjoin
Fernando Torres
Fernando Torres

Reputation: 1180

How can I join 3 SQL queries

How can this 3 queries be joined into 1.

Im not lookin for an UNION ALL result.

Per row I want Forecast, Budget, Actual

SELECT 
    ps.PersonId, 
    SUM(TF.Hours * r.Amount) as Forecast
FROM ProjectStaffing ps
INNER JOIN Rate r on (r.ProviderId = ps.ProviderId AND r.OrganizationId = ps.OrganizationId AND r.RoleId = ps.RoleId)
INNER JOIN TimeForecast TF on (TF.ProjectStaffingId = ps.Id)
WHERE ps.ProjectId = @ProjectId
GROUP BY ps.PersonId

SELECT 
    ps.PersonId, 
    SUM(tb.Hours * r.Amount) as Budget
FROM ProjectStaffing ps
INNER JOIN Rate r on (r.ProviderId = ps.ProviderId AND r.OrganizationId = ps.OrganizationId AND r.RoleId = ps.RoleId)
INNER JOIN TimeBudget tb on (tb.ProjectStaffingId = ps.Id)
WHERE ps.ProjectId = @ProjectId
GROUP BY ps.PersonId

SELECT 
    ps.PersonId, 
    SUM(ta.Hours * r.Amount) as Actual
FROM ProjectStaffing ps
INNER JOIN Rate r on (r.ProviderId = ps.ProviderId AND r.OrganizationId = ps.OrganizationId AND r.RoleId = ps.RoleId)
INNER JOIN TimeActual ta on (ta.ProjectStaffingId = ps.Id)
WHERE ps.ProjectId = @ProjectId
GROUP BY ps.PersonId

I tried this out but just gets strange results

SELECT 
    PS.PersonId, 
    SUM(TF.Hours * r.Amount) as Forecast,
    SUM(TB.Hours * r.Amount) as Budget,
    SUM(TA.Hours * r.Amount) as Actual
FROM ProjectStaffing PS
INNER JOIN Rate r on (r.ProviderId = PS.ProviderId AND r.OrganizationId = PS.OrganizationId AND r.RoleId = PS.RoleId)
LEFT JOIN TimeForecast TF on (TF.ProjectStaffingId = PS.Id)
LEFT JOIN TimeBudget TB   on (TB.ProjectStaffingId = PS.Id)
LEFT JOIN TimeActual TA   on (TA.ProjectStaffingId = PS.Id)
WHERE PS.ProjectId = @ProjectId
    AND TF.Hours is not null
GROUP BY PS.PersonId

Upvotes: 0

Views: 64

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

One straight-forward way to do it is to use Common Table Expressions.

If all three subqueries have rows for each PersonId, then you can INNER JOIN them in any order. If one of the subqueries doesn't have rows for all PersonIds, then LEFT JOIN it.

WITH CTE_Forecast AS
(
    SELECT 
        ps.PersonId, 
        SUM(TF.Hours * r.Amount) as Forecast
    FROM 
        ProjectStaffing ps
    INNER JOIN 
        Rate r ON (r.ProviderId = ps.ProviderId 
                   AND r.OrganizationId = ps.OrganizationId 
                   AND r.RoleId = ps.RoleId)
    INNER JOIN 
        TimeForecast TF on (TF.ProjectStaffingId = ps.Id)
    WHERE 
        ps.ProjectId = @ProjectId
    GROUP BY 
        ps.PersonId
)
, CTE_Budget AS
(
    SELECT 
        ps.PersonId, 
        SUM(tb.Hours * r.Amount) as Budget
    FROM 
        ProjectStaffing ps
    INNER JOIN 
        Rate r ON (r.ProviderId = ps.ProviderId 
                   AND r.OrganizationId = ps.OrganizationId 
                   AND r.RoleId = ps.RoleId)
    INNER JOIN 
        TimeBudget tb on (tb.ProjectStaffingId = ps.Id)
    WHERE 
        ps.ProjectId = @ProjectId
    GROUP BY 
        ps.PersonId
)
, CTE_Actual AS
(
    SELECT 
        ps.PersonId, 
        SUM(ta.Hours * r.Amount) as Actual
    FROM 
        ProjectStaffing ps
    INNER JOIN 
        Rate r ON (r.ProviderId = ps.ProviderId 
                   AND r.OrganizationId = ps.OrganizationId 
                   AND r.RoleId = ps.RoleId)
    INNER JOIN 
        TimeActual ta on (ta.ProjectStaffingId = ps.Id)
    WHERE 
        ps.ProjectId = @ProjectId
    GROUP BY 
        ps.PersonId
)
SELECT
    CTE_Forecast.PersonId,
    CTE_Forecast.Forecast,
    CTE_Budget.Budget, 
    CTE_Actual.Actual
FROM
    CTE_Forecast
INNER JOIN 
    CTE_Budget ON CTE_Budget.PersonId = CTE_Forecast.PersonId
INNER JOIN 
    CTE_Actual ON CTE_Actual.PersonId = CTE_Forecast.PersonId
;

Upvotes: 2

Fernando Torres
Fernando Torres

Reputation: 1180

I found out that this does the job. But it is too complex. There should be an easier way.

SELECT *
FROM
(
    SELECT 
        --ps.PersonId, 
        'FORECAST' AS Description,
        SUM(TF.Hours * r.Amount) as Total --Forecast
    FROM ProjectStaffing ps
    INNER JOIN Rate r on (r.ProviderId = ps.ProviderId AND r.OrganizationId = ps.OrganizationId AND r.RoleId = ps.RoleId)
    INNER JOIN TimeForecast TF on (TF.ProjectStaffingId = ps.Id)
    WHERE ps.ProjectId = @ProjectId
    --GROUP BY ps.PersonId

    UNION ALL

    SELECT 
        --ps.PersonId, 
        'BUDGET' AS Description,
        SUM(tb.Hours * r.Amount) as Total --Budget
    FROM ProjectStaffing ps
    INNER JOIN Rate r on (r.ProviderId = ps.ProviderId AND r.OrganizationId = ps.OrganizationId AND r.RoleId = ps.RoleId)
    INNER JOIN TimeBudget tb on (tb.ProjectStaffingId = ps.Id)
    WHERE ps.ProjectId = @ProjectId
    --GROUP BY ps.PersonId

    UNION ALL

    SELECT 
        --ps.PersonId, 
        'ACTUAL' AS Description,
        SUM(ta.Hours * r.Amount) as Total --Actual
    FROM ProjectStaffing ps
    INNER JOIN Rate r on (r.ProviderId = ps.ProviderId AND r.OrganizationId = ps.OrganizationId AND r.RoleId = ps.RoleId)
    INNER JOIN TimeActual ta on (ta.ProjectStaffingId = ps.Id)
    WHERE ps.ProjectId = @ProjectId
    --GROUP BY ps.PersonId
) p
PIVOT (SUM(p.Total) FOR [Description] in ([FORECAST], [BUDGET], [ACTUAL]) ) AS pvt

Upvotes: 0

Related Questions