SHARE
TWEET

Untitled

a guest Mar 20th, 2017 60 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE @AllData TABLE =
  2. (
  3.     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
  4.     FROM Employees, Orders, [Order Details], Products
  5.     WHERE
  6.         Employees.EmployeeID = Orders.EmployeeID AND
  7.         Orders.OrderID = [Order Details].OrderID AND
  8.         [Order Details].ProductID = Products.ProductID
  9. )
  10.  
  11. DECLARE @MaxCount int = SELECT MAX(Data.Counter) FROM @AllData as Data
  12. DECLARE @MaxPrice real = SELECT MAX(Data.Pricer) FROM @AllData as Data
  13. DECLARE @MinCount int = SELECT MIN(Data.Counter) FROM @AllData as Data
  14. DECLARE @MinPrice real = SELECT MIN(Data.Pricer) FROM @AllData as Data
  15.  
  16. SELECT Data.Employee, [Best COUNT], @MaxCount FROM @AllData as Data WHERE Data.Counter = @MaxCount
  17. UNION
  18. SELECT Data.Employee, [Best REVENUE], @MaxPrice FROM @AllData as Data WHERE Data.Pricer = @MaxPrice
  19. UNION
  20. SELECT Data.Employee, [Worst COUNT], @MinCount FROM @AllData as Data WHERE Data.Pricer = @MinCount
  21. UNION
  22. SELECT Data.Employee, [Worst REVENUE], @MinPrice FROM @AllData as Data WHERE Data.Pricer = @MinPrice
RAW Paste Data
Top