w3resource

AdventureWorks Database: Fetch columns using multiple CTE in a single query

SQL Query - AdventureWorks: Exercise-164 with Solution

164. From the following table write a query in SQL to find the SalesPersonID, salesyear, totalsales, salesquotayear, salesquota, and amt_above_or_below_quota columns. Sort the result set in ascending order on SalesPersonID, and SalesYear columns.

Sample table: Sales.SalesOrderHeader 
salesorderid|revisionnumber|orderdate              |duedate                |shipdate               |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal   |taxamt    |freight  |totaldue   |comment|rowguid                             |modifieddate           |
------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+-----------+----------+---------+-----------+-------+------------------------------------+-----------------------+
       43659|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO522145787        |10-4020-000676|     29825|          279|          5|            985|            985|           5|       16281|105041Vi84182         |              | 20565.6206| 1971.5149| 616.0984| 23153.2339|       |79b65321-39ca-4115-9cba-8fe0903e12e6|2011-06-07 00:00:00.000|
       43660|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18850127500      |10-4020-000117|     29672|          279|          5|            921|            921|           5|        5618|115213Vi29411         |              |  1294.2529|  124.2483|  38.8276|  1457.3288|       |738dc42d-d03b-48a1-9822-f95a67ea7389|2011-06-07 00:00:00.000|
       43661|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18473189620      |10-4020-000442|     29734|          282|          6|            517|            517|           5|        1346|85274Vi6854           |             4| 32726.4786| 3153.7696|  985.553| 36865.8012|       |d91b9131-18a4-4a11-bc3a-90b6f53e9d74|2011-06-07 00:00:00.000|
       43662|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18444174044      |10-4020-000227|     29994|          282|          6|            482|            482|           5|       10456|125295Vi53935         |             4| 28832.5289| 2775.1646| 867.2389| 32474.9324|       |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000|
       43663|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18009186470      |10-4020-000510|     29565|          276|          4|           1073|           1073|           5|        4322|45303Vi22691          |              |   419.4589|   40.2681|  12.5838|   472.3108|       |9b1e7a40-6ae0-4ad3-811c-a64951857c4b|2011-06-07 00:00:00.000|
       43664|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO16617121983      |10-4020-000397|     29898|          280|          1|            876|            876|           5|         806|95555Vi4081           |              | 24432.6088| 2344.9921|   732.81| 27510.4109|       |22a8a5da-8c22-42ad-9241-839489b6ef0d|2011-06-07 00:00:00.000|
       43665|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO16588191572      |10-4020-000146|     29580|          283|          1|            849|            849|           5|       15232|35568Vi78804          |              | 14352.7713| 1375.9427| 429.9821| 16158.6961|       |5602c304-853c-43d7-9e79-76e320d476cf|2011-06-07 00:00:00.000|
	   -- more --

Click to view Full table

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:

-- Common Table Expressions (CTEs) are used to define two temporary result sets: Sales_CTE and Sales_Quota_CTE.
-- Sales_CTE calculates the total sales for each salesperson per year.
WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear) AS
(
    -- Selecting SalesPersonID, calculating the sum of TotalDue as TotalSales, and extracting the year from OrderDate as SalesYear
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, DATE_PART('year', OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    -- Filtering out rows where SalesPersonID is not null
    WHERE SalesPersonID IS NOT NULL
    -- Grouping the results by SalesPersonID and the year extracted from OrderDate
    GROUP BY SalesPersonID, DATE_PART('year', OrderDate)
),
-- Sales_Quota_CTE calculates the total sales quota for each salesperson per year.
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear) AS
(
    -- Selecting BusinessEntityID, calculating the sum of SalesQuota as SalesQuota, and extracting the year from QuotaDate as SalesQuotaYear
    SELECT BusinessEntityID, SUM(SalesQuota) AS SalesQuota, DATE_PART('year', QuotaDate) AS SalesQuotaYear
    FROM Sales.SalesPersonQuotaHistory
    -- Grouping the results by BusinessEntityID and the year extracted from QuotaDate
    GROUP BY BusinessEntityID, DATE_PART('year', QuotaDate)
)
-- The main query combines data from the two CTEs to calculate the difference between total sales and sales quota for each salesperson and year.
SELECT SalesPersonID
    , SalesYear
    , CAST(TotalSales AS VARCHAR(10)) AS TotalSales
    , SalesQuotaYear
    , CAST(TotalSales AS VARCHAR(10)) AS SalesQuota
    , CAST(TotalSales - SalesQuota AS VARCHAR(10)) AS Amt_Above_or_Below_Quota
FROM Sales_CTE
-- Joining Sales_CTE with Sales_Quota_CTE on SalesPersonID and SalesYear
JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
                    AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
-- Ordering the results by SalesPersonID and SalesYear
ORDER BY SalesPersonID, SalesYear;

Explanation:

  • The query calculates the total sales and sales quota for each salesperson per year and then computes the difference between them.
  • Two CTEs are defined: Sales_CTE and Sales_Quota_CTE, each responsible for computing total sales and total sales quota respectively.
  • Sales_CTE calculates total sales by summing up the TotalDue column from the SalesOrderHeader table for each salesperson and year.
  • Sales_Quota_CTE calculates total sales quota by summing up the SalesQuota column from the SalesPersonQuotaHistory table for each salesperson and year.
  • The main query joins the two CTEs on SalesPersonID and SalesYear and calculates the difference between total sales and sales quota (TotalSales - SalesQuota).
  • The results are ordered by SalesPersonID and SalesYear.

Sample Output:

salespersonid|salesyear|totalsales|salesquotayear|salesquota|amt_above_or_below_quota|
-------------+---------+----------+--------------+----------+------------------------+
          274|   2011.0|32567.9155|        2011.0|32567.9155|-93432.084              |
          274|   2012.0|516197.368|        2012.0|516197.368|45197.3680              |
          274|   2013.0|485880.642|        2013.0|485880.642|-35119.358              |
          274|   2014.0|201288.519|        2014.0|201288.519|14288.5196              |
          275|   2011.0|986298.090|        2011.0|986298.090|-438701.90              |
          275|   2012.0|3806298.30|        2012.0|3806298.30|-225701.69              |
          275|   2013.0|4490942.20|        2013.0|4490942.20|-345057.79              |
          275|   2014.0|1191828.47|        2014.0|1191828.47|322828.474              |
          276|   2011.0|1294819.74|        2011.0|1294819.74|-788180.25              |
          276|   2012.0|4328860.05|        2012.0|4328860.05|465860.053              |
          276|   2013.0|4637217.83|        2013.0|4637217.83|-78782.167              |
          276|   2014.0|1434121.43|        2014.0|1434121.43|310121.430              |
          277|   2011.0|1477158.28|        2011.0|1477158.28|-805841.71              |
          277|   2012.0|4865650.84|        2012.0|4865650.84|122650.842              |
...

Contribute your code and comments through Disqus.

Previous: Get mailing addresses for companies in cities begin with PA outside US.
Next: Cross product of BusinessEntityID and Department columns.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-164.php