SOLVED: Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SOLVED: Query help

Author

Topic

pcock
Starting Member

12 Posts

Posted - 2008-08-14 : 20:35:24

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-14 : 23:04:48

[code]DECLARE @TABLE TABLE
(
id int,
config_id int,
start_day varchar(3),
end_day varchar(3),
start_time varchar(8),
end_time varchar(8)
)
INSERT INTO @TABLE
SELECT 1 , 101 , 'Mon' , 'Sun' , '08:30:00' , '18:00:00' UNION ALL
SELECT 2 , 101 , 'Mon' , 'Sun' , '18:00:00' , '22:00:00' UNION ALL
SELECT 3 , 555 , 'Mon' , 'Fri' , '08:30:00' , '16:00:00'

DECLARE @weekday TABLE
(
wd varchar(3),
seq int
)
INSERT INTO @weekday
SELECT wd = 'Mon', seq = 1 UNION ALL
SELECT wd = 'Tue', seq = 2 UNION ALL
SELECT wd = 'Wed', seq = 3 UNION ALL
SELECT wd = 'Thu', seq = 4 UNION ALL
SELECT wd = 'Fri', seq = 5 UNION ALL
SELECT wd = 'Sat', seq = 6 UNION ALL
SELECT wd = 'Sun', seq = 7

SELECT t.config_id, w.wd, t.start_time, t.end_time
FROM @TABLE t
INNER JOIN @weekday s ON t.start_day = s.wd
INNER JOIN @weekday e ON t.end_day = e.wd
CROSS JOIN @weekday w
WHERE w.seq >= s.seq
AND w.seq <= e.seq
ORDER BY t.config_id, w.seq, w.wd

/*
config_id wd 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 16:00:00
555 Tue 08:30:00 16:00:00
555 Wed 08:30:00 16:00:00
555 Thu 08:30:00 16:00:00
555 Fri 08:30:00 16:00:00

(19 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

pcock
Starting Member

12 Posts

Posted - 2008-08-14 : 23:53:55

Thank you again :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources