Hi All,
I’m hoping someone can help me out with the SQL query to filter a dataset.
We display our classroom timetables on our screens around school and as from next week we would like to use Xibo to do this.
I am so close to this working how we want it to, it’s just the filter query that needs tweaking.
A staff member uses a CSV to plan certain classrooms a week or 2 in advance. She imports this CSV on a Friday evening for the following week.
The Dataset (Classroom Timetable) contains columns such as:
Day (Monday, Tuesday, Wednesday etc.)
Period (1, 2, 3, 4, 5, Lunch etc.)
Subject
Year Group (7, 8, 9 etc.)
Teacher
Room
Start (08:50, 09:50, 11:10)
End (09:50, 10:50, 12:10)
My problem is I can’t get the SQL query right to only display the rooms during the current period.
I’m trying to achieve something along the lines, show only the current day, and where the time now is between the Start and End column values.
I have referenced another community thread asking about this (Help with SQL filter query ticker data set) but still no joy.
I managed to get the Day filter sorted with previous assistance, so my current filter looks like:
Day=DAYNAME(NOW()) AND NOW() > Start AND NOW() < End
I have also tried:
Day=DAYNAME(NOW()) AND TIME() > Start AND TIME() < End
Day=DAYNAME(NOW()) AND TIME(NOW()) > Start AND TIME(NOW()) < End
Day=DAYNAME(NOW()) AND DATE_FORMAT(NOW(), ‘%T’) > Start AND DATE_FORMAT(NOW(), ‘%T’) < End
The Start and End columns in the dataset are set as String-Value as String-Date forces you to have to put the date in the column which will be too annoying to the staff to use.
Any ideas as to how using a String-Value column with just 4 digit time code in I can filter on this?
I hope that makes sense, any questions please ask. Help greatly appreciated 
Thanks,
Lee.