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?
CodePudding user response:
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]