select query issue - 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 2005 Forums

Transact-SQL (2005)

select query issue

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2007-08-23 : 11:13:59

Hi All,

I have a table which stores numbers in ranges to save storage space.
e.g.
Prefix_num Lowerbound Upperbound
123456 100 150.
thus we will have numbers from 123456100 to 123456150 stored.

the following sample query displays data from some tables:

select Prefix_num, Lowerbound, Upperbound
from PhoneTable1
inner join PhoneTable2
-- some search criteria
inner join PhoneTable3
-- some search criteria
inner join PhoneTable4
-- some search criteria
where country = 'XX'

this query will display the data as shown...
123456 100 150 Data1 Data2
123466 200 250 Data1 Data2

But, i want to display this data as follows:
123456 100 Data1 Data2
123456 101 Data1 Data2
123456 102 Data1 Data2
TILL 150...

123466 201 Data1 Data2
123466 202 Data1 Data2
123466 203 Data1 Data2
123466 204 Data1 Data2
TILL 200...

Can anybody plz. suggest me how to go about it.
Is this possible only through cursors???

TIA,
Ziangi.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-23 : 11:26:58

[code]DECLARE @TABLE TABLE
(
Prefix_num int,
Lowerbound int,
Upperbound int
)

INSERT INTO @TABLE
SELECT 123456, 100, 150 UNION ALL
SELECT 123466, 200, 250

DECLARE @phone TABLE
(
Prefix_num int,
Data1 int,
Data2 int
)

INSERT INTO @phone
SELECT 123456, 10, 10 UNION ALL
SELECT 123466, 20, 20

SELECT t.Prefix_num, n.NUMBER, p.Data1, p.Data2
FROM @TABLE t INNER JOIN @phone p
ON t.Prefix_num = p.Prefix_num
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE
CROSS apply F_TABLE_NUMBER_RANGE(t.Lowerbound, t.Upperbound) n
ORDER BY t.Prefix_num, n.NUMBER[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 11:55:03

This is now officially the new EXAM question

http://www.dbforums.com/showthread.php?p=6292072

E 12°55'05.25"
N 56°04'39.16"

zion99
Posting Yak Master

141 Posts

Posted - 2007-08-25 : 05:33:50

Hi khtan,
sorry for the late reply. As per your solution, i will have to create a new table. The Sql query which i use parses more than 2 million records. so i think creating a new table will be inefficient.
i m getting the results dynamically.
results are displayed as :
123456 100 150 Data1 Data2
123466 200 250 Data1 Data2

But, i want to display this data as follows:
123456 100 Data1 Data2
123456 101 Data1 Data2
123456 102 Data1 Data2
isn' there a way to check 2nd & 3rd column of the first display & then show the second display

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-25 : 06:45:26

No, the creation of table is only to mimic your environment.
Scroll down to the REAL QUERY and now you wee what you want.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources