Sql check a value in column is greater than number of rows

i am building a web app where customer can book a service from a business(vendor). but first vendor need to add what services he provide, where he able to deliver those services, and from which time (start time) to which time(end time) how many users is available

so i am confusing that suppose a customer book a service for some particular time and when another customer book a service from that vendor we need to calculate is the number of users available in this time period is greater than count of already booking (which are not completed yet) in that particular time

i have these three table

users = (for both customer & vendors)

id | name | address | role_id 
1 | Customer | some address | 1
2 | Vendor | Some Address | 2
3 | Another Customer | Some address | 1
4 | Another Vendor | address | 2

vendor_available_time = (only for vendor)

id | date_start_time | date_end_time | no_of_users |vendor_id
1 | 2019-10-16 00:00:00 | 2019-19-16 23:59:59 | 3 | 2
1 | 2019-10-16 09:00:00 | 2019-19-16 17:00:00 | 1 | 4
1 | 2019-10-17 00:00:00 | 2019-19-17 23:59:59 | 3 | 2
1 | 2019-10-17 09:00:00 | 2019-19-17 17:00:00 | 2 | 4

bookings = (for both)
id | booking_status | booking_datetime | customer_id | vendor_id 
1 | job started | 2019-10-16 10:00:00 | 1 | 2 
1 | completed | 2019-10-15 10:00:00 | 1 | 2
1 | accepted  | 2019-10-16 09:00:00 | 3 | 2
1 | work in progress  | 2019-10-16 09:00:00 | 3 | 2

please help me with a query

where i can get all vendors whose no_of_users is greater than count(bookings) where booking_datetime >= date_start_time and booking_datetime < date_end_time and booking_status is not in [‘completed’, ‘rejected’, ‘cancelled’]

Any suggestion will be really appreciated can i get those result from one query, should i use multiple queries or should i change my database structure

The output i want is just a user_ids whose are available for that time period and whose no of users is greater than already count of bookings for that time

Answer

Is this what you want?

       Select  b.vendor_id from 
       bookings b join vendor_available_time v on b.vendor_id=v.id
      where b.booking_datetime >= v.date_start_time and 
       b.booking_datetime < v.date_end_time 
       and b.booking_status not in ['completed', 'rejected', 'cancelled']

     group by b.vendor_id having count(distinct b.id) <=count(b.customer_id)

Advertisement

Answer