new to sql, syntax question - 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

General SQL Server Forums

New to SQL Server Programming

new to sql, syntax question

Author

Topic

jrussell999
Starting Member

14 Posts

Posted - 2009-09-03 : 16:14:12

I have data like the following in an Access 2007 db.

Customer	Date		Year	Sale

X Corp		01/05/07	2007	$10,000
X Corp		03/03/08	2008	$20,000

Y Corp		04/18/08	2008	$10,000
Y Corp		07/11/09	2009	$25,000
Y Corp		08/20/09	2009	$30,000

Z Corp		02/02/09	2009	$40,000
Z Corp		06/23/09	2009	$50,000

I need to create a report in Excel 2007 showing customers that are new this year. In other words, I need to find customers that had sales in 2009 but not in 2008 or earlier.

For this data, the result would be Z Corp, and the total of $90,000.

I think sql is probably the best way to do this, but I'm having a hard time figuring out what kind of query I need to create. Conceptually, I think the following is what I want (almost), but how do I express this in sql?

select Customer, Sum of Sale
where sum of Sale for year 2008 = 0
and sum of Sale for year 2009 > 0

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-03 : 17:58:26

select customer,[Sales] = sum(sales)
from yourTable
where year = 2009
and customer not in (select customer from yourTable where year < 2009)
group by customer
having sum(sales) > 0

Jim

Everyday I learn something that somebody else already knew

jrussell999
Starting Member

14 Posts

Posted - 2009-09-04 : 11:40:44

Jim, thanks! That worked great. I modified it a bit - wound up not using the aggregate function, because they want to see some other details (sales reps, etc) along with the sales totals, and I can just do the totals in a pivot table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources