Idea of how to start a query

Jonathan Brotto 1,076 Reputation points
2022-07-15T19:54:10.147+00:00

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

image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,290 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,518 questions
{count} votes

Accepted answer
  1. Tom Cooper 8,461 Reputation points
    2022-07-18T04:23:49.133+00:00

    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

    0 comments No comments

0 additional answers

Sort by: Most helpful