SHARE
TWEET

Untitled

a guest Mar 24th, 2019 50 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- zad2 OK
  2. SELECT  SalesPersonID pracID,
  3.         LastName + ',' + FirstName "Nazwisko , Imię",
  4.         AVG(SubTotal) "Średnia psrzedaż",
  5.         ROW_NUMBER() OVER (ORDER BY AVG(SubTotal) DESC) AS Ranking,
  6.         CASE (NTILE(2) OVER(ORDER BY AVG(SubTotal) DESC))
  7.             WHEN 1 THEN 'Wyróżniony'
  8.             WHEN 2 THEN 'Do weryfikacji'
  9.         END "Grupa"  
  10. FROM    Sales.SalesOrderHeader JOIN Person.Person ON SalesPersonID = BusinessEntityID
  11. GROUP BY SalesPersonID, LastName , FirstName
  12. ORDER BY 3 DESC, 2, 1;
  13.  
  14. -- zad3 OK
  15. SELECT  YEAR(OrderDate) Rok,
  16.         MONTH(OrderDate) Miesiąc,
  17.         DAY(OrderDate) Dzień,
  18.         SUM(Sales.SalesOrderHeader.SubTotal) Kwota,
  19.         COUNT(*) "Liczba zamówień"
  20. FROM    Sales.SalesOrderHeader
  21. GROUP BY ROLLUP(YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate))
  22. HAVING YEAR(OrderDate) IS NOT NULL AND MONTH(OrderDate) IS NOT NULL AND DAY(OrderDate) IS NOT NULL
  23. ORDER BY 1, 2, 3
  24.  
  25. -- zad4 OK
  26. SELECT  SalesPersonID pracID,
  27.         CustomerID klientID,
  28.         ROUND(SUM(SubTotal),2) Suma
  29. FROM    Sales.SalesOrderHeader
  30. GROUP BY ROLLUP(SalesPersonID, CustomerID)
  31. ORDER BY 1, 2, 3 DESC
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top