AdventureWorks Database: Find different sales quotas for a employee over next years

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

109. From the following table, write a query in SQL to return the business entity ID, sales year, current sales quota, and the next year's sales quota for a specific salesperson. Use the LEAD window function to get the next year's sales quota, defaulting to 0 if not available. Filter the results for the salesperson with BusinessEntityID 277 for the years 2011 and 2012.

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 specific columns from the SalesPersonQuotaHistory table
SELECT 
    -- Selecting the BusinessEntityID column
    BusinessEntityID, 

    -- Extracting the year part from the QuotaDate and aliasing it as SalesYear
    date_part('year', QuotaDate) AS SalesYear, 

    -- Selecting the SalesQuota column and aliasing it as CurrentQuota
    SalesQuota AS CurrentQuota,   

    -- Using the LEAD window function to get the next SalesQuota, defaulting to 0 if not available
    LEAD(SalesQuota, 1, 0) OVER (
        -- Ordering the data by year
        ORDER BY date_part('year', QuotaDate)
    ) AS NextQuota  

-- Filtering data for a specific salesperson identified by BusinessEntityID
FROM 
    Sales.SalesPersonQuotaHistory  

-- Filtering records for the salesperson with ID 277 and the years 2011 and 2012
WHERE 
    BusinessEntityID = 277 
    AND date_part('year', QuotaDate) IN ('2011', '2012');

Explanation:

• This SQL code operates on a table named Sales.SalesPersonQuotaHistory.
• It retrieves data related to sales quotas for a specific salesperson (identified by BusinessEntityID) for the years 2011 and 2012.
• The retrieved data includes the BusinessEntityID, the year (QuotaDate extracted and aliased as SalesYear), the current sales quota (SalesQuota aliased as CurrentQuota), and the next sales quota (NextQuota) using the LEAD window function.
• The LEAD function is used to fetch the next value of SalesQuota, with a default of 0 if the next value is not available.
• The result set is ordered by year.

Sample Output:

businessentityid|salesyear|currentquota|nextquota|
----------------+---------+------------+---------+
             277|   2011.0|      565000|   872000|
             277|   2011.0|      872000|   846000|
             277|   2011.0|      846000|   952000|
             277|   2012.0|      952000|  1600000|
             277|   2012.0|     1600000|  1352000|
             277|   2012.0|     1352000|   839000|
             277|   2012.0|      839000|        0|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find the statistical variance of the sales quota for each quarter.
Next: Compare year-to-date sales between employees for specific terrotery .