end result is a percentage... - 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

General SQL Server Forums

New to SQL Server Programming

end result is a percentage...

Author

Topic

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2010-01-28 : 14:00:10

I came across this requirement which gets a little tricky because the end result is a Percentage Dec(3,2) ? and the inputs are integers.

Given this sequel below I'm getting zero as a result and it must have to do with the order of mathematical expressions.

Do you see what I might be missing in this statement or is there a better approach?

Select subject, technique,
	cast(sum((target_duration - down_duration) / target_duration) as dec(3,2)) -- * 100
-- sum(cast(((target_duration - down_duration) / target_duration) as dec)) as Percnt
    from dbo.event_results
group by subject, technique

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-01-28 : 14:18:03

In sql server an integer divided by an integer is an integer, so 1/2 = 0. Try
Select subject, technique,
cast(sum((target_duration - down_duration) / target_duration*1.0E) as dec(3,2)) -- * 100

from dbo.event_results
group by subject, technique

Jim

Everyday I learn something that somebody else already knew

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2010-01-28 : 14:56:20

Still getting zero rows returned.. here is my table DDL

CREATE TABLE [dbo].[event_results](
[event_results_ID] [int] IDENTITY(1,1) NOT NULL,
[subject] [varchar](18) NOT NULL,
[technique] [varchar](20) NOT NULL,
[posted_dt] [datetime] NOT NULL DEFAULT (getdate()),
[target_start_dt] [datetime] NOT NULL,
[target_end_dt] [datetime] NOT NULL,
[target_duration] [int] NOT NULL DEFAULT ((0)),
[down_start_dt] [datetime] NULL,
[down_end_dt] [datetime] NULL,
[down_duration] [int] NULL,
[down_event_id] [int] NULL,

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-28 : 15:06:10

Should be this way I guess..
Select subject, technique,
cast(sum((target_duration - down_duration)*1.0E / target_duration) as dec(3,2)) -- * 100
from dbo.event_results
group by subject, technique

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2010-01-28 : 15:28:43

that might works. Thanks guys for your help!

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2010-01-28 : 15:32:03

cabn you tell me what *1.0E signifies? Just trying to understand..

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-01-28 : 15:44:27

I believe it turns the number into a float. Peso does it, which is pretty much why I do it, so he probably understands what's up with it.

Try select 1/(7*1.0E) versus select 1/(7*1.0) and see the difference

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-29 : 02:33:43

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources