Rental Inventory Availability

[Ron Neville]

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, 2017 by Ron Neville

[bcooney]

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

[Riley Casey]

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

[Wim Decorte]

You could use one of the free plugins that support SQL if you really want to use SQL...

Why rebuild in 11 though?  Can't help but feel that is such a waste of effort not going to the most recent version...