Help with a sql statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a sql statement

Author

Topic

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-30 : 11:43:58

Hi I am trying to figure out how to create this sql statement.
"Which employee had the highest percentage increase in sales from 2001 to 2002?"

Employee table
EmployeeID
TaxpayerID
LastName
FirstName
HomePhone

Bicycle Table
EmployeeID
SerialNumber
CustomerID
ShipDate
ShipEmployee
SalePrice
SalesTax

This is what I started to do.
Select *
From Bicycle b
Join Employee c
on b.EmployeeID = c.EmployeeID

The shipdate is the when the bicycle is sold.

Thanks for any help you can give.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 12:54:12

[code]SELECT TOP 1 e.*
FROM Employee e
JOIN (SELECT EmployeeID,
(SUM(CASE WHEN YEAR(ShipDate)=2002 THEN SalePrice ELSE 0 END)-SUM(CASE WHEN YEAR(ShipDate)=2001 THEN SalePrice ELSE 0 END))*100.0/SUM(CASE WHEN YEAR(ShipDate)=2001 THEN SalePrice ELSE 0 END) AS [%Inc]
FROM Bicycle
WHERE YEAR(ShipDate) IN (2001,2002)
GROUP BY EmployeeID
)b
ON e.EmployeeID=b.EmployeeID
ORDER BY b.[%Inc] DESC
[/code]