On 5/18/2011 4:17 PM, Pavel Ivanov wrote:
>> I need help to build a statement in order to select all days free from
>> events in a specific time range.
>
> This kind of task should be implemented in your application. SQL
> wasn't intended for and can't solve such tasks.
Sounds like a challenge:
select startGap, min(endGap) from
(
select date(e1.endDate, '+1 day') startGap, date(e2.startDate, '-1
day') endGap
from (select startDate, endDate from events
union all
select '' startDate, date('2011-01-01', '-1 day') endDate) e1,
(select startDate, endDate from events
union all
select date('2011-02-01', '+1 day') startDate, '' endDate) e2
where startGap <= endGap and startGap >= '2011-01-01' and endGap <=
'2011-02-01'
and not exists (
select 1 from events where startDate between startGap and endGap
or endDate between startGap and endGap)
)
group by startGap;
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
