w3resource

AdventureWorks Database: Compare year-to-date sales between employees for specific terrotery

SQL Query - AdventureWorks: Exercise-110 with Solution

110 From the following query write a query in SQL to compare year-to-date sales between employees for specific terrotery. Return TerritoryName, BusinessEntityID, SalesYTD, and the salesquota coming in next row.

Sample table: Sales.vSalesPerson


Click to view Full table

Sample Solution:

SELECT TerritoryName, BusinessEntityID, SalesYTD,   
       LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales  
FROM Sales.vSalesPerson  
WHERE TerritoryName IN ('Northwest', 'Canada')   
ORDER BY TerritoryName; 

Sample Output:

territoryname|businessentityid|salesytd    |nextrepsales|
-------------+----------------+------------+------------+
Canada       |             282|2604540.7172|1453719.4653|
Canada       |             278|1453719.4653|           0|
Northwest    |             284|1576562.1966|1573012.9383|
Northwest    |             283|1573012.9383|1352577.1325|
Northwest    |             280|1352577.1325|           0|

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Find different sales quotas for a employee over next years.
Next: Find the differences in sales quota for the next quarters.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

Ref : https://bit.ly/3EJPnMQ