Combining two select statements into one SQL Server CTE

Question

I am trying to create a CTE (or possible an alternative) where I take two existing select statements and join the to compare. Both of these statements already work as expected and return the desired results, however I would like to compare some of the columns in the results.

Query A:

SELECT
    Region.RegionID,
    Country.CountryName,
    Segment.SegmentName,
    SalesOrder.SalesMonth,
    FORMAT(SUM(SalesOrderLineItem.SalePrice), 'n') AS [SalePrice]
FROM
    Country
    INNER JOIN Region ON Region.CountryID = Country.CountryID
    INNER JOIN Segment ON Segment.SegmentID = Region.SegmentID
    INNER JOIN SalesRegion ON SalesRegion.RegionID = Region.RegionID
    INNER JOIN SalesOrder ON SalesOrder.SalesRegionID = SalesRegion.SalesRegionID
    INNER JOIN SalesOrderLineItem ON SalesOrderLineItem.SalesOrderID = SalesOrder.SalesOrderID
    INNER JOIN Product ON Product.ProductID = SalesOrderLineItem.ProductID
    INNER JOIN ProductCost ON Product.ProductID = ProductCost.ProductID
    AND ProductCost.CountryID = Country.CountryID
GROUP BY
    Region.RegionID,
    Segment.SegmentName,
    Country.CountryName,
    SalesOrder.SalesMonth

Query B:

    SELECT
    Country.CountryName,
    Segment.SegmentName,
    SalesKPI.SalesYear,
    FORMAT(SUM(SalesKPI.KPI), 'n') AS [KPI]
FROM
    SalesKPI
    INNER JOIN SalesPerson ON SalesKPI.SalesPersonID = SalesPerson.SalesPersonID
    INNER JOIN SalesRegion ON SalesRegion.SalesPersonID = SalesPerson.SalesPersonID
    INNER JOIN Region ON Region.RegionID = SalesRegion.RegionID
    INNER JOIN Segment ON Segment.SegmentID = Region.SegmentID
    INNER JOIN Country ON Country.CountryID = Region.CountryID
GROUP BY
    SalesKPI.SalesYear,
    Country.CountryName,
    Segment.SegmentName

Query A:

Query B:

I have tried the following code, however the sales and KPI (estimate) values are far higher than what they are supposed to be:

WITH sales_cte AS (
        SELECT
            Region.RegionID,
            Country.CountryName,
            Segment.SegmentName,
            SalesOrder.SalesMonth,
            SUM(SalesOrderLineItem.SalePrice) AS SalePrice
        FROM
            Country
            INNER JOIN Region ON Region.CountryID = Country.CountryID
            INNER JOIN Segment ON Segment.SegmentID = Region.SegmentID
            INNER JOIN SalesRegion ON SalesRegion.RegionID = Region.RegionID
            INNER JOIN SalesOrder ON SalesOrder.SalesRegionID = SalesRegion.SalesRegionID
            INNER JOIN SalesOrderLineItem ON SalesOrderLineItem.SalesOrderID = SalesOrder.SalesOrderID
            INNER JOIN Product ON Product.ProductID = SalesOrderLineItem.ProductID
            INNER JOIN ProductCost ON Product.ProductID = ProductCost.ProductID
            AND ProductCost.CountryID = Country.CountryID
        GROUP BY
            Region.RegionID,
            Segment.SegmentName,
            Country.CountryName,
            SalesOrder.SalesMonth
    ),
    kpi_cte AS (
        SELECT
            Country.CountryName,
            Segment.SegmentName,
            SalesKPI.SalesYear,
            SUM(SalesKPI.KPI) AS [KPI]
        FROM
            SalesKPI
            INNER JOIN SalesPerson ON SalesKPI.SalesPersonID = SalesPerson.SalesPersonID
            INNER JOIN SalesRegion ON SalesRegion.SalesPersonID = SalesPerson.SalesPersonID
            INNER JOIN Region ON Region.RegionID = SalesRegion.RegionID
            INNER JOIN Segment ON Segment.SegmentID = Region.SegmentID
            INNER JOIN Country ON Country.CountryID = Region.CountryID
        GROUP BY
            SalesKPI.SalesYear,
            Country.CountryName,
            Segment.SegmentName
    )
SELECT
    sales_cte.RegionID,
    sales_cte.CountryName,
    sales_cte.SegmentName,
    YEAR(sales_cte.SalesMonth),
    FORMAT(SUM(SalePrice), 'n') AS [Sales],
    FORMAT(SUM(kpi_cte.KPI), 'n') AS [Estimate]
FROM
    sales_cte
    INNER JOIN kpi_cte ON sales_cte.CountryName = sales_cte.CountryName
    AND sales_cte.SegmentName = kpi_cte.SegmentName
GROUP BY
    RegionID,
    sales_cte.CountryName,
    sales_cte.SegmentName,
    YEAR(SalesMonth)
ORDER BY
    YEAR(sales_cte.SalesMonth),
    sales_cte.CountryName ASC,
    sales_cte.RegionID

Query C:

An example of what it should look like is as follows:

RegionID	CountryName	SegmentName	Date	Sales	Estimate (KPI)
2	Canada	Midmarket	2001	792,579.90	777,009.00

Is there a reason that the sales and estimate (kpi) are returning the wrong values even though its the same initial select statements?

Answer 1

erm, how about just

SELECT
         M.[RegionID],
         M.[CountryName],
         M.[SegmentName],
         M.[SalesMonth],
         M.[SalePrice],
         Y.[SaleYear],
         Y.[KPI]
   FROM
         (
         SELECT
                  Region.RegionID,
                  Country.CountryName,
                  Segment.SegmentName,
                  SalesOrder.SalesMonth,
                  FORMAT(SUM(SalesOrderLineItem.SalePrice), 'n') AS [SalePrice]
            FROM
                  Country
               INNER JOIN
                  Region
                     ON Region.CountryID = Country.CountryID
               INNER JOIN
                  Segment
                     ON Segment.SegmentID = Region.SegmentID
               INNER JOIN
                  SalesRegion
                     ON SalesRegion.RegionID = Region.RegionID
               INNER JOIN
                  SalesOrder
                     ON SalesOrder.SalesRegionID = SalesRegion.SalesRegionID
               INNER JOIN
                  SalesOrderLineItem
                     ON SalesOrderLineItem.SalesOrderID = SalesOrder.SalesOrderID
               INNER JOIN
                  Product
                     ON Product.ProductID = SalesOrderLineItem.ProductID
               INNER JOIN
                  ProductCost
                     ON Product.ProductID = ProductCost.ProductID
                        AND ProductCost.CountryID = Country.CountryID
            GROUP BY
                  Region.RegionID,
                  Segment.SegmentName,
                  Country.CountryName,
                  SalesOrder.SalesMonth
         ) M
      JOIN
         (
         SELECT
                  Country.CountryName,
                  Segment.SegmentName,
                  SalesKPI.SalesYear,
                  FORMAT(SUM(SalesKPI.KPI), 'n') AS [KPI]
            FROM
                  SalesKPI
               INNER JOIN
                  SalesPerson
                     ON SalesKPI.SalesPersonID = SalesPerson.SalesPersonID
               INNER JOIN
                  SalesRegion
                     ON SalesRegion.SalesPersonID = SalesPerson.SalesPersonID
               INNER JOIN
                  Region
                     ON Region.RegionID = SalesRegion.RegionID
               INNER JOIN
                  Segment
                     ON Segment.SegmentID = Region.SegmentID
               INNER JOIN
                  Country
                     ON Country.CountryID = Region.CountryID
            GROUP BY
                  SalesKPI.SalesYear,
                  Country.CountryName,
                  Segment.SegmentName
         ) Y
            ON Y.[CountryName] = M.[CountryName]
               AND Y.[SegmentName] = M.[SegmentName]