Idea of how to start a query

Question

Maybe because it is Friday and my brain is not there, but ​I am thinking for my query it will contact a bunch of nested queries within the select section for each column. Also the top 50 items would be based on 2022 quantities which you can say that will be the outer query. Need to bounce some ideas

Answer 1

As others have noted it's hard to give much help without sample data and desired results. To help get you started, here is a sample query using Microsoft's sample database AdventureWorks2017. This query gets the years 2011 to 2014 since the data i n that database is from those years.

Declare @StartOfCurrentYear date = '20140101',  
  @StartOfOldestYear date = '20110101',  
  @EndOfCurrentYear date = '20141231';  
  
;With cteOrderQtys As  
(Select d.ProductID,  
  Sum(d.OrderQty) As Qty,  
  Sum(d.OrderQty*UnitPrice)/Sum(d.OrderQty) As AvgPrice  
From Sales.SalesOrderHeader h  
Inner Join Sales.SalesOrderDetail d On h.SalesOrderID = d.SalesOrderID  
Where h.OrderDate Between @StartOfCurrentYear And @EndOfCurrentYear  
Group By d.ProductID),  
  
cteRowNumber As  
(Select ProductID, Qty, AvgPrice,  
  Row_Number() Over(Order By Qty Desc) As rn  
From cteOrderQtys),  
  
cteGetTop10 As  
(Select ProductID, Qty, AvgPrice  
From cteRowNumber  
Where rn <= 50),  
  
cteAllSinceOldestYear As  
(Select Distinct d.ProductID,  
  Year(h.OrderDate) As Year,  
  Sum(d.OrderQty) Over(Partition By d.ProductID, Year(h.OrderDate)) As Qty,  
  Sum(d.OrderQty*UnitPrice) Over(Partition By d.ProductID, Year(h.OrderDate))/Sum(d.OrderQty) Over(Partition By d.ProductID, Year(h.OrderDate)) As AvgPrice  
From Sales.SalesOrderHeader h  
Inner Join Sales.SalesOrderDetail d On h.SalesOrderID = d.SalesOrderID  
Where h.OrderDate Between @StartOfOldestYear And @EndOfCurrentYear  
  And d.ProductID In (Select g.ProductID From cteGetTop10 g))  
  
Select ProductID,  
  IsNull(Max(Case When Year = 2014 Then Qty End), 0) As Qty2014,  
  Max(Case When Year = 2014 Then AvgPrice End) As AvgPrice2014,   
  IsNull(Max(Case When Year = 2013 Then Qty End), 0) As Qty2013,  
  Max(Case When Year = 2013 Then AvgPrice End) As AvgPrice2013,  
  Max(Case When Year = 2014 Then AvgPrice End) - Max(Case When Year = 2013 Then AvgPrice End) As [PriceDiff2014-2013],  
  IsNull(Max(Case When Year = 2012 Then Qty End), 0) As Qty2012,  
  Max(Case When Year = 2012 Then AvgPrice End) As AvgPrice2012,  
  Max(Case When Year = 2013 Then AvgPrice End) - Max(Case When Year = 2012 Then AvgPrice End) As [PriceDiff2013-2012],  
  IsNull(Max(Case When Year = 2011 Then Qty End), 0) As Qty2011,  
  Max(Case When Year = 2011 Then AvgPrice End) As AvgPrice2011,  
  Max(Case When Year = 2012 Then AvgPrice End) - Max(Case When Year = 2011 Then AvgPrice End) As [PriceDiff2012-2011]  
From cteAllSinceOldestYear  
Group By ProductID  
Order By Qty2014 Desc;  

If you want to download and use the AdventureWorks2017 database, instructions do download and install it are at adventureworks-install-configure