daily pastebin goal
67%
SHARE
TWEET

Untitled

a guest Mar 13th, 2018 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3. -- zajecia
  4. select
  5. src2.Kategoria,
  6. src2.Podkategoria,
  7. LineTotal,
  8. src2.liczbaSprzedanychProd,
  9. ssss,
  10. DENSE_RANK() over (order by ssss desc)
  11. --srednia,
  12. --ilosc,
  13. --RANK() over (partition by src2.srednia order by src2.srednia) "sredniaNaTransakcje"
  14.  from (
  15.     select
  16.     Kategoria,
  17.     Podkategoria,
  18.     LineTotal,
  19.     liczbaSprzedanychProd,
  20.     ssss
  21.     --sumaProdWtransakcji,
  22.     --avg(sw) over (partition by Kategoria, Podkategoria order by Kategoria, Podkategoria ) "sred"
  23.     --ilosc,
  24.     --sumaProdWtransakcji/ilosc "srednia"
  25.     from (
  26.         select
  27.                 cat.Name "Kategoria",
  28.                 sub.Name "Podkategoria",
  29.                 sum(sod.LineTotal) over (partition by cat.Name,sub.Name order by cat.Name,sub.Name ) "LineTotal",
  30.                 count(sod.ProductID) over (partition by cat.Name,sub.Name order by cat.Name,sub.Name ) "liczbaSprzedanychProd",
  31.                 avg(sod.OrderQty) over (partition by sod.SalesOrderDetailID) "ssss",
  32.                 --sum(sod.OrderQty) over (partition by cat.Name,sub.Name order by cat.Name,sub.Name ) "sumaProdWtransakcji",
  33.                 --sum(sod.OrderQty) over(partition by soh.SalesOrderID order by soh.SalesOrderID ) "sumaWszystkichTransakcji",
  34.                 --sum(sod.OrderQty) over(partition by soh.SalesOrderID order by soh.SalesOrderID ) sw,
  35.                 ROW_NUMBER() over (partition by cat.Name,sub.Name order by cat.Name,sub.Name ) RowNumber
  36.             from
  37.                 (select * from AdventureWorks2014.Sales.SalesOrderDetail) sod join
  38.                 (select ProductSubcategoryID, ListPrice, ProductID from AdventureWorks2014.Production.Product) prod on sod.ProductID=prod.ProductID join
  39.                 (select ProductCategoryID, ProductSubcategoryID, Name from AdventureWorks2014.Production.ProductSubcategory) sub on prod.ProductSubcategoryID=sub.ProductSubcategoryID join
  40.                 (select ProductCategoryID, Name from AdventureWorks2014.Production.ProductCategory) cat on sub.ProductCategoryID=cat.ProductCategoryID
  41.     ) src
  42.     where src.RowNumber = 1
  43. ) src2
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
 
Top