DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to rank sales?

Execute the following SQL Server T-SQL script in Management Studio Query Editor to rank sales of AdventureWorks Cycles.

-- SQL rank function - SQL row_number over order by

-- SQL common table expression - CTE - SQL over partition by

USE AdventureWorks;

WITH cteSOD

     AS (SELECT SalesOrderID,

                ProductName = Name,

                RANK()

                  OVER(PARTITION BY SalesOrderID ORDER BY p.ProductID) AS Rank

         FROM   Sales.SalesOrderDetail sod

                JOIN Production.Product p

                  ON sod.ProductID = p.ProductID),

     cteSR

     AS (SELECT sohsr.SalesOrderID,

                sr.[Name],

                RANK()

                  OVER(PARTITION BY SalesOrderID ORDER BY sr.[Name]) AS Rank

         FROM   Sales.SalesOrderHeaderSalesReason AS sohsr

                INNER JOIN Sales.SalesReason AS sr

                  ON sr.SalesReasonID = sohsr.SalesReasonID)

SELECT   SeqID = ROW_NUMBER()

                   OVER(ORDER BY soh.SalesOrderNumber),

         soh.SalesOrderNumber,

         ProductName,

         cteSR.[Name] AS Reason

FROM     cteSOD

         LEFT JOIN cteSR

           ON cteSR.SalesOrderID = cteSOD.SalesOrderID

              AND cteSR.Rank = cteSOD.Rank

         INNER JOIN Sales.SalesOrderHeader AS soh

           ON soh.SalesOrderID = COALESCE(cteSOD.SalesOrderID,cteSR.SalesOrderID)

WHERE    cteSR.[Name] is not null

ORDER BY soh.SalesOrderID,

         COALESCE(cteSR.Rank,cteSOD.Rank);

GO

/* Partial results

 

SeqID SalesOrderNumber  ProductName             Reason

5956  SO54366           Mountain-200 Black, 46  On Promotion

5957  SO54367           Mountain-200 Black, 42  On Promotion

5959  SO54369           AWC Logo Cap            On Promotion

5958  SO54369           Touring-2000 Blue, 50   Price

5960  SO54370           Patch Kit/8 Patches     Price

5961  SO54371           Road-550-W Yellow, 42   Price

5962  SO54373           Patch Kit/8 Patches     Price

*/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE