w3resource

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


Click to view Full table

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.



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