Dataset Sorting by day of week

[mattgrimley]

To be completed by the original poster:

CMS Version

3.0.5

Player Type

Windows 10 client / Windows 2019 Host Server (manual install)

Player Version

3.0.5

Issue

I have a simple dataset with a column called DayofWeek which allows selection of Monday through Friday (as we have events which recur, recording the day rather than a date makes it more usable).

I can pull the data from the dataset, but i would like to sort the displayed data by “day of week” beginning with whatever the day is today.

I feel like I should be able to do this using DAYNAME(CURDATE()) and DAYNAME(CURDATE()+1) etc, but i can’t seem to get it to work. This would sort by whatever today’s dayname is and then add one for each subsequent +1.

e.g:
ORDER BY FIELD(DayofWeek, DAYNAME(CURDATE()), DAYNAME(CURDATE() +1), DAYNAME(CURDATE() + 2), DAYNAME(CURDATE() +3), DAYNAME(CURDATE()+4), DAYNAME(CURDATE()+5), DAYNAME(CURDATE()+6))

Frankly, i can’t even get a manual list of strings to work as nothing appears in my data, so i might have the syntax completely wrong

I would have expected this to force a display order of M through F (although my plan above was to adjust the start day to today).
ORDER BY FIELD(DayofWeek, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')

Any thoughts or guidance?

FYI my filter (which works fine looks like this):
WHERE DayofWeek = DAYNAME(CURDATE()) OR DayofWeek = DAYNAME(CURDATE() + 1) OR DayofWeek = DAYNAME(CURDATE() + 2) OR DayofWeek = DAYNAME(CURDATE() + 3) OR DayofWeek = DAYNAME(CURDATE() + 4) OR DAYNAME(CURDATE() + 5) OR DayofWeek = DAYNAME(CURDATE() + 6)

[mattgrimley]

Scratch this question, i solved it by adding a column to the dataset with a formula:

CASE WHEN DayofWeek = DAYNAME(CURDATE()) THEN 1 WHEN DayofWeek = DAYNAME(CURDATE() + 1) THEN 2 WHEN DayofWeek = DAYNAME(CURDATE() + 2) THEN 3 WHEN DayofWeek = DAYNAME(CURDATE() + 3) THEN 4 WHEN DayofWeek = DAYNAME(CURDATE() + 4) THEN 5 WHEN DayofWeek = DAYNAME(CURDATE() + 5) THEN 6 WHEN DayofWeek = DAYNAME(CURDATE() + 6) THEN 7 ELSE 0 END

This assigns a dynamic number for each day of the week in the DayofWeek table based on the current day. So, today is 1, tomrrow is 2 etc. I can then ORDER by this new column in the dataset.

Self-fixed!