select all data before a specify service ID

Good morning !

I have create this fiddle for you:
https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=4e2bdf9aecca8b69d829770589c14807

Explanation:

  1. Create a table “timeslots”
  2. Fill this table with a date range from tomorrow to + 2 days , time range. 14-18 o’clock.
  3. Update 3 times lots and set service to “2” (I chose random IDs)
  4. SELECT * FROM timeslots ORDER BY timeslot ASC

And this is the result, which shows the fiddle.
But now I need to expand my sql query to get a specific result and I don’t know how I can realize it. Maybe you have some idea.

First step:
I would like to get only the timeslots where service = 0:

Easy for me:

SELECT * FROM timeslots WHERE service = 0

A part of the result:

2021-07-06 14:00:00 0
2021-07-06 15:00:00 0
2021-07-06 16:00:00 2 (not this value, because service is not 0)
2021-07-06 17:00:00 0

All ok !

But If there exist – on the same day – a timeslot with service 2 (like the result above 2021-07-06 16:00:00) I need only the values AFTER 2021-07-06 16:00:00. In this example the result should be:

2021-07-06 17:00:00

I hope you understand my problem and you can help me !
Thank you !

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You are looking for NOT EXISTS, because you want to look at rows where not exists a service-two slot on the same day at that time or later.

select *
from timeslots ts
where not exists
(
  select null
  from timeslots ts2
  where date(ts2.timeslot) = date(ts.timeslot)
  and time(ts2.timeslot) >= time(ts2.timeslot)
  and service = 2
)
order by timeslot;

Demo: https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=6cf862d80445fcda030e2be35adb4909


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x