AdventureWorks Database: Return the difference in sales quotas for a specific employee

Last update on December 20 2024 11:37:55 (UTC/GMT +8 hours)

181. From the following table write a query in SQL to return the difference in sales quotas for a specific employee over previous calendar quarters. Sort the results by salesperson with businessentity id 277 and quotadate year 2012 or 2013.

Sample table: sales.salespersonquotahistory

businessentityid|quotadate              |salesquota|rowguid                             |modifieddate           |
----------------+-----------------------+----------+------------------------------------+-----------------------+
             274|2011-05-31 00:00:00.000|     28000|99109bbf-8693-4587-bc23-6036ec89e1be|2011-04-16 00:00:00.000|
             274|2011-08-31 00:00:00.000|      7000|dfd01444-8900-461c-8d6f-04598dae01d4|2011-07-17 00:00:00.000|
             274|2011-12-01 00:00:00.000|     91000|0a69f453-9689-4ccf-a08c-c644670f5668|2011-10-17 00:00:00.000|
             274|2012-02-29 00:00:00.000|    140000|da8d1458-5fb9-4c3e-9ead-8f5ce1393047|2012-01-15 00:00:00.000|
             274|2012-05-30 00:00:00.000|     70000|760cef84-b980-417b-a667-7358c38857f0|2012-04-15 00:00:00.000|
             274|2012-08-30 00:00:00.000|    154000|fb29e024-f26a-49aa-a7cc-c99ae7ba4853|2012-07-16 00:00:00.000|
             274|2012-11-30 00:00:00.000|    107000|13947d2c-a254-47c9-8817-cbd186ffa526|2012-10-16 00:00:00.000|
             274|2013-02-28 00:00:00.000|     58000|bc1a222f-47a0-48d1-9c56-ac873269dc98|2013-01-14 00:00:00.000|
             274|2013-05-30 00:00:00.000|    263000|8b4e3cbf-f0ef-49c5-9a8c-87679055057e|2013-04-15 00:00:00.000|
             274|2013-08-30 00:00:00.000|    116000|e9de95e3-b119-4441-bd1d-b27fc4516022|2013-07-16 00:00:00.000|
             274|2013-11-30 00:00:00.000|     84000|7cea47b5-8391-4414-a866-ff6ec6628cd3|2013-10-16 00:00:00.000|
             274|2014-03-01 00:00:00.000|    187000|0cfb6474-20aa-46d3-a5c0-29dba2eda025|2014-01-15 00:00:00.000|
-- more --

Click to view Full table

Sample Solution:

-- Selecting the quotadate as Year, and extracting the quarter from the quotadate
SELECT quotadate AS Year, date_part('quarter',quotadate) AS Quarter, SalesQuota AS SalesQuota,  
       -- Retrieving the previous sales quota using the LAG window function
       LAG(SalesQuota) OVER (ORDER BY date_part('year',quotadate), date_part('quarter',quotadate)) AS PrevQuota,  
       -- Calculating the difference between the current sales quota and the previous sales quota
       SalesQuota - LAG(SalesQuota) OVER (ORDER BY date_part('year',quotadate), date_part('quarter',quotadate)) AS Diff  
-- Selecting from the salespersonquotahistory table
FROM sales.salespersonquotahistory  
-- Filtering records where businessentityid is 277 and year is either 2012 or 2013
WHERE businessentityid = 277 AND date_part('year',quotadate) IN (2012, 2013)  
-- Ordering the results by year and quarter
ORDER BY date_part('year',quotadate), date_part('quarter',quotadate);

Explanation:

This SQL query analyzes sales quota history for a specific salesperson.
Comments are added to explain each part of the query for better understanding and maintenance.
Here's a breakdown of what the query does:

It selects the quotadate as Year and extracts the quarter from the quotadate.
It retrieves the sales quota for each quarter.
It uses the LAG window function to fetch the previous sales quota based on the ordering by year and quarter.
It calculates the difference between the current sales quota and the previous sales quota.
It filters records for a specific salesperson (businessentityid = 277) and for years 2012 and 2013.
It orders the results by year and quarter.

Sample Output:

year                   |quarter|salesquota|prevquota|diff   |
-----------------------+-------+----------+---------+-------+
2012-02-29 00:00:00.000|    1.0|    952000|         |       |
2012-05-30 00:00:00.000|    2.0|   1600000|   952000| 648000|
2012-08-30 00:00:00.000|    3.0|   1352000|  1600000|-248000|
2012-11-30 00:00:00.000|    4.0|    839000|  1352000|-513000|
2013-02-28 00:00:00.000|    1.0|   1369000|   839000| 530000|
2013-05-30 00:00:00.000|    2.0|   1171000|  1369000|-198000|
2013-08-30 00:00:00.000|    3.0|    971000|  1171000|-200000|
2013-11-30 00:00:00.000|    4.0|    714000|   971000|-257000|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Return only rows that have a count of employees by title.
Next: Return a truncated date with 4 months added to the orderdate.