SELECT [Name]
     , CASE
            WHEN DATEPART(hour,[Start]) >= 0 AND DATEPART(hour,[Start]) < 12 THEN 'Morning'
            WHEN DATEPART(hour, [Start]) >= 12 AND DATEPART(hour,[Start]) < 18 THEN 'Afternoon'
            WHEN DATEPART(hour, [Start]) >= 18 AND DATEPART(hour,[Start]) < 24 THEN 'Evening'
       END AS 'Part of the Day'
     , Case
            WHEN [Duration] <= 3 THEN 'Extra Short'
            WHEN [Duration] >= 4 AND [Duration] <= 6 THEN 'Short'
            WHEN [Duration] > 6 THEN 'Long'
            ELSE 'Extra Long'
       END AS 'Duration'

  FROM Games
ORDER BY [Name],

         (Case
             WHEN [Duration] <= 3 THEN 1
             WHEN [Duration] >= 4 AND [Duration] <= 6 THEN 2
             WHEN [Duration] > 6 THEN 3
             ELSE 4
          END),
          (CASE
             WHEN DATEPART(hour,[Start]) >= 0 AND DATEPART(hour,[Start]) < 12 THEN 1
             WHEN DATEPART(hour, [Start]) >= 12 AND DATEPART(hour,[Start]) < 18 THEN 2
             WHEN DATEPART(hour, [Start]) >= 18 AND DATEPART(hour,[Start]) < 24 THEN 3
           END)