2008/8/15 novice <user.postgresql@gmail.com>:
> Hi,
> I have a table
>
> select id, config_id, start_day, end_day, start_time, end_time from config;
>
> id | config_id | start_day | end_day | start_time | end_time
> -----+-----------+-----------+---------+------------+----------
> 1 | 101 | Mon | Sun | 08:30:00 | 18:00:00
> 2 | 101 | Mon | Sun | 18:00:00 | 22:00:00
> 3 | 555 | Mon | Fri | 08:30:00 | 16:00:00
>
>
>
> I'd like to write a query to generate the following... is it possible at all?
>
> config_id | day | start_time | end_time
> -----------+-----------+---------+-------------
> 101 | Mon | 08:30:00 | 18:00:00
> 101 | Mon | 18:00:00 | 22:00:00
> 101 | Tue | 08:30:00 | 18:00:00
> 101 | Tue | 18:00:00 | 22:00:00
> 101 | Wed | 08:30:00 | 18:00:00
> 101 | Wed | 18:00:00 | 22:00:00
> 101 | Thu | 08:30:00 | 18:00:00
> 101 | Thu | 18:00:00 | 22:00:00
> 101 | Fri | 08:30:00 | 18:00:00
> 101 | Fri | 18:00:00 | 22:00:00
> 101 | Sat | 08:30:00 | 18:00:00
> 101 | Sat | 18:00:00 | 22:00:00
> 101 | Sun | 08:30:00 | 18:00:00
> 101 | Sun | 18:00:00 | 22:00:00
> 555 | Mon | 08:30:00 | 18:00:00
> 555 | Tue | 08:30:00 | 18:00:00
> 555 | Wed | 08:30:00 | 18:00:00
> 555 | Thu | 08:30:00 | 18:00:00
> 555 | Fri | 08:30:00 | 18:00:00
>
> Thanks
>
Solved:
create TABLE weekday
(
wd varchar(3),
seq int
)
INSERT INTO weekday (wd, seq) VALUES
('Mon', '1'),
('Tue', '2'),
('Wed', '3'),
('Thu', '4'),
('Fri', '5'),
('Sat', '6'),
('Sun', '7');
SELECT config.config_id, w.wd, config.start_time, config.end_time
FROM config
INNER JOIN weekday s ON config.start_day = s.wd
INNER JOIN weekday e ON config.end_day = e.wd
CROSS JOIN weekday w
WHERE w.seq >= s.seq
AND w.seq <= e.seq
ORDER BY config.config_id, w.seq, w.wd
