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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to check the YTD performance of the sales reps?

Execute the following SQL Server T-SQL script in SSMS Query Editor to list YTD and Last Year sales by rep:

USE AdventureWorks2008;

 

SELECT row_number()

         OVER(PARTITION BY CountryRegionName ORDER BY SalesYTD ASC) AS SeqNo,

       CountryRegionName,

       FirstName,

       LastName,

       JobTitle,

       SalesYTD = '$' + convert(VARCHAR,SalesYTD,1),

       SalesLastYear = '$' + convert(VARCHAR,SalesLastYear,1)

FROM   Sales.vSalesPerson

 

SeqNo CountryRegionName FirstName LastName JobTitle SalesYTD SalesLastYear
1 Australia Lynn Tsoflias Sales Representative $1,758,385.93 $2,278,548.98
1 Canada Garrett Vargas Sales Representative $1,764,938.99 $1,620,276.90
2 Canada José Saraiva Sales Representative $3,189,356.25 $2,038,234.65
1 France Ranjit Varkey Chudukatil Sales Representative $3,827,950.24 $2,396,539.76
1 Germany Rachel Valdez Sales Representative $2,241,204.04 $1,307,949.79
1 United Kingdom Jae Pak Sales Representative $5,015,682.38 $1,635,823.40
1 United States Pamela Ansman-Wolfe Sales Representative $0.00 $1,927,059.18
2 United States Syed Abbas Pacific Sales Manager $219,088.88 $0.00
3 United States Amy Alberts European Sales Manager $636,440.25 $0.00
4 United States Stephen Jiang North American Sales Manager $677,558.47 $0.00
5 United States Tete Mensa-Annan Sales Representative $1,931,620.18 $0.00
6 United States Tsvi Reiter Sales Representative $2,811,012.72 $1,849,640.94
7 United States Shu Ito Sales Representative $3,018,725.49 $2,073,506.00
8 United States David Campbell Sales Representative $3,587,378.43 $1,371,635.32
9 United States Jillian Carson Sales Representative $3,857,163.63 $1,997,186.20
10 United States Michael Blythe Sales Representative $4,557,045.05 $1,750,406.48
11 United States Linda Mitchell Sales Representative $5,200,475.23 $1,439,156.03

 

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