cast function - 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)

cast function

Author

Topic

phaze
Starting Member

42 Posts

Posted - 2006-12-14 : 10:47:58

I have the following query

select amount
from ( select amount = case
when amount between .01 and 100.00 then '.01 - 100.00'
when amount between 101.00 and 1000.00 then '101.00 - 1000.00'
when amount between 1001.00 and 10000.00 then '1001.00 - 10000.00'
when amount >= 10001.00 then '10001+'
end
from bia.dbo.trans
)as result
group by amount

how would i use the cast function on the dollar values?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-14 : 10:58:30

What dollar values? The query returns a character sting only.

CODO ERGO SUM

phaze
Starting Member

42 Posts

Posted - 2006-12-14 : 11:04:53

i wanted to make these into money values

'.01 - 100.00'
'101.00 - 1000.00'
'1001.00 - 10000.00'
'10001+'

thanks for the response

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-14 : 11:12:12

quote:
Originally posted by phaze

i wanted to make these into money values

'.01 - 100.00'
'101.00 - 1000.00'
'1001.00 - 10000.00'
'10001+'

thanks for the response

That doesn't make any sense to me.

What are you trying to do? What would you output look like?

CODO ERGO SUM

phaze
Starting Member

42 Posts

Posted - 2006-12-14 : 11:15:46

this is my result

amount
------------------
NULL
.01 - 100.00
10001+
1001.00 - 10000.00
101.00 - 1000.00

i wanted to have the results to be in ascending order. through my search i believe that the only i can have it order that by is to cast as money, but i'm not sure of how to cast it

does that make any sense?

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-14 : 11:44:24

You can't cast a non numeric value to a numeric datatype.

You could use a temp table...
Create Table #Sort
(sort int
, val varchar(50))
insert into #Sort values (1, '.01 - 100.00')
insert into #Sort values (2, '101.00 - 1000.00')
insert into #Sort values (3, '1001.00 - 10000.00')
insert into #Sort values (4, '10001+')

select
amount
from
(select amount = case
when amount between .01 and 100.00 then '.01 - 100.00'
when amount between 101.00 and 1000.00 then '101.00 - 1000.00'
when amount between 1001.00 and 10000.00 then '1001.00 - 10000.00'
when amount >= 10001.00 then '10001+'
end
from bia.dbo.trans) as result
inner join #sort s on result.amount = s.val
group by amount
order by min(s.sort)

or duplicate your case statement...

select
amount
from
(select amount = case
when amount between .01 and 100.00 then '.01 - 100.00'
when amount between 101.00 and 1000.00 then '101.00 - 1000.00'
when amount between 1001.00 and 10000.00 then '1001.00 - 10000.00'
when amount >= 10001.00 then '10001+'
end
, sort = case
when amount between .01 and 100.00 then 1
when amount between 101.00 and 1000.00 then 2
when amount between 1001.00 and 10000.00 then 3
when amount >= 10001.00 then 4
end
from bia.dbo.trans) as result
group by amount, sort
order by sort

phaze
Starting Member

42 Posts

Posted - 2006-12-14 : 11:49:07

I will be duplicating my case statement.

I appreciate your help a lot

kudos to you

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-14 : 16:37:45

quote:
Originally posted by JoeNak

You can't cast a non numeric value to a numeric datatype.

You could use a temp table...
Create Table #Sort
(sort int
, val varchar(50))
insert into #Sort values (1, '.01 - 100.00')
insert into #Sort values (2, '101.00 - 1000.00')
insert into #Sort values (3, '1001.00 - 10000.00')
insert into #Sort values (4, '10001+')

select
amount
from
(select amount = case
when amount between .01 and 100.00 then '.01 - 100.00'
when amount between 101.00 and 1000.00 then '101.00 - 1000.00'
when amount between 1001.00 and 10000.00 then '1001.00 - 10000.00'
when amount >= 10001.00 then '10001+'
end
from bia.dbo.trans) as result
inner join #sort s on result.amount = s.val
group by amount
order by min(s.sort)

Nice, but even better is:


Create Table #Sort
	(sort int
	, desc varchar(50)
        ,  min money
        , max money)

insert into #Sort values (1, '.01 - 100.00',.01,100)
insert into #Sort values (2, '101.00 - 1000.00',101,1000)
insert into #Sort values (3, '1001.00 - 10000.00',1001,10000)
insert into #Sort values (4, '10001+',10001,99999999)

select 
	S.desc, count(*)
from 
        bia.dbo.trans  T
inner join #sort s on T.amount between s.min and s.max
group by s.desc, s.sort
order by s.sort

Of course, any of these algorithms has a flaw in that the values like 100.5, 1000.5, and 10000.5 will be skipped ....

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources