mysql counting results between two datetime

Question

Hey All,

Really hope this can be done but I have come across a problem I need to resolve.

I have an Inventory booking system for items. Each item gets booked in and out by hours. So some may go in and out within a few hours while others may be days.

So in my column I have 2 datetime fields out | in. But I may have multiple items so in theory I may have 10 widgets to be booked out.

So lets say the next employee comes in and wants to book out another widget. I need a way to check that widget has available ones for the time they want to book out their widget. So the system would need to loop through the table and see how many widgets are booked out for the time they are asking for.

So ie 6 out of the 10 are booked out so we can allow another widget to be booked out. In other words I need to be able to count how many widgets are booked out during the time the employee wants to book his out.

I may have made this sound to long winded sorry but hope someone can advise.

Thank you in advance if you can.

Answer 1

Assuming you have a table that stores the quantity of each type of item as well as the booking table then you can compare the total quantity with the number of that type of items that have been booked out (but not back in again yet).

When booking out an item store a row in the booking table with item_type, out_time and in_time = NULL. When booking an item back in then store the in_time. You can search for items of the requested type and where in_time IS NULL.

Answer 2

This will find all the widgets that were checked in before you needed them, and won't be checkout out again until after you need them.

SELECT COUNT(*) FROM widgets WHERE in < $$newcheckOUTtime$$ AND out > $$newcheckINtime$$

Answer 3

So you have a table [bookings] with fields [out] and [in]. Assuming [in] is NULL for widgets that have not been returned, you can retrieve the count of booked out widgets like:

select count(*)
from [bookings]
where [in] is null