Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @AllData TABLE =
- (
- SELECT Employees.EmployeeFirstName+Employees.EmployeeLastName as Employee, COUNT([Order Details].OrderID) as Counter, SUM([Order Details].UnitPrice*[Order Details].Discount*[Order Details].Quantity) as Pricer
- FROM Employees, Orders, [Order Details], Products
- WHERE
- Employees.EmployeeID = Orders.EmployeeID AND
- Orders.OrderID = [Order Details].OrderID AND
- [Order Details].ProductID = Products.ProductID
- )
- DECLARE @MaxCount int = SELECT MAX(Data.Counter) FROM @AllData as Data
- DECLARE @MaxPrice real = SELECT MAX(Data.Pricer) FROM @AllData as Data
- DECLARE @MinCount int = SELECT MIN(Data.Counter) FROM @AllData as Data
- DECLARE @MinPrice real = SELECT MIN(Data.Pricer) FROM @AllData as Data
- SELECT Data.Employee, [Best COUNT], @MaxCount FROM @AllData as Data WHERE Data.Counter = @MaxCount
- UNION
- SELECT Data.Employee, [Best REVENUE], @MaxPrice FROM @AllData as Data WHERE Data.Pricer = @MaxPrice
- UNION
- SELECT Data.Employee, [Worst COUNT], @MinCount FROM @AllData as Data WHERE Data.Pricer = @MinCount
- UNION
- SELECT Data.Employee, [Worst REVENUE], @MinPrice FROM @AllData as Data WHERE Data.Pricer = @MinPrice
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement