-- Tellimuste arv aastate ja kuude lõikes, Võrlus
WITH TABEL AS (
SELECT Year(OrderDate) as Aasta, Month(OrderDate) as Kuu, COUNT(*) TellimusteArv
FROM Orders
GROUP BY Year(OrderDate), Month(OrderDate))
SELECT *,
-- -LAG(TellimusteArv,1,0) OVER (ORDER BY Aasta,kuu)+TellimusteArv as Vahe,
CASE SIGN(-LAG(TellimusteArv,1,0) OVER (ORDER BY Aasta,kuu)+TellimusteArv)
WHEN -1 THEN '-'
WHEN 1 THEN '+'
WHEN 0 THen ''
END as Vahe
FROM Tabel
WITH TABEL AS (
SELECT Year(OrderDate) as Aasta, COUNT(*) TellimusteArv
FROM Orders
GROUP BY Year(OrderDate))
SELECT *,
LEAD(TellimusteArv,1,0) OVER (ORDER BY Aasta),
LEAD(TellimusteArv,1,0) OVER (ORDER BY Aasta)-TellimusteArv as Vahe,
CASE SIGN(LEAD(TellimusteArv,1,0) OVER (ORDER BY Aasta)-TellimusteArv)
WHEN -1 THEN '-'
WHEN 1 THEN '+'
WHEN 0 THen ''
END as Vahe2
FROM Tabel
--Tellimuste summad
WITH Tabel AS (
SELECT O.OrderID, ORDERDate, YEAR(OrderDate) AS Aasta, MONTH(OrderDate) as Kuu,CustomerID,
SUM(UnitPrice*Quantity*(1-Discount) ) as Summa
FROM Orders AS O INNER JOIN [Order Details] D ON O.OrderID=D.OrderID
GROUP BY O.OrderID, ORDERDate, YEAR(OrderDate), MONTH(OrderDate) ,CustomerID
)
SELECT Aasta, ROUND(SUM(Summa),2) as Summa,
CASE SIGN(-LAG(ROUND(SUM(Summa),2),1,0) OVER (ORDER BY Aasta) + ROUND(SUM(Summa),2))
WHEN 1 THEN '+'
WHEN -1 THEN '-'
END
FROM Tabel
GROUP BY aasta