Всем начинающим советую посмотреть ролик http://www.techdays.ru/videos/1354.html
И привожу код используемый автором

USE AdventureWorks2008
SELECT pc.name AS productcategory
,ps.name AS subcategory
,DATEPART(yy,sh.orderdate) AS orderyear
,'Q'+DATENAME(qq,sh.orderdate) AS orderqtr
,SUM(sd.unitprice*sd.orderqty) AS salesamount
,sales.salesterritory.[name] AS territorry
,Sales.salesterritory.[group] AS region

FROM Production.ProductSubcategory AS ps join
sales.salesorderheader AS sh join
sales.salesorderdetail AS sd ON sh.salesorderId=sd.salesorderid join
production.product AS p ON sd.productid=p.productid ON
ps.productsubcategoryid=
p.productsubcategoryid join
production.productcategory as pc on ps.productcategoryid =
pc.productcategoryid join
sales.salesterritory on sh.territoryid = sales.salesterritory.territoryid
where
(sh.orderdate BETWEEN '20030101' AND '20041231')
group by
datepart(yy, sh.orderdate),
pc.name,
ps.name,
'Q'+ datename(qq, sh.orderdate),
ps.productsubcategoryid,
sales.salesterritory.name,
sales.salesTerritory.[group]