How to calculate Availability of Products in a rental database

I've got a big relational database that is being used for a series of companies to handle their product rentals and events.

It has gotten a bit crazy and I'm sure my calculations could be improved.  Perhaps someone out there can help.

So, I've got a products table.  It is just name and description stuff.  Then I've got "inventory_items", which has the serial number/bar_code of the item itself and a warehouse_id.

ALSO.... there is a stock_count field.  Some people have things like power-strips, that they have a bar-code on the wall of the warehouse and they just scan it 4 or 5 times if they're packing up a few of them, so I can have an inventory_item with a stock_count of more than one and a single bar-code

When people make a new order, the system creates a record in the orders table.  Mostly just contact info, and the date range.  (remember, this is a rental order, so there is an estimated stock_out and stock_in date range).

When they add a product to the order, it does 2 things: first, it adds that product_id and the quantity they are requesting to an order_sub table.  Then, it adds the item_id (from inventory_items) and a quantity to a table called orders_sub_items.

So now, if we're making a new rental order for 3/1/12 - 3/14/12, then I do the following:

I add up the stock_count of all the items of each product's inventory_items, I'll get the total stock_count per product.

I extract a subset of orders that with stock-in/out dates that overlap 3/1 - 3/14 in any way.  Then, I sum up the quantities on the orders_sub_items to determine the maximum number of each item_id rented on any given day.  I then group by product_id to get the maximum amount of any given product rented on any day during that time span.

The available number of products to be rented during that period will be the total amount of stock minus the maximum number rented on any given day.

Which is fine.... until now.  Now I've got to stop with the day-grouping crap, since some people will be renting the same products several times, for just a few hours, in the same day.

This doesn't even include all the exclusions for products in the maint warehouses, on transport orders, incoming new products, products to be retired at certain dates, scheduled maint, items that are in kits (where they may have a case with 20 different things in it that they rent under one bar-code, but sometimes piece out and rent separately)

Ugh.....  I can't find any "standard" way to do this.  There's a ton of LIFO and LILO logistics stuff for sales of products, but I need to get these ones back!  So, normal logistics seem to crap out on me.

Any adivce?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Any adivce?>>

 in concept, this is simply resource booking; be it a item, room, etc.

 Sounds like you've already got what you need, just need it in finer detail (time as well as date).

  Attached is a sample DB I made up sometime ago based on a reservation system I did for a campgorund once.

  I think you'll find it interesting because of two things:

1. If can handle overlaps based on date as well as time.
2. It handles sites down for maintenance.

 Look it over and we can continue if it doesn't fill the need.

DanielcmorrisAuthor Commented:
I suppose the biggest issue isn't the single unit reservations that concerns me, but the ability to determine exactly how many are left that can be rented for a given time-span.

What I need to do is figure out how many widgets are going to be sitting on the shelf for the entire time-span of a requested reservation.  

So, if I own a total of 10 widgets,
I've rented 2 of them for 3/1/12 - 3/3/12.  
I rented 4 of them for 3/2/12-3/3/12.  
I rented 1 of them for 2/23/12 - 3/31/12
I rented 3 for 3/5/12 - 3/9/12

now, I get a call from someone who wants to know how many he can rent if he needs them from 3/2/12 - 3/5/12

What I currently do is loop through the days calculating the maximum amount rented at any given time during that time period.

So,  In this case:
3/2 = 7
3/3 = 7
3/4 = 1
3/5 = 4

So, the maximum I can rent out for 3/2 - 3/5 is 3 widgets.

Now, that's time consuming from a database-level.  I've got about 600,000 products in stock over almost 100 warehouses and 50 offices and 12 companies.... and the product is just in beta.

Still.... the query runs in under 2 seconds.

However.....  now I've got a guy who's renting by the hour.  So, where I used to count the maximum number of widgets rented in a day and subtract that from the total, NOW, I need to to do it by the hour.  That means I need to do 24X more lookups.

Once this thing goes live, and we add a few more big clients, it's going to be slower.

What I really need is a way to calculate the Maximum number of items which are out of the warehouse at "any given moment" during the time-period requested.

The only way I can see to do this is by iterating through time-periods and doing lookups.

There has to be a better way.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<There has to be a better way.>>

  If you look at the DB I posted, it's really not all that bad.

  What you do is this:

1. I have X many units to rent.
2. For the time period I need the rental, I have Y number of reservations already.
3. If X is > Y, then I can rent another.

 In terms of the database I posted, it's looking at all the sites available vs existing reservations for the period I'm trying to make a reservation for.

 That's not all that different then what your trying to do.  Think about the situation where I had a campground with a single site; it's still the same formula:

  # of resources - current reservations = number available.

  Now if I had 40 identical sites, it's still the same formula

  The only difference is that instead of having 40 records in the table, you going to have a single record that has a available qty.  

  It's all done with queries and it will be fast, especially if you index it properly (start/end of the reservation).

<<However.....  now I've got a guy who's renting by the hour.  So, where I used to count the maximum number of widgets rented in a day and subtract that from the total, NOW, I need to to do it by the hour.  That means I need to do 24X more lookups.>>

  No you don't.  Don't envision the reservation representing a bunch of X'd out periods on a daily planner.

  The reservation is a range.  It starts/ends at a given date and time.

 Let's do a quick example:

Product "A" - 2 widgets to rent.

Existing reservations:

03/21 8:00 am   03/24  5:00 pm
03/22 1:00 pm   03/22 4:00 pm
03/25 8:00 am   03/25 9:00 am

I walk in the door and want to reserve the item and want to reserve from 03/20 8:00 am to 03/22 8:00 am.

So I simply SUM() the overlapping reservations, which is 1:

 2 - 1 = 1, so yes I can reserve.  Now I've got:

03/20 8:00 am   03/22 8:00 am
03/21 8:00 am   03/24  5:00 pm
03/22 1:00 pm   03/22 4:00 pm
03/25 8:00 am   03/25 9:00 am

Now someone walks in and wants to reserve from 03/22 12:00 pm to 03/26 am.  Can't do it.  The 2nd, 3rd, and 4th all overlap the start/end time that I want.

Does that help?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

DanielcmorrisAuthor Commented:
Let me just work through this:

I have 5 items.  

Rez A for 2pc   03/20 8:00 am   03/22 8:00 am
Rez B for 1pc   03/21 8:00 am   03/24  5:00 pm
Rez C for 1pc   03/22 1:00 pm   03/22 4:00 pm
Rez D for 2pc   03/25 8:00 am   03/25 9:00 am

Now someone walks in and wants to reserve 1 widget from 03/22 12:00 pm to 03/26 8am.  
I sum the overlapping reservations, which is Rez B,C & D, for a total of 4 products.  Therefore I have one available.

Looks good.  But what if I have another Reservation:  

Rez E for 1pc 03/22 5:00pm to 03/22 6:00pm

Now, that overlaps, so it would be included in the sum, but at that moment, Rez C would be back in stock, so I could easily have simply rented that same one for Rez E, meaning I actually did have one available to rent.

Look at this senario:
I have 2 widgets:

Rez A for 1pc   03/21 5:00 am   03/21 7:00 am
Rez B for 1pc   03/21 8:00 am   03/21  9:00 am
Rez C for 1pc   03/21 11:00 am   03/21 1:00 pm
Rez D for 1pc   03/21 2:00 pm   03/21 9:00 pm

Now someone walks in and wants to reserve a widget from 03/20 12:00 pm to 03/22 8am.

So I simply SUM() the overlapping reservations, which is 4:

It will say that I am already overbooked, so I can't do it..... but I actually can.  

At any given time, I never have more than one out of stock.  

See the problem?  the only way is to pick a period (hours, minutes, days, weeks, whatever) and iteratate through them, finding the maximum rented.  In this case, that would have told me that there was never a point where more than one was out of stock, and I'd have made the rental.  

If I had just summed overlapping, then my salesman would have made the booking to the customer anyway, but he'd also have made a subrental for the widget so we'd have one to give to the client during their request period.  That means we have just lost 90% of the profit as well as incurring the wrath of the owner who goes crazy when he sees a $10,000 video projector sitting on the shelf while he pays $2000 to subrent one for a week.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I see your point and I wasn't looking at this right.  What you need is a lot closer to something called time phased inventory, which is used in MRP systems to calculate available inventory.

 What would need to be done is:

1. calculate the net available at the start of the rental period.  This would be the Qty Available to Rent - reservations that overlap the start date/time.

2. Read through all reservations that have either a end or start date/time in between the start/end of the reservation being made.

3.  If reservation read is ending, net available goes up (+1)

4.  If reservation read is starting, net available goes down (-1)

5.  If net available ever  = 0, you can't take the new reservation.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Actually reading back through this, fact lot of help I was!   What I just suggested is basically what your doing now from the sound of it, just a little different.


Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<just a little different.>>

 Are you pre-calulating the net available for the days rather then doing it on the fly?  What I'm suggesting is that you do it on the fly. Then the time period of the rental doesn't matter be it days, hours, or minutes.

DanielcmorrisAuthor Commented:
I'm calculating on the fly by simply iterating through days and running a stock-lookup on products.  I thought I was going to have to start iterating through hours, but I think you've hit upon something here.  

What I had done initially was calculate by "days", where I'd check the maxumum checked-out at any given day, and then be done with it.  That way, if the order was only for 2 days, I'd only be doing 2 lookups for each product.  1 product=2 lookups

Changing to an hourly meant 1 product = 48 lookups.

If I had 20 orders or 10 orders, it was the same number of lookups, but....

By only checking the dates where inventory quantities have changed, it will be more  lookups than a daily lookup, but a million times faster than an hourly.  

I feel completely retarded.  

Next time you come out to SF, drinks are on me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.