Ordering records by CASE statement in SQL Server

elvisidrizi1
 
on Jul 15, 2022 10:54 PM
260 Views

Hello Everyone,

I have a bunch of records in my dataset that have a DateTime column and a value and I would like to see where I had the most expenses during the 7-day week.

My records contain data as old as 5 years and I would like to see them during these 5 years periods where I had the most expenses.

The only issue is I do not know how to order them properly.

Begin
   SELECT
         AVG(Price) As Total,
         FORMAT(Date, 'ddd', 'en-US') AS 'Day'
    FROM
        dbo.Expenses
    WHERE
    Department = 'Clothes'
    GROUP BY
    Department,
   FORMAT(Date, 'ddd', 'en-US')
   ORDER BY  FORMAT(Date, 'ddd', 'en-US') asc
End

And this is the result that I get :

130.936666  Fri
79.517500   Mon
100.457142  Sat
31.910000   Sun
42.567692   Thu
57.246923   Tue
60.650000   Wed

Is there a way that I can group them properly, starting from Monday?

Any Help is much appreciated 😊

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 18, 2022 03:46 AM

Hi elvisidrizi1,

Use CASE stetement in the ORDER BY clause.

SQL

SELECT AVG(Price) As Total,
       FORMAT(Date, 'ddd', 'en-US') AS 'Day'
FROM dbo.Expenses
WHERE Department = 'Clothes'
GROUP BY Department,
         FORMAT(Date, 'ddd', 'en-US')
ORDER BY 
CASE WHEN FORMAT(Date, 'ddd', 'en-US') = 'Mon' THEN 0
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Tue' THEN 1
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Wed' THEN 2
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Thu' THEN 3
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Fri' THEN 4
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Sat' THEN 5
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Sun' THEN 6
END ASC