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.



Follow us on Facebook and Twitter for latest update.