DURETTE, STEVEN J (AIT) wrote:
>  
> But you can use between
>  
> select count(idField)
> from yourtable
> where type = 'checktype'
> and (startDateField between 'startdate' and 'enddate'
>     OR endDateField between 'startdate and 'enddate')

That won't work, it won't detect the following overlap:
('2003-12-01', '2003-12-31') & ('2003-12-02', '2003-12-30')

You need a statement that is actually even simpler:
SELECT COUNT(idField)
FROM yourtable
WHERE type = 'checktype'
AND startDateField < <cfqueryparam cfsqltype="cf_sql_date"
value="#formEndDate#">
AND endDateField > <cfqueryparam cfsqltype="cf_sql_date"
value="#formStartDate#">

Just make sure you verify that the user submitted startdate is
less than the user submitted enddate.

Jochem

--
Who needs virtual reality
if you can just dream?
     - Loesje
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

Reply via email to