Thanks to ruvo80's post in
http://www.radiodj.ro/community/index.php?topic=9626.msg55642.
His post [using CURDATE()] has guided me to solve my previous question, though in a longer way. I am sharing the solution here for SQL experts to improve on it, or make a shorter version of it:
#LOVE SONGS English
SELECT * FROM (
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 1 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
WHERE start_date <= NOW()
AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
AND song_type = 0
AND enabled = 1
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
AND `path` LIKE '%Love Songs English%'
AND `count_played`<5
AND DAYNAME(CURDATE()) = 'Monday'
AND `weight` >90
AND queuelist.artist IS NULL
AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)
UNION
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 2 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
WHERE start_date <= NOW()
AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
AND song_type = 0
AND enabled = 1
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
AND `path` LIKE '%Love Songs English%'
AND `count_played`<5
AND DAYNAME(CURDATE()) = 'Tuesday'
AND `weight` BETWEEN 70 AND 80
AND queuelist.artist IS NULL
AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)
UNION
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 3 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
WHERE start_date <= NOW()
AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
AND song_type = 0
AND enabled = 1
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
AND `path` LIKE '%Love Songs English%'
AND `count_played`<5
AND DAYNAME(CURDATE()) = 'Wednesday'
AND `weight` BETWEEN 51 AND 60
AND queuelist.artist IS NULL
AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)
UNION
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 4 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
WHERE start_date <= NOW()
AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
AND song_type = 0
AND enabled = 1
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
AND `path` LIKE '%Love Songs English%'
AND `count_played`<5
AND DAYNAME(CURDATE()) = 'Thursday'
AND `weight` BETWEEN 60 AND 70
AND queuelist.artist IS NULL
AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)
UNION
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 5 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
WHERE start_date <= NOW()
AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
AND song_type = 0
AND enabled = 1
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
AND `path` LIKE '%Love Songs English%'
AND `count_played`<5
AND DAYNAME(CURDATE()) = 'Friday'
AND `weight` BETWEEN 75 AND 95
AND queuelist.artist IS NULL
AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)
UNION
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 6 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
WHERE start_date <= NOW()
AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
AND song_type = 0
AND enabled = 1
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
AND `path` LIKE '%Love Songs English%'
AND `count_played`<5
AND DAYNAME(CURDATE()) = 'Saturday' OR 'Sunday'
AND `weight` =50
AND queuelist.artist IS NULL
AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)
UNION
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 7 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
WHERE start_date <= NOW()
AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
AND song_type = 0
AND enabled = 1
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 400
AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
AND `path` LIKE '%Love Songs English %'
AND `count_played`<3
AND `weight` = 50
AND queuelist.artist IS NULL
AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY `date_played` ASC, RAND()
LIMIT 2)
)
temp
ORDER BY `preference`
LIMIT 2
***ORIGINAL POST***
Even in SQL with UNION, the songs seem to repeat everyday, unless you really stretch the duration of the SongTitle separation. How about if a CASE scenario is added to concentrate on certain range of 'weight' (called priority) everyday without having to create an event for each day of the weekdays. What I would like to achieve is: if Monday, select songs based on all the criteria listed, especially with weight "so and so"; if Tuesday, with weight "so on and so forth", etc.
Here is the code I am working on to make it work (please help me on the separated section in the middle):
Note: Without the separated section, the SQL should work. But if I have a show that repeats Monday to Friday, I do not want to write an event for each day, if it can be automated by basing it on the day name (e.g., Monday, Tuesday, etc.) of the date on the computer at the time of play.
SELECT * FROM (
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 1 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
WHERE start_date <= NOW()
AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
AND song_type = 0
AND enabled = 1
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 500
AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
AND `path` LIKE '%Love Songs English %'
AND `count_played`<5
AND SELECT DAYNAME(NOW())
CASE WHEN 'weekday' = Monday THEN
AND `weight` > 90
WHEN 'weekday' = Tuesday THEN
AND `weight` BETWEEN 70 AND 80
WHEN 'weekday' = Wednesday THEN
AND `weight` BETWEEN 51 AND 60
WHEN 'weekday' = Thursday THEN
AND `weight` BETWEEN 60 AND 70
WHEN 'weekday' = Friday THEN
AND `weight` BETWEEN 75 AND 95
ELSE 'weekday' = Saturday OR Sunday
END AS `weight` = 50
AND queuelist.artist IS NULL
AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)
UNION
(SELECT songs.ID, songs.artist, songs.title, songs.weight, songs.count_played, songs.date_played, 'no' AS `from_requests`, 2 AS `preference` FROM songs
LEFT JOIN queuelist ON (songs.artist = queuelist.artist OR songs.ID = queuelist.songID)
WHERE start_date <= NOW()
AND (end_date >= NOW() OR end_date = '2002-01-01 00:00:01')
AND song_type = 0
AND enabled = 1
AND TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 400
AND TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 30
AND `path` LIKE '%Love Songs English %'
AND `count_played`<3
AND `weight` = 50
AND queuelist.artist IS NULL
AND queuelist.songID IS NULL
GROUP BY `artist`
HAVING COUNT(*) <2
ORDER BY RAND()
LIMIT 2)
)
temp
ORDER BY `preference`
LIMIT 2
[/color]
Thanks again!
Topic: Adding RANDOM in Tandem with DAY of Week in SELECTING Songs (Read 534 times)
