SQLTeam.com | Weblogs | Forums

How to Merge Queries

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

Hi Community,

I was thinking that the solution would be to create subqueries. Would that be right?

Yes
you are right Carlton

Derived Tables