This is often a major issue for bookings and managing complex rental items. There are a multitude of ways to do it, some easy and some hard, some flexible and some not. Some of our systems use a simple “Additive” approach.
For example, you have a table of products, you’ve got a count of how many you have. Then you count how many are currently on live jobs. Whatever is remaining is your available stock. To determine future availability, you just count the products which are planned to be out for that period of time. However, this leads to some awful mistakes when stock is scheduled to be transferred to different warehouses.
We’ve found the best way to handle rental inventory is to create a shipments table and use that to calculate both total stock and inventory avaiablity quantities for time periods. However, this often has to be emulated. Quite often, there are multiple reasons for a product to be taken out of availability. The best example is for QA or repair. Nationwide Rentals takes each of it’s projectors and recalibrates the lenses, QA’s the projector and overhauls a lot of the equipment as soon as it is back. Depending on whether it needs work or not will make the determination of when it goes back into availability. So, you need a way to hold it out without a rental order. Town and Country Event Rentals has wash dishes and linens after a rental, so that needs to be pulled out for a set of time after it’s actually been received from the client and marked returned.
Since we can’t always have a nice clean shipments table, we create a shipments “view” in the database. In fact, we create a shipments_in and a shipments_out, individually, and then we use those to create a “shipments” view with a union of those two, containing the shipment date and quantity of every single product.
Our Shipment Table has the following columns: ShipDate, ProductId, WarehouseId, Quantity.
Incoming will be positive quantities and outgoing are negative.
Now, “availability”, is a calculation that results in the minimum available stock at any point in the rental period. Basically, if we are to rent 10 widgets; we own 10. On Monday morning we have 9 in stock. 3 are rented and going out on Tuesday, one will come back on wed, two more on thursday and one goes out on Friday, if someone wants to know how many we have available for a Monday – Friday rental, the answer is 6, because that is the lowest number available at any point in time during the week.
To make this calculation, you need 2 specific things. First, you need to know exactly how many you will have in stock at the beginning point of the rental. We call that the “stock-point” or “stock-count”. Then, you need a running list of the reservations and returns scheduled for the entire period of time you are intending to rent for. We call that the shipment register or transfer register. It’s exactly like a check register, where your current balance may be one thing, but you know full well that you’ve got post-dated checks out and some post-dated checks that you’ve received and have not yet deposited.
Here is the sql we use.
ALTER FUNCTION [dbo].[fn_get_stock_v2](
@start_date datetime,
@end_date datetime,
@productid int,
@warehouse_id int)
returns int
as
begin
DECLARE @stock INT
DECLARE @available INT
DECLARE @shipments TABLE
(
ship_date DATETIME ,
quantity numeric(8,2)
)
set @stock = 1234567
-- this is just a sample. since many databases are different, we get this value
-- from various different sources.
-- base stock must be combined with orders spanning this job to provide a base
-- note: earlier in the script, we have declared and loaded @stock, which is a base count
-- added to the inventory when the system was first created. It can also be used
-- if you want to have reconciliations. Sometimes we use the "last-reconcile date"
-- from a reconciliation table to show the actual stock date rather than just going
-- back to a date earlier than the shipments table (as we do here with jan 1999).
INSERT INTO @shipments
(
ship_date ,
quantity
)
SELECT '1/1/1999 12:00 AM', ISNULL(SUM(quantity),0)+@stock FROM dbo.vw_Shipments s
WHERE ship_date<@start_date AND s.warehouse_id = @warehouse_id AND s.product_id = @productid -- Now we have the table @shipments holding a single record containing the base amount of -- combined with the net sum of all shipments providing us with the starting stock-count. -- Now add in all shipments into and out of the warehouse from that point forward -- The shipments are summed up here to account for any shipments in which the same product -- is shipped multiple times during the same exact date. We want the final query to focus -- on the minimum counts rather than additional aggregations. INSERT INTO @shipments ( ship_date , quantity ) SELECT ship_date , SUM( quantity) FROM vw_Shipments WHERE ship_date>=@start_date AND ship_date<@end_date
AND Product_ID = @productid
AND warehouse_id = @warehouse_id
GROUP BY ship_date
-- Here, we are doing a COALESCE subquery. That creates a running total of the shipment quantities as
-- the base, sorted by ship-date. Then, we do a simple "MIN" on top of that. The result
-- will be the minimum available stock during the period in question.
SELECT @available = MIN(RunningTotal)
FROM ( SELECT TOP 10000
RunningTotal = quantity
+ COALESCE(( SELECT SUM(quantity)
FROM @shipments AS s
WHERE s.ship_date < s2.ship_date
), 0)
FROM @shipments s2
ORDER BY ship_date
) b
return @available
Now, the usage of this function is simple:
/* get the available stock to display on a catalog list of all widgets in the "Big Stuff" category for a rental period between 2/3/16 and 2/23/16 if we are pulling from warehouse 123 */ select p.product_id, p.product_name, [dbo].[fn_get_stock_v2]( '2/3/16', '2/23/16', p.product_id, p.warehouse_id) as AvailableStock from ProductList p where p.warehouse_id = 123
Now, be warned, this is NOT going to be super fast if you’re pulling thousands of products. There are MUCH more efficient ways to do this, however, this is the simplest and most easily understood way to get these totals in an extremely flexible way if your back-end is a bit crazy.
If you want to do a bit more “work” up front, you can increase efficiency a thousandfold by keeping a stock registry that has a running total associated with it. It is “work”, because you’ll need to do more than just update one record with each reservation. Since you may have a booking in Dec 2017, when you make a job in Nov 2017, the available stock calculation for Dec 2017 will have to be updated, as will every single record from that point forward. Unfortunately, 99% of custom database systems are actually a complete disaster inside and have been tweaked in different ways for a decade.
There are other ways, but the concept will always be the same. It’s just a matter of how you get to the base information most efficiently for the size of the recordset you intend to return.
