Northwind Related 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

SQL Server 2008 Forums

Transact-SQL (2008)

Northwind Related Question

Author

Topic

rutvij1984
Starting Member

7 Posts

Posted - 2010-08-15 : 14:51:55

Customer Perks Program. Management would like to setup a Platinum Buyer Club for all customers who have consistently placed 10 or more orders each year for the previous 2 years (1997 and 1998). Get the list of qualifying Customers, the average placed orders during 1997 and 1998, and the total number of orders placed on 1997 and 1998. Get one record per Customer.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-08-16 : 19:44:55

[CODE]with AnnualSales
as (
select CustomerName, Year(DateOfSale) SalesYear, count(*) CountSales
from Sales
where '19970101' <= DateOfSale and DateOfSale < '19990101'
group by CustomerName, Year(DateOfSale)
having count(*) >= 10
)
select
CustomerName, SalesYear, Avg(CountSales) AvgSalesPerYear, sum(CountSales) TotalSales
from AnnualSales a
where exists (
select CustomerName, SalesYear
from AnnualSales a1
where a.CustomerName = a1.CustomerName
and a.SalesYear = a1.SalesYear
group by a1.CustomerName, a1.SalesYear
having count(*) >= 2
);[/CODE]You didn't give any schema so I haven't run this. Good luck!

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources