Query help

Hi ,

below can work?
select config_id, start_day as day, start_time, end_time from config
union
select config_id, end_day as day, start_time, end_time from config

Best Regards,
Hui Xie
-------------------------------------------
Axisoft Co. Ltd. Zhuhai Branch
Tel: (86) 0756-3612121 8858

novice <user.postgresql@gmail.com>
Sent by: pgsql-general-owner@postgresql.org
15/08/2008 08:32 AM

To
pgsql-general@postgresql.org
cc

Subject
[GENERAL] 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

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

*** The email has been scanned by AxiScan ***

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