Solved

How to calculate Availability of Products in a rental database

Posted on 2012-03-21
8
499 Views
Last Modified: 2012-03-23
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?
0
Comment
Question by:Danielcmorris
  • 5
  • 3
8 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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.

Jim.
res.zip
0
 
LVL 4

Author Comment

by:Danielcmorris
Comment Utility
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:
Booked
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.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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?
Jim.
0
 
LVL 4

Author Comment

by:Danielcmorris
Comment Utility
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.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
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.

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.

LOL.

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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.

Jim.
0
 
LVL 4

Author Comment

by:Danielcmorris
Comment Utility
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now