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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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