AdventureWorks Database: Find the differences in sales quota for the next quarters
SQL Query - AdventureWorks: Exercise-111 with Solution
111. From the following table write a query in SQL to obtain the difference in sales quota values for a specified employee over subsequent calendar quarters. Return year, quarter, sales quota, next sales quota, and the difference in sales quota. Sort the result set on year and then by quarter, both in ascending order.
Sample table: Sales.SalesPersonQuotaHistory
Sample Solution:
SELECT date_part('year',quotadate) AS Year, date_part('quarter',quotadate) AS Quarter, SalesQuota AS SalesQuota,
LEAD(SalesQuota,1,0) OVER (ORDER BY date_part('year',quotadate), date_part('quarter',quotadate)) AS NextQuota,
SalesQuota - LEAD(SalesQuota,1,0) OVER (ORDER BY date_part('year',quotadate), date_part('quarter',quotadate)) AS Diff
FROM sales.salespersonquotahistory
WHERE businessentityid = 277 AND date_part('year',quotadate) IN (2012,2013)
ORDER BY date_part('year',quotadate), date_part('quarter',quotadate);
Sample Output:
year |quarter|salesquota|nextquota|diff | ------+-------+----------+---------+-------+ 2012.0| 1.0| 952000| 1600000|-648000| 2012.0| 2.0| 1600000| 1352000| 248000| 2012.0| 3.0| 1352000| 839000| 513000| 2012.0| 4.0| 839000| 1369000|-530000| 2013.0| 1.0| 1369000| 1171000| 198000| 2013.0| 2.0| 1171000| 971000| 200000| 2013.0| 3.0| 971000| 714000| 257000|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Compare year-to-date sales between employees for specific terrotery.
Next: Obtain the salary percentile of each employee for a department.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
How to request a random row in SQL?
Select a random row with MySQL:
SELECT column FROM table ORDER BY RAND() LIMIT 1
Select a random row with PostgreSQL:
SELECT column FROM table ORDER BY RANDOM() LIMIT 1
Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table ORDER BY NEWID()
Select a random row with IBM DB2:
SELECT column, RAND() as IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Select a random record with Oracle:
SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum = 1
Database: SQL Server, PostgreSQL Server, MySQL
Ref: https://bit.ly/39n35HP
- Weekly Trends
- 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
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook