--Tellimuste arv aastate lõikes
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
SET @cols = STUFF(
(SELECT DISTINCT ',' + QUOTENAME(CAST(YEAR(OrderDate) AS VARCHAR(4)))
FROM [dbo].[Orders] FOR XML PATH ('')) ,1,1,'')
SET @query = 'SELECT ''Orders'','+ @cols + '
FROM (
SELECT [OrderID], YEAR([OrderDate]) as Aasta
FROM Orders) as Andmed
PIVOT (Count(OrderID) For Aasta in('+@cols+')
) as Tabel'
EXECUTE (@query)
--Tellitud kaupade arvud aastate lõikes
SELECT Productname, [1996],[1997],[1998] --päis
FROM (
SELECT ProductName, Quantity , year(OrderDate) as Aasta --andmed
FROM Products P Left JOIN [Order Details] O
ON P.ProductID=O.ProductID LEFT JOIN Orders Ord ON O.OrderID=Ord.OrderID) as Andmed
PIVOT (SUM(Quantity) FOR Aasta IN ([1996],[1997],[1998])
)AS Tabel
--kümme mitte populaarsemat kaupa
WITH Tabel AS (
SELECT ROW_NUMBER() OVER (ORDER BY year(OrderDate), SUM(Quantity)) as RowNr,
RANK() OVER (ORDER BY year(OrderDate), SUM(Quantity)) as Rank,
DENSE_RANK() OVER (ORDER BY year(OrderDate), SUM(Quantity)) as DenseRank,
ProductName, year(OrderDate) AS Years, SUM(Quantity) as ProductsCount --andmed
FROM Products P INNER JOIN [Order Details] O
ON P.ProductID=O.ProductID INNER JOIN Orders Ord ON O.OrderID=Ord.OrderID
GROUP BY ProductName, year(OrderDate))
SELECT *
FROM Tabel
WHERE DenseRank <= 10
--Märgistada kauba populaarsuse järgi
WITH Tabel AS (
SELECT ROW_NUMBER() OVER (ORDER BY year(OrderDate), SUM(Quantity)) as RowNr,
RANK() OVER (ORDER BY year(OrderDate), SUM(Quantity)) as Rank,
DENSE_RANK() OVER (ORDER BY year(OrderDate), SUM(Quantity)) as DenseRank,
NTILE(3) OVER (ORDER BY year(OrderDate), SUM(Quantity)) as NTile,
ProductName, year(OrderDate) AS Years, SUM(Quantity) as ProductsCount --andmed
FROM Products P INNER JOIN [Order Details] O
ON P.ProductID=O.ProductID INNER JOIN Orders Ord ON O.OrderID=Ord.OrderID
GROUP BY ProductName, year(OrderDate))
SELECT TOP 10 *,
CASE NTile -- märgistame iga jagatud kaupade rühma populaarsuse järgi
WHEN 1 THEN '-'
WHEN 2 THEN ''
WHEN 3 THEN '*'
END AS Populaarsust
FROM Tabel
ORDER BY ProductName, Years
Märgistada kõige odavad kaubad (Products-UnitPrice järgi)
SELECT TOP 10 ProductName,CategoryID, UnitPrice,
NTILE(5) OVER (ORDER BY UnitPrice) AS HinnaTase,
CASE NTILE(5) OVER (ORDER BY UnitPrice)
WHEN 1 THEN 'Odav kaup'
WHEN 5 THEN 'Kallis kaup'
ELSE ''
END AS Mark
FROM Products
ORDER BY CategoryID, ProductName
--Iga kauba kategooria kolm kõige odavama hinnaga kaubad
WITH Tabel AS (
SELECT ProductName, C.CategoryName, UnitPrice,
DENSE_RANK() OVER (PARTITION BY CategoryName Order BY UnitPrice) as PriceNr
FROM Products P INNER JOIN Categories C ON C.CategoryID=P.CategoryID )
SELECT *
FROM Tabel
WHERE PriceNr <=3
--Iga kategooria kõige odavad kaubad on tärni(de)ga mõrgistatud
WITH Tabel AS (
SELECT ProductName, C.CategoryName, UnitPrice,
DENSE_RANK() OVER (PARTITION BY CategoryName Order BY UnitPrice) as PriceNr
FROM Products P INNER JOIN Categories C ON C.CategoryID=P.CategoryID )
SELECT TOP 10 ProductName + CASE PriceNr
WHEN 1 THEN ' **' -- Kõige odavad
WHEN 2 THEN ' *' -- Ka odavad, on teisel kohal
END as ProductName
,CategoryName, UnitPrice
FROM Tabel
WHERE PriceNr <=3
--viis kõige populaarsemat kaupa (müüdud arvu järgi) aastate lõikes
WITH Tabel AS (
SELECT
DENSE_RANK() OVER (PARTITION BY YEAR(OrderDate) ORDER BY SUM(Quantity) DESC) as DenseRank,
ProductName, year(OrderDate) AS Years --, SUM(Quantity) as ProductsCount --andmed
FROM Products P INNER JOIN [Order Details] O
ON P.ProductID=O.ProductID INNER JOIN Orders Ord ON O.OrderID=Ord.OrderID
GROUP BY ProductName, year(OrderDate))
SELECT *
FROM Tabel
WHERE DenseRank <= 5