Обсуждение: Query help

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

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

If I read this correctly, you want the output sorted by config_id,start_day(day),start_time,

thus:

select config_id, start_day as day, start_time, end_time from config
order by config_id, start_day, start_time;


Cheers,

  Brent Wood

>>> novice <user.postgresql@gmail.com> 08/15/08 3:55 PM >>>
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general