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
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.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join