Split the number 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 2005 Forums

Transact-SQL (2005)

Split the number range

Author

Topic

Mir
Starting Member

19 Posts

Posted - 2009-11-18 : 00:13:30

Hi guys,

How can I change

0.00 to 2.00
into
0.00 to 1.00
1.00 to 2.00

I try using case function but got stuck.
Anyone please help ?
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-18 : 00:25:22

always increment by 1 ?

cross join to a number table. use F_TABLE_NUMBER_RANGE if you don't have one

select NUMBER, NUMBER + 1
from F_TABLE_NUMBER_RANGE(0, 2)

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

Mir
Starting Member

19 Posts

Posted - 2009-11-18 : 00:37:58

Thanks for your reply KHTAN,

The original data is
FROM TO
0.00 - 2.00
2.00 - 3.00

I want the output to be
FROM TO
0.00 - 1.00
1.00 - 2.00
2.00 - 3.00

Thanks..

quote:
Originally posted by khtan

always increment by 1 ?

cross join to a number table. use F_TABLE_NUMBER_RANGE if you don't have one

select NUMBER, NUMBER + 1
from F_TABLE_NUMBER_RANGE(0, 2)

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-18 : 02:31:10

[code]
select NUMBER, NUMBER + 1
from yourtable s
cross apply F_TABLE_NUMBER_RANGE(st, en - 1)
order by NUMBER
[/code]

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

Mir
Starting Member

19 Posts

Posted - 2009-11-18 : 03:40:33

Thanks for ur reply khtan.

what is st and en?
How can i apply column from and to from mytable to this query?

quote:
Originally posted by khtan

select NUMBER, NUMBER + 1
from yourtable s
cross apply F_TABLE_NUMBER_RANGE(st, en - 1)
order by NUMBER

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-18 : 04:30:42

st is the FROM
en is the TO
of yourtable

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources