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.