Sample Header Ad - 728x90

ORDER column by numeric order not alphabet

0 votes
2 answers
99 views
i am stuck with an exercise. using WideWorldImporters DataBase I need to show my results this way: enter image description here 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