Joining two tables on the basis of subquery

Question

I have two tables (UOF and SUBCON). I want to join them with the help of a subquery. How can I do this?

    ------------------UOF


DECLARE @Date DATE=
(
    SELECT MAX(RecordDate)
    FROM dw_UtilizationPredictionReport
);
SELECT RecordDate,
       DWVerticalGroup,
       OnOff,
       LocationGrouping,
       WFMGrade,
       SUM(BilledHrs) BHRS,
       SUM(AvailableHrs) AHRS,
       SUM(TotalFTE) AS OffTFTE,
       SUM(BilledFTE) AS OffBFTE
FROM dw_UtilizationPredictionReport
WHERE RecordDate = @Date
      AND OnOff = 'Offshore'
GROUP BY DWVerticalGroup,
         OnOff,
         LocationGrouping,
         WFMGrade,
         RecordDate;


----------------SUBCON
SELECT DISTINCT
       WFMgrade,
       SUM(TotalFTE) AS CTFTE,
       SUM(TotalFTE) AS OverallFTE
FROM dbo.dw_UtilizationPredictionReport
WHERE WFMgrade = 'Cont'
GROUP BY(WFMgrade);

Answer 1

Try joining the two subqueries on the common column WFMGrade:

SELECT
    t1.RecordDate,
    t1.DWVerticalGroup,
    t1.OnOff,
    t1.LocationGrouping,
    t1.WFMGrade,
    t1.BHRS,
    t1.AHRS,
    t1.OffTFTE,
    t1.OffBFTE,
    COALESCE(t2.CTFTE, 0) CTFTE,
    COALESCE(t2.OverallFTE, 0) OverallFTE
FROM
(
    SELECT RecordDate, DWVerticalGroup, OnOff, LocationGrouping, WFMGrade,
        SUM(BilledHrs) BHRS, SUM(AvailableHrs) AHRS, SUM(TotalFTE) OffTFTE,
        SUM(BilledFTE) OffBFTE
    FROM dw_UtilizationPredictionReport 
    WHERE RecordDate=@Date AND OnOff = 'Offshore'
    GROUP BY
        DWVerticalGroup, OnOff, LocationGrouping, WFMGrade, RecordDate
) t1
LEFT JOIN
(
    SELECT WFMgrade, SUM(TotalFTE) CTFTE, SUM(TotalFTE) OverallFTE
    FROM dbo.dw_UtilizationPredictionReport
    WHERE WFMgrade = 'Cont'
    GROUP BY WFMgrade
) t2
    ON t1.WFMGrade t2.WFMGrade;