I would like to ask some assistance in terms of formulating my query so that I can achieve the expected output stated below. I tried so many times to achieve the expected output but I’m always getting the actual output that is also depicted below. I’m using MySQL as my RDBMS. By the way if this isn’t possible by using only a query, please let me know so that I can inform my team. Thank you
The ordering rule is all about displaying in sequence each set of time period of each date. For example, September 23 – 26 [Display the first set of time periods] -> next display: September 23 – 26 [Display second set of time periods, if any] -> and so on.
| Date | Start_Time | End_Time |September 23, 2020 | 8:00 AM | 9:00 AM |September 24, 2020 | 9:00 AM | 10:00 AM |September 25, 2020 | 8:00 AM | 9:00 AM |September 26, 2020 | 9:00 AM | 10:30 AM |September 23, 2020 | 9:00 AM | 10:00 AM
Select DISTINCT timeslot.Timeslot_ID, timeslot.Date,timeslot.Start_Time,timeslot.End_Time from timeslot WHERE timeslot.ExamEvent_ID=1 ORDER BY STR_TO_DATE (CONCAT(timeslot.Start_Time,' - ',timeslot.End_Time), '%l:%i %p - %l:%i %p'), STR_TO_DATE (timeslot.Date, '%M %e, %Y')
ROW_NUMBER() window function:
SELECT Timeslot_ID, ExamEvent_ID, Date, Start_Time, End_Time FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Date ORDER BY STR_TO_DATE(Start_Time, '%l:%i %p')) rn FROM timeslot ) t ORDER BY rn, STR_TO_DATE(Date, '%M %e, %Y')
See the demo.