w3resource

AdventureWorks Database: Calculate sales targets per month for salespeople

SQL Query - AdventureWorks: Exercise-140 with Solution

140. From the following tables write a query in SQL to calculate sales targets per month for salespeople.

Sample table: Sales.SalesPerson


Click to view Full table

Sample table: HumanResources.Employee


Click to view Full table

Sample table: Person.Person


Click to view Full table

Sample Solution:

SELECT s.BusinessEntityID AS SalesPersonID, FirstName, LastName, SalesQuota, SalesQuota/12 AS "Sales Target Per Month" 
FROM Sales.SalesPerson AS s   
JOIN HumanResources.Employee AS e   
    ON s.BusinessEntityID = e.BusinessEntityID  
JOIN Person.Person AS p   
    ON e.BusinessEntityID = p.BusinessEntityID;

Sample Output:

salespersonid|firstname|lastname         |salesquota|Sales Target Per Month|
-------------+---------+-----------------+----------+----------------------+
          274|Stephen  |Jiang            |          |                      |
          275|Michael  |Blythe           |    300000|    25000.000000000000|
          276|Linda    |Mitchell         |    250000|    20833.333333333333|
          277|Jillian  |Carson           |    250000|    20833.333333333333|
          278|Garrett  |Vargas           |    250000|    20833.333333333333|
          279|Tsvi     |Reiter           |    300000|    25000.000000000000|
          280|Pamela   |Ansman-Wolfe     |    250000|    20833.333333333333|
          281|Shu      |Ito              |    250000|    20833.333333333333|
          282|José     |Saraiva          |    250000|    20833.333333333333|
          283|David    |Campbell         |    250000|    20833.333333333333|
          284|Tete     |Mensa-Annan      |    300000|    25000.000000000000|
          285|Syed     |Abbas            |          |                      |
          286|Lynn     |Tsoflias         |    250000|    20833.333333333333|
          287|Amy      |Alberts          |          |                      |
          288|Rachel   |Valdez           |    250000|    20833.333333333333|
          289|Jae      |Pak              |    250000|    20833.333333333333|
          290|Ranjit   |Varkey Chudukatil|    250000|    20833.333333333333|

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Tax difference between highest and lowest-taxed state or province.
Next: Find unit price, ID number, and modulus of division.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.