HELP with the Range - 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)

HELP with the Range

Author

Topic

HardHabit
Starting Member

12 Posts

Posted - 2008-07-21 : 00:02:38

Please help me with this problem...

This is my table

City Street Range Type
ABERDEEN BARBOUR DR 200-208 E
ABERDEEN BARBOUR DR 201-209 O

and I want the result look like this
CITY ADDRESS Range Type
ABERDEEN BARBOUR DR 0 E
ABERDEEN BARBOUR DR 2 E
ABERDEEN BARBOUR DR 4 E
ABERDEEN BARBOUR DR 6 E
ABERDEEN BARBOUR DR 8 E

ABERDEEN BARBOUR DR 1 O
ABERDEEN BARBOUR DR 3 O
ABERDEEN BARBOUR DR 5 O
ABERDEEN BARBOUR DR 7 O
ABERDEEN BARBOUR DR 9 O

any help would be highly appreciated....

Thanks in advance....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 01:21:17

[code]DECLARE @TABLE TABLE
(
City varchar(10),
Street varchar(10),
Range_s int,
Range_e int,
Type CHAR(1)
)
INSERT INTO @TABLE
SELECT 'ABERDEEN', 'BARBOUR DR', 200, 208, 'E' UNION ALL
SELECT 'ABERDEEN', 'BARBOUR DR', 201, 209, 'O'

SELECT t.City, t.Street, n.n, t.Type
FROM @TABLE t
INNER JOIN
(
SELECT n = 0 UNION ALL
SELECT n = 1 UNION ALL
SELECT n = 2 UNION ALL
SELECT n = 3 UNION ALL
SELECT n = 4 UNION ALL
SELECT n = 5 UNION ALL
SELECT n = 6 UNION ALL
SELECT n = 7 UNION ALL
SELECT n = 8 UNION ALL
SELECT n = 9
) n ON n <= t.Range_e - t.Range_s
AND (
(t.Type = 'E' AND n % 2 = 0)
OR (t.Type = 'O' AND n % 2 = 1)
)

/*
City Street n Type
---------- ---------- ----------- ----
ABERDEEN BARBOUR DR 0 E
ABERDEEN BARBOUR DR 2 E
ABERDEEN BARBOUR DR 4 E
ABERDEEN BARBOUR DR 6 E
ABERDEEN BARBOUR DR 8 E
ABERDEEN BARBOUR DR 1 O
ABERDEEN BARBOUR DR 3 O
ABERDEEN BARBOUR DR 5 O
ABERDEEN BARBOUR DR 7 O

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

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

HardHabit
Starting Member

12 Posts

Posted - 2008-07-21 : 01:58:59

Thanks khtan!

Your code is great!...but i have another problem regarding the code that you give to me.,

What if i have the range of 100 or 1000? (sorry for not mentioning it earlier

)....or do you have other idea regarding my problem

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 02:00:59

use a number table or http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources