select 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)

select query help...

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2009-04-02 : 08:49:29

I have a file that contain a list of ranges...assign and ID to the another table base on the range assigned. eg:

tblRange:
ID, Ranges:
1,1-100
2,101-200
3,201-400

tblMain
id,sum,rangeID
1,100,1
2,50,1
3,400,3

In the past I have used case statement, but code the range in:

select id,sum,
case when sum between 1 and 100 then 1
when sum between 101 and 200 then 2
when sum between 201 and 400 then 3
from table.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-02 : 09:15:11

I'm sorry...but what is the question...
Can you explain what you want to get done?

maeenul
Starting Member

20 Posts

Posted - 2009-04-02 : 09:27:19

I am not sure what exactly your question is. But you can take care of the case statement. You have to give end at the end of case statement.

select id,sum,
case when sum between 1 and 100 then 1
when sum between 101 and 200 then 2
when sum between 201 and 400 then 3
end
from table.

-----------------------
maeenul

http://sqlservertipsntricks.blogspot.com

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-02 : 09:40:29

[code]
create table #tblRange (ID int , Ranges varchar(100))
insert into #tblRange
select 1,'1-100'union all
select 2,'101-200' union all
select 3,'201-400'

create table #tblMain (ID int , sum float)
insert into tblMain
select 1,100 union all
select 2,50 union all
select 3,400

select a.id,a.sum,b.ID from #tblMain a join #tblRange b on sum between left(Ranges,charindex('-',Ranges)-1)
and
reverse(left(reverse(Ranges),charindex('-',reverse(Ranges))-1))[/code]

hai
Yak Posting Veteran

84 Posts

Posted - 2009-04-02 : 13:44:11

thank you very much Sakets_2000

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-02 : 13:46:11

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources