Hello Community,
Can someone show me how to merge queries.
I have the following three queries and I would like to merge them.
I know it might appear as though I'm lazy, but I have tried to merge the queries myself using and an application called Devart - dbForge Studio 2019 for SQL Server, but I can't seem to get it right.
Your help will be greatly appreciated.
;
WITH Initial2017Sales_CTE
AS
(
SELECT SD.SalePrice, CU.CustomerName, SA.SaleDate
FROM Data.Make AS MK
INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID
INNER JOIN Data.Sales AS SA ON SA.SalesID = SD.SalesID
INNER JOIN Data.Customer CU ON SA.CustomerID = CU.CustomerID
WHERE YEAR(SaleDate) = 2017
)
,
AggregateSales_CTE (CustomerName, SalesForCustomer)
AS
(
SELECT CustomerName, SUM(SalePrice)
FROM Initial2017Sales_CTE
GROUP BY CustomerName
)
,
TotalSales_CTE (TotalSalePrice)
AS
(
SELECT SUM(SalePrice)
FROM Initial2017Sales_CTE
)
SELECT
IT.CustomerName
,IT.SalePrice
, IT.SaleDate
,FORMAT(IT.SalePrice / AG.SalesForCustomer, '0.00%') AS SaleAsPercentageForCustomer
,FORMAT(IT.SalePrice / TT.TotalSalePrice, '0.00%') AS SalePercentOverall
FROM Initial2017Sales_CTE IT
INNER JOIN AggregateSales_CTE AG
ON IT.CustomerName = AG.CustomerName
CROSS APPLY TotalSales_CTE TT
ORDER BY IT.SaleDate, IT.CustomerName
SELECT CustomerName
,SaleDate
,TotalSalePrice
,AVG(TotalSalePrice)
OVER (PARTITION BY CustomerName ORDER BY SaleDate
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS AverageSalePrice
FROM Data.Sales AS SA
INNER JOIN Data.Customer CU ON SA.CustomerID = CU.CustomerID
ORDER BY CustomerName, SaleDate
SELECT CustomerName
,SaleDate
,TotalSalePrice
,TotalSalePrice - LAG(TotalSalePrice,1)
OVER (PARTITION BY CustomerName ORDER BY SaleDate)
AS DifferenceToPreviousSalePrice
FROM Data.Sales SA
INNER JOIN Data.Customer CU
ON SA.CustomerID = CU.CustomerID
ORDER BY SaleDate
Thanks in advance
Carlton