|
|
The mission is to to find best and youngest sales
person for year 1999.
1. Calculate sales for 1999 grouped by Sales person. Selection is conditioned
by Department number 5 (Sales department). JOIN between Employees table and
Invoice is made by SlpCode field in Invoices:
SELECT b.EmpNo, SUM(a.DocTotal) AS Tot1999
FROM Invoice AS a INNER JOIN Employees AS b
ON a.SlpCode = b.EmpNo
WHERE b.DeptNo=5 AND Year(a.DocDate)=1999
GROUP BY b.EmpNo;
|
2. Calculate Age-To-Money proportion for every person using following formula:
[Age-To-Money proportion] = [1999-YearTotal] / [Age]
SELECT RTrim(c.FirstName) + ' ' + RTrim(c.LastName)
AS EmpName, c.Age, c.AgeToMoney
FROM
(
SELECT a.EmpNo, a.FirstName, a.LastName, a.DateOfBirth,
DateDiff(yyyy, a.DateOfBirth,GetDate()) AS Age,
b.Tot1999, (b.Tot1999/DateDiff(yyyy, a.DateOfBirth,GetDate()))
AS AgeToMoney
FROM Employees AS a INNER JOIN
(SELECT b.EmpNo, SUM(a.DocTotal) AS Tot1999
FROM Invoice AS a INNER JOIN Employees AS b
ON a.SlpCode = b.EmpNo
WHERE b.DeptNo=5 AND Year(a.DocDate)=1999
GROUP BY b.EmpNo) AS b
ON a.EmpNo = b.EmpNo
) AS c
ORDER BY c.AgeToMoney DESC
|
The results says that Aiva Pesh apparently deserves fat bonus for 1999 results:
| EmpName | Age | AgeToMoney |
| aiva pesh | 22 | 1050.64 |
| gina sohn | 27 | 527.92 |
| dona fiber | 42 | 343.79 |
| leonardo rota | 28 | 91.96 |
|