Rental Inventory Availability

January 2, 20179 yr

HI Guys and all the best for 2017…

Could I please ask for advice on a SQL query i require as part of an availabilities system I am building for a rental database where rental businesses rent inventory to customers for a time period.

When a rental job is created it has a start & end date also a start & end time. When I go to my rental item list to add a rental item, next to each inventory item I want to see how many in total I have (own) and how many are available for rent for the date/time range added to the job.

Tables
Rentals
Line_items
Inventory

Each lineitem added to a Rental has a start/end timestamp so I need a SQL query that will count the QTY in the line_items table for each item for the date/time range of the job.

E.g
SELECT COUNT qty (Can SELECT COUNT be used in FM?)
FROM Line_Items
WHERE id_Product = X AND date > X AND dateEnd < Y

Any ideas or suggestions would be greatly appreciated..

Edited January 2, 20179 yr by Ron Neville

January 2, 20179 yr

Here are two links to help:

http://stackoverflow.com/questions/14208958/select-data-from-date-range-between-two-dates

http://www.soliantconsulting.com/blog/2016/03/determining-if-two-date-ranges-overlap

January 4, 20179 yr

Newbies

Glad you posted this question as I'm rebuilding a Filemaker 6 database that serves a rental based business and this functionality seems difficult to explain to FM developers. I'm rebuilding it to use in FM 11 however. Is there anything of value in those links for a version of FM that doesn't use SQL ( as I'm pretty sure FM 11 does not )?

Thanks