Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511

    Unanswered: Select Query To Compare Aggregated Sum From Two Dates

    Hi,

    I have the following SELECT query which gives me sum of price grouped by productcategory:

    Code:
    SELECT TableA.ProductCategory, SUM(TableA.Price) As Price
     
    FROM TableA
     
    WHERE TableA.BusinessDate='08-June-2011'
     
    GROUP BY TableA.ProductCategory
    I can run the same query again using a different business date, say 07-June-2011, and it will give me the Prices from yesterday.

    What I would like to do is have a single query which will show me the ProductCategory, the sum of price on the 8th June, the sum price on the 7th June, and a calculated field showing me the difference between the two sum of prices. I had a try using nested SELECT statements but I ended up confusing myself. Please would someone give me some pointers?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this
    Code:
    SELECT T.ProductCategory, T.Price as TodayPrice, Y.Price as YesterdayPrice, (T.Price - Y.Price) as PriceDifference
    FROM 
    (SELECT ProductCategory, SUM(Price) As Price
    FROM TableA
    WHERE BusinessDate='08-June-2011'
    GROUP BY ProductCategory) as T -- Today
    LEFT OUTER JOIN 
    (SELECT ProductCategory, SUM(Price) As Price
    FROM TableA
    WHERE BusinessDate='07-June-2011'
    GROUP BY ProductCategory) as Y ON -- Yesterday
    		T.ProductCategory = Y.ProductCategory
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    That's exactly what I wanted.

    I'd confused myself on the correct syntax to join T and Y, but your example shows me where I went wrong. Thank you very much!

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I wrote another solution to this problem using a CTE. I thought it would be faster than my first attempt. Yet the cost in the execution plan was 1/3th for the first attempt and 2/3th for the CTE solution.

    I don't understand why the CTE solution is less efficient.
    Code:
    CREATE TABLE TableA(
    	id	int	not null,
    	ProductCategory	int not null,
    	Price	int not null,
    	BusinessDate	DATE not null
    )
    GO
    
    SELECT T.ProductCategory, T.Price as TodatPrice, Y.Price as YesterdayPrice, (T.Price -  Y.Price) as PriceDifference
    FROM 
    (SELECT ProductCategory, SUM(Price) As Price
    FROM TableA
    WHERE BusinessDate='08-June-2011'
    GROUP BY ProductCategory) as T -- Today
    LEFT OUTER JOIN 
    (SELECT ProductCategory, SUM(Price) As Price
    FROM TableA
    WHERE BusinessDate='07-June-2011'
    GROUP BY ProductCategory) as Y ON -- Yesterday
    		T.ProductCategory = Y.ProductCategory
    GO
    
    with CTE
    AS (
    SELECT ProductCategory, BusinessDate, SUM(Price) As Price
    FROM TableA
    WHERE BusinessDate BETWEEN '07-June-2011' and '08-June-2011'
    GROUP BY ProductCategory, BusinessDate
    )
    SELECT T.ProductCategory, T.Price as TodatPrice, Y.Price as YesterdayPrice, (T.Price -  Y.Price) as PriceDifference
    FROM CTE as T
    	INNER JOIN CTE as Y ON
    		T.ProductCategory = Y.ProductCategory AND
    		T.BusinessDate > Y.BusinessDate
    GO
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •