MSSQL – Sort By Day Of Week

This script utilizes this Format Date Function to sort results based on a day of the week.

The case statement in this query is easy to update if your region has a different day for the start of the week.

This particular script is part of a metric that I provide for calls that come in for given marketing campaign phone number.

SELECT dbo.fnFormatDate(CallStart, 'DDDD', NULL) AS DayOfWeek
	,Count(PhoneNumber) AS TotalCalls
FROM tblMetricsCalls
WHERE PhoneNumber = '<Phone Number>'
GROUP BY PhoneNumber
	,dbo.fnFormatDate(CallStart, 'DDDD', NULL)
ORDER BY CASE
		WHEN dbo.fnFormatDate(CallStart, 'DDDD', NULL) = 'Sunday'
			THEN 1
		WHEN dbo.fnFormatDate(CallStart, 'DDDD', NULL) = 'Monday'
			THEN 2
		WHEN dbo.fnFormatDate(CallStart, 'DDDD', NULL) = 'Tuesday'
			THEN 3
		WHEN dbo.fnFormatDate(CallStart, 'DDDD', NULL) = 'Wednesday'
			THEN 4
		WHEN dbo.fnFormatDate(CallStart, 'DDDD', NULL) = 'Thursday'
			THEN 5
		WHEN dbo.fnFormatDate(CallStart, 'DDDD', NULL) = 'Friday'
			THEN 6
		ELSE 7
		END