Demaestro - 4:05 pm on Feb 15, 2012 (gmt 0)

Hey skross,

No worries, I have done this kind of thing a couple times and that is the only reason I know about all the little pitfalls.

You will have to do some testing and if it doesn't quite work post back but this is how I think you can pull them both off.

So for #1, which is the contract was active for at least one day in the range given:

select
*
from
contracts
where
(contract_start between DATE('2012-05-01') and DATE('2012-09-01')
Or contract_finish between DATE('2012-05-01') and DATE('2012-09-01'))
OR
(contract_start <= DATE('2012-05-01') and contract_finish >= DATE('2012-09-01'))

//I think that will have it, you have 2 where clauses, the first chacks if the start or the end date is between the range, which would mean it was active at least one day.... then the next one checks to see if the start is equal to or before the date range begins and the end date is equal to or is after the date range ends which would mean the contract was active for the whole time period.


Then for #2, which is the contract was active all days in the range given:

select
*
from
contracts
where
(contract_start <= DATE('2012-05-01') and contract_finish >= DATE('2012-09-01'))

Which checks if the start is before or equal to the date range begins and the end date is after or equal to the date range ends which would mean the contract was active for the whole time period.

I have tested this so what I would do if I were you is create a simple table, populate it with a few rows of different contract_start and contract_finish times and do some testing and make sure it is working the way you need.

I am happy to help if you need more, this should get you most of the way there.... I hope.