Thank you Gregory for this clear and concise article.

I cannot resist pitching in some Window function version of the queries;

;WITH CUST_SALES_DETAILS AS

(

SELECT

SSOH.CustomerID

,ROW_NUMBER() OVER

(PARTITION BY SSOH.SalesOrderID ORDER BY (SELECT NULL)) AS SSOH_RID

,COUNT(SSOD.SalesOrderDetailID) OVER

(PARTITION BY SSOH.SalesOrderID) AS COUNT_SSOD

FROM Sales.SalesOrderHeader SSOH

INNER JOIN Sales.SalesOrderDetail SSOD

ON SSOH.SalesOrderID = SSOD.SalesOrderID

)

SELECT

CSD.CustomerID

,CSD.COUNT_SSOD

/* ,DENSE_RANK() OVER (ORDER BY CSD.COUNT_SSOD DESC) AS SALES_RANK */

FROM CUST_SALES_DETAILS CSD

WHERE CSD.SSOH_RID = 1

AND CSD.COUNT_SSOD > 70;

GO

-------------------------------------------

;WITH CUST_SALE_BY_YEAR AS

(

SELECT

SSOH.CustomerID

,ROW_NUMBER() OVER

(PARTITION BY SSOH.CustomerID,YEAR(SSOH.[OrderDate])

ORDER BY (SELECT NULL)) AS CUST_RID

,YEAR(SSOH.[OrderDate]) AS SSOH_YEAR

,SUM(SSOH.SubTotal) OVER

(PARTITION BY SSOH.CustomerID,YEAR(SSOH.[OrderDate])) AS SSOH_SUBTOTAL

,(SUM(SSOH.SubTotal) OVER

(PARTITION BY SSOH.CustomerID,YEAR(SSOH.[OrderDate]))) * (0.10) AS SSOH_REBATE

FROMSales.SalesOrderHeaderSSOH

)

SELECT

CABY.CustomerID

/*

,DENSE_RANK() OVER (PARTITION BY CABY.SSOH_YEAR ORDER BY CABY.SSOH_SUBTOTAL DESC,CABY.SSOH_YEAR DESC) AS SALES_RANK

*/

,CABY.SSOH_YEAR

,CABY.SSOH_SUBTOTAL

,CABY.SSOH_REBATE

FROM CUST_SALE_BY_YEARCABY

WHERE CABY.CUST_RID = 1

AND CABY.SSOH_YEAR = 2008

AND CABY.SSOH_SUBTOTAL > 150000;

GO

-------------------------------------------