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

Question

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

Sample data and desired results would really help.

Gordon Linoff
– Gordon Linoff

2019-10-19 15:37:13 +00:00
Commented Oct 19, 2019 at 15:37 — Gordon Linoff
– Gordon Linoff, Commented Oct 19, 2019 at 15:37
Hey @GordonLinoff i have edited my question will that help

Amit Bisht
– Amit Bisht

2019-10-19 15:53:16 +00:00
Commented Oct 19, 2019 at 15:53 — Amit Bisht
– Amit Bisht, Commented Oct 19, 2019 at 15:53

Himanshu · Accepted Answer · 2019-10-19 15:53:09Z

1

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)

answered Oct 19, 2019 at 15:53

Himanshu

3,9902 gold badges16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amit Bisht Over a year ago

Thanks Man, Just a little bit tweak and it is working fine for me Thank you

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related