|
I’m looking for a way to find first free time in reservations table.
Reservation table contains reservations start dates, start hours and
durations.
Start hour is by half hour increments in working hours 8:00 .. 18:00 in
work days.
Duration is also by half hour increments in day.
CREATE TABLE reservation (
id serial primary key,
startdate date not null, -- start
date
starthour numeric(4,1) not null , -- start
hour 8 8.5 9 9.5 .. 16.5 17 17.5
duration Numeric(3,1) not null --
duration by hours 0.5 1 1.5 .. 9 9.5 10
);
table structure can changed if required.
How to find first free half hour in table which is not reserved ?
E.q if table contains
startdate starthour duration
14
9
1
-- ends at 9:59
14
10
1.5 -- ends at
11:29, e.q there is 30 minute gap before next
14
12 2
14
16 2
result should be:
starthour duration
11.5 0.5
Probably PostgreSql 9.2 window function should used to find
first row whose starthour is greater than previous row starthour +
duration
How to write select statement which returns this information ?
Andrus.
I posted this also in
|
