June 28, 2013 at 10:31 am
I am trying to create a query that will return the results in which the Average Sale Price (ASP) from 2013 is lower than the ASP from 2012. My attempt is below. Any suggestions? Thanks very much.
SELECT
[Year]
,[SKU Number]
,[Country]
,[Units]
,[Sales]
,[ASP]
FROM [Wirt].[dbo].[Basal_Media_2]
ORDER BY [SKU Number]
,[Country]
,[Year]
WHERE [ASP] for [YEAR] = '2012' > [ASP] for [YEAR] = '2013'
June 28, 2013 at 10:45 am
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]
June 28, 2013 at 10:48 am
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])
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply