Return records with lower Average Sale Price compared to last year

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  June 28, 2013 at 10:45 am

  #1628354

  SELECT

  y2013.[Year]

  ,y2013.[SKU Number]

  ,y2013.[Country]

  ,y2013.[Units]

  ,y2013.[Sales]

  ,y2013.[ASP]

  ,y2012.[ASP]

  FROM [Wirt].[dbo].[Basal_Media_2] AS y2013

  JOIN [Wirt].[dbo].[Basal_Media_2] AS y2012

  ON {whatever keys for this tables are}

  ORDER BY y2013.[SKU Number]

  ,y2013.[Country]

  ,y2013.[Year]

  WHERE y2013.[Year] = 2013

  AND y2012.[Year] = 2012

  AND y2013.[ASP] < y2012.[ASP]

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 28, 2013 at 10:48 am

  #1628356

  You can't do a where clause as you're trying to do it. Unless someone suggest something better, you must use a subquery to find out the information from the previous year.

  Without some sample data and DDL, this is a guess I made. For better help, please take a look at the article linked in my signature.

  SELECT

  [Year]

  ,[SKU Number]

  ,[Country]

  ,[Units]

  ,[Sales]

  ,[ASP]

  FROM [Wirt].[dbo].[Basal_Media_2] x

  WHERE [Year] = 2013

  AND EXISTS(SELECT

  [Year]

  ,[SKU Number]

  ,[Country]

  ,[Units]

  ,[Sales]

  ,[ASP]

  FROM [Wirt].[dbo].[Basal_Media_2] y

  WHERE y.[Year] = 2012

  AND y.[SKU Number] = x.[SKU Number]

  AND y.[Country] = x.[Country]

  AND y.[ASP] > x.[ASP])

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2