i am stuck with an exercise. using WideWorldImporters DataBase
I need to show my results this way:
but no matter what I do in order to get to this exact solution I can't,
because the month column is alphanumeric and not numeric.
the closest I got is this code
I would like some insights in order to solve this problem.
thanks all!!
WITH T1
AS
(
SELECT
YEAR(o.OrderDate) AS OrderYear
,MONTH(o.OrderDate) AS OrderMonth
,SUM(ol.PickedQuantity*ol.UnitPrice) AS MonthlyTotal
--,SUM(SUM(ol.PickedQuantity*ol.UnitPrice))OVER(PARTITION BY YEAR(o.OrderDate) ORDER BY MONTH(o.OrderDate)) AS CumulativeTotal
FROM Sales.OrderLines ol
JOIN Sales.Orders o
ON o.OrderID = ol.OrderID
GROUP BY YEAR(o.OrderDate),YEAR(o.OrderDate),MONTH(o.OrderDate)
)
SELECT
OrderYear
,CAST(OrderMonth AS VARCHAR) AS OrderMonth
,FORMAT(MonthlyTotal,'#,#.00') AS MonthlyTotal
,FORMAT(SUM(MonthlyTotal)OVER(PARTITION BY OrderYear ORDER BY OrderMonth), '#,#.00') AS CumulativeTotal
FROM t1
UNION ALL
SELECT
t1.OrderYear
,'Grand Total' AS OrderMonth
,FORMAT(SUM(MonthlyTotal)OVER(PARTITION BY OrderYear ORDER BY OrderMonth), '#,#.00') AS MonthlyTotal
,FORMAT(SUM(MonthlyTotal)OVER(PARTITION BY OrderYear ORDER BY OrderMonth), '#,#.00') AS CumulativeTotal
FROM t1
WHERE (t1.OrderYear = 2013 and OrderMonth = 12)
OR (t1.OrderYear = 2014 and OrderMonth = 12)
OR (t1.OrderYear = 2015 and OrderMonth = 12)
OR (t1.OrderYear = 2016 and OrderMonth = 5)
ORDER BY OrderYear

Asked by Lipaz Hagai
(9 rep)
Dec 25, 2024, 06:09 PM
Last activity: Jan 3, 2025, 04:38 AM
Last activity: Jan 3, 2025, 04:38 AM