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.