Is This Right? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Is This Right?

Author

Topic

ross_gt
Starting Member

23 Posts

Posted - 2011-06-01 : 16:48:17

Is this query right to find the bottom 20 Canadian SKU's for revenue between 2010 and 2011?

USE Customer_Analytics

SELECT TOP 20 [SKU Number], [Extended Sales PMAR Amount]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted]
WHERE [Country of Ultimate Destination Code] = 'CA'
AND [Extended Sales PMAR Amount] >= '20100101'
AND [Extended Sales PMAR Amount] < '20110101'
ORDER BY [SKU Number] DESC;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-01 : 17:29:16

The query you have will just give you the 20 SKU Numbers ordered by SKU number, regardless of revenue. If you want to get the SKU Numbers having the bottom 20 amounts, you would need sum up the revenue. Assuming [Extended Sales PMAR Amount] is the revenue column, you will need to sum that.

But then, you are comparing [Extended Sales PMAR Amount] against dates. If [Extended Sales PMAR Amount] is indeed an amount, that is not correct. You need to find the date column in your table and use that to limit the query to 2010. So the code would be something like this:


SE Customer_Analytics

SELECT TOP 20 [SKU Number], sum([Extended Sales PMAR Amount]) as Amount
FROM [Customer_Analytics].[CleanData].[0607080910_sorted]
WHERE [Country of Ultimate Destination Code] = 'CA' 
AND YourDateColumn >= '20100101' 
AND YourDateColumn < '20110101'
ORDER BY Amount DESC;

ross_gt
Starting Member

23 Posts

Posted - 2011-06-01 : 17:59:54

Oh wow, I did not mean to do that. Thank you for the tip.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources