On May 18, 2011, at 11:28 PM, Igor Tandetnik wrote:
> 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:
Nicely done :)
Lets break it apart.
First, a test set:
create view event
as
select 1 as id,
date( '2011-01-02' ) as start_date,
date( '2011-01-08' ) as end_date
union all
select 2 as id,
date( '2011-01-06' ) as start_date,
date( '2011-01-12' ) as end_date
union all
select 3 as id,
date( '2011-01-18' ) as start_date,
date( '2011-01-21' ) as end_date;
> select * from event;
1|2011-01-02|2011-01-08
2|2011-01-06|2011-01-12
3|2011-01-18|2011-01-21
Then the query itself:
select start_gap,
min( end_gap ) as end_gap
from (
select date( e1.end_date, '+1 day' ) as start_gap,
date( e2.start_date, '-1 day' ) as end_gap
from (
select start_date,
end_date
from event
union all
select '' as start_date,
date('2011-01-01', '-1 day') as end_date
) e1
cross join (
select start_date,
end_date
from event
union all
select date('2011-02-01', '+1 day') as
start_date,
'' as end_date
) e2
where start_gap <= end_gap
and start_gap >= '2011-01-01'
and end_gap <= '2011-02-01'
and not exists
(
select 1
from event
where start_date between start_gap and
end_gap
or end_date between start_gap and end_gap
)
)
group by start_gap;
2011-01-01|2011-01-01
2011-01-13|2011-01-17
2011-01-22|2011-02-01
Very nice. One question though, regarding the inner most where clause:
where start_gap <= end_gap
and start_gap >= '2011-01-01'
and end_gap <= '2011-02-01'
Where does the start_gap and end_gap come from? They are only declared in the
select part of the inner select statement, and nowhere in the from part. But
nonetheless, SQLite manages to use these non existing columns in the where
clause. What gives?
In other words, how come the following works in SQLite:
select 1 as value where value > 0;
Or even weirder:
select 1 as value where value = 0;
There is no from clause, there is no column 'value' per se, but nonetheless
that non existing column can be referenced in the where clause. What gives?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
