Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>>I have a view with 3 integer fields: FailureCount, QuestionCount and
>>AuditCount. I want to get the following calculated result in a SELECT
>>statement:
>>SELECT FailureCount / (QuestionCount * AuditCount)
>>Problem is, this returns an integer result (as one would expect from 3
>>integers). I need the real number value, and CONVERT isn't working. I've
>>tried various syntaxes and I either get errors or no effect at all.
>>Any ideas how to properly do this?
>>Thanks,
>>Randall Arnold
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:

>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.
4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall Arnold
On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:

>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Convert Seconds to Time

Alright... i have a field Browse_Time which is in seconds... just a plain
numeric value... such as 1664 which in the real world would be
approximately 28 minutes how do I convert seconds to hours:minutes' I am
clueless...Carlos Rapa wrote:
> Alright... i have a field Browse_Time which is in seconds... just a
> plain numeric value... such as 1664 which in the real world would
> be approximately 28 minutes how do I convert seconds to
> hours:minutes' I am clueless...
Hi Carlos
use the code below in customCode
Public Function TimeString(Seconds As Long, Optional Verbose _
As Boolean = False) As String
'if verbose = false, returns
'something like
'02:22.08
'if true, returns
'2 hours, 22 minutes, and 8 seconds
Dim lHrs As Long
Dim lMinutes As Long
Dim lSeconds As Long
lSeconds = Seconds
lHrs = Int(lSeconds / 3600)
lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
lSeconds = Int(lSeconds Mod 60)
Dim sAns As String
If lSeconds = 60 Then
lMinutes = lMinutes + 1
lSeconds = 0
End If
If lMinutes = 60 Then
lMinutes = 0
lHrs = lHrs + 1
End If
sAns = Format(CStr(lHrs), "#####0") & ":" & _
Format(CStr(lMinutes), "00") & "." & _
Format(CStr(lSeconds), "00")
If Verbose Then sAns = TimeStringtoEnglish(sAns)
TimeString = sAns
End Function
Private Function TimeStringtoEnglish(sTimeString As String) _
As String
Dim sAns As String
Dim sHour, sMin As String, sSec As String
Dim iTemp As Integer, sTemp As String
Dim iPos As Integer
iPos = InStr(sTimeString, ":") - 1
sHour = Left$(sTimeString, iPos)
If CLng(sHour) <> 0 Then
sAns = CLng(sHour) & " hour"
If CLng(sHour) > 1 Then sAns = sAns & "s"
sAns = sAns & ", "
End If
sMin = Mid$(sTimeString, iPos + 2, 2)
iTemp = sMin
If sMin = "00" Then
sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
Else
sTemp = IIf(iTemp = 1, " minute", " minutes")
sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
sAns = sAns & Format$(iTemp, "##") & sTemp
End If
iTemp = Val(Right$(sTimeString, 2))
sSec = Format$(iTemp, "#0")
sAns = sAns & sSec & " second"
If iTemp <> 1 Then sAns = sAns & "s"
TimeStringtoEnglish = sAns
End Function
* Source www.freevbcode.com*
regards
Frank|||I feel kind of dumb but where is the customCode area? is it where I right
click on the field click properties? and towards the right hand side there is
Custom: with the button and blank textbox? if so I put it in there and
the field just comes up with the formula...
"Frank Matthiesen" wrote:
> Carlos Rapa wrote:
> > Alright... i have a field Browse_Time which is in seconds... just a
> > plain numeric value... such as 1664 which in the real world would
> > be approximately 28 minutes how do I convert seconds to
> > hours:minutes' I am clueless...
>
> Hi Carlos
> use the code below in customCode
> Public Function TimeString(Seconds As Long, Optional Verbose _
> As Boolean = False) As String
> 'if verbose = false, returns
> 'something like
> '02:22.08
> 'if true, returns
> '2 hours, 22 minutes, and 8 seconds
> Dim lHrs As Long
> Dim lMinutes As Long
> Dim lSeconds As Long
> lSeconds = Seconds
> lHrs = Int(lSeconds / 3600)
> lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
> lSeconds = Int(lSeconds Mod 60)
> Dim sAns As String
>
> If lSeconds = 60 Then
> lMinutes = lMinutes + 1
> lSeconds = 0
> End If
> If lMinutes = 60 Then
> lMinutes = 0
> lHrs = lHrs + 1
> End If
> sAns = Format(CStr(lHrs), "#####0") & ":" & _
> Format(CStr(lMinutes), "00") & "." & _
> Format(CStr(lSeconds), "00")
> If Verbose Then sAns = TimeStringtoEnglish(sAns)
> TimeString = sAns
> End Function
> Private Function TimeStringtoEnglish(sTimeString As String) _
> As String
> Dim sAns As String
> Dim sHour, sMin As String, sSec As String
> Dim iTemp As Integer, sTemp As String
> Dim iPos As Integer
> iPos = InStr(sTimeString, ":") - 1
> sHour = Left$(sTimeString, iPos)
> If CLng(sHour) <> 0 Then
> sAns = CLng(sHour) & " hour"
> If CLng(sHour) > 1 Then sAns = sAns & "s"
> sAns = sAns & ", "
> End If
> sMin = Mid$(sTimeString, iPos + 2, 2)
> iTemp = sMin
> If sMin = "00" Then
> sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
> Else
> sTemp = IIf(iTemp = 1, " minute", " minutes")
> sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
> sAns = sAns & Format$(iTemp, "##") & sTemp
> End If
> iTemp = Val(Right$(sTimeString, 2))
> sSec = Format$(iTemp, "#0")
> sAns = sAns & sSec & " second"
> If iTemp <> 1 Then sAns = sAns & "s"
> TimeStringtoEnglish = sAns
> End Function
> * Source www.freevbcode.com*
> regards
> Frank
>
>|||Alright I figured out where the customcode is but unfortunately i keep getting
####0:00.0 as a result...
i have the field set up as
code.TimeString(Sum(Fields!browse_time.Value))
are there anyother settings i am forgetting'
Thanks a bunch,
Carlos
"Frank Matthiesen" wrote:
> Carlos Rapa wrote:
> > Alright... i have a field Browse_Time which is in seconds... just a
> > plain numeric value... such as 1664 which in the real world would
> > be approximately 28 minutes how do I convert seconds to
> > hours:minutes' I am clueless...
>
> Hi Carlos
> use the code below in customCode
> Public Function TimeString(Seconds As Long, Optional Verbose _
> As Boolean = False) As String
> 'if verbose = false, returns
> 'something like
> '02:22.08
> 'if true, returns
> '2 hours, 22 minutes, and 8 seconds
> Dim lHrs As Long
> Dim lMinutes As Long
> Dim lSeconds As Long
> lSeconds = Seconds
> lHrs = Int(lSeconds / 3600)
> lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
> lSeconds = Int(lSeconds Mod 60)
> Dim sAns As String
>
> If lSeconds = 60 Then
> lMinutes = lMinutes + 1
> lSeconds = 0
> End If
> If lMinutes = 60 Then
> lMinutes = 0
> lHrs = lHrs + 1
> End If
> sAns = Format(CStr(lHrs), "#####0") & ":" & _
> Format(CStr(lMinutes), "00") & "." & _
> Format(CStr(lSeconds), "00")
> If Verbose Then sAns = TimeStringtoEnglish(sAns)
> TimeString = sAns
> End Function
> Private Function TimeStringtoEnglish(sTimeString As String) _
> As String
> Dim sAns As String
> Dim sHour, sMin As String, sSec As String
> Dim iTemp As Integer, sTemp As String
> Dim iPos As Integer
> iPos = InStr(sTimeString, ":") - 1
> sHour = Left$(sTimeString, iPos)
> If CLng(sHour) <> 0 Then
> sAns = CLng(sHour) & " hour"
> If CLng(sHour) > 1 Then sAns = sAns & "s"
> sAns = sAns & ", "
> End If
> sMin = Mid$(sTimeString, iPos + 2, 2)
> iTemp = sMin
> If sMin = "00" Then
> sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
> Else
> sTemp = IIf(iTemp = 1, " minute", " minutes")
> sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
> sAns = sAns & Format$(iTemp, "##") & sTemp
> End If
> iTemp = Val(Right$(sTimeString, 2))
> sSec = Format$(iTemp, "#0")
> sAns = sAns & sSec & " second"
> If iTemp <> 1 Then sAns = sAns & "s"
> TimeStringtoEnglish = sAns
> End Function
> * Source www.freevbcode.com*
> regards
> Frank
>
>|||Carlos Rapa wrote:
> code.TimeString(Sum(Fields!browse_time.Value))
> are there anyother settings i am forgetting'
Don't know...are the results ok?
regards
Frank
www.xax.de|||You could use the TimeSpan structure:
=new TimeSpan(0, 0, Fields!Browse_Time.Value).Minutes & ":" & new
TimeSpan(0, 0, Fields!Browse_Time.Value).Seconds
See also:
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemTimeSpanClassTopic.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carlos Rapa" <Carlos Rapa@.discussions.microsoft.com> wrote in message
news:FF418AD5-D687-456A-ACA6-BE9D8FD49670@.microsoft.com...
> Alright... i have a field Browse_Time which is in seconds... just a plain
> numeric value... such as 1664 which in the real world would be
> approximately 28 minutes how do I convert seconds to hours:minutes' I
am
> clueless...

Convert real to Varchar.

Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.0000013)

Select * from A

------
1.3 e-05

I am getting '1.3 e-05' values because datatype is real so that is why I am getting but want to get '0.0000013' values. Please let me know what have to that for that.

Please let me know asap.

Regards,
M Jain
NJ,USAYou can convert "REAL" into "NUMERIC" first and then
convert to varchar.

like,

select convert(varchar, convert(NUMERIC(30,20), id)) from A

Convert real to Varchar.

Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.0013)

select convert(varchar, convert(DECIMAL(30,6), id)) from A

------
0.001300

I am getting '0.001300' values because scale is 6 in Decimal so that is why I am getting but want to get '0.0013' values. Please let me knowHEY NJ!

isn't it "Jersey"?

Doesn't this work?

USE Northwind
GO

CREATE TABLE A ([ID] Real)
GO

INSERT INTO A ([ID]) values(0.0013)

SELECT CONVERT(varchar(45), [ID]) FROM A
GO
DROP TABLE A
GO|||Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.000013)

select * from a

Ouput
------
1.13 e05

But I want '0.000013' to display so that is why I have written below Query.

select convert(varchar, convert(DECIMAL(30,6), id)) from A

------
0.00001300

I am getting '0.001300' values because scale is 6 in Decimal so that is why I am getting but want to get '0.0013' values. Please let me know

-- Is any function using that I can remove added/padded zero suffix.

Current Expected result.
0.00001300 to 0.000013|||Did you try what I gave you?|||You use 2 different numbers:

0.0013 and 0.000013 - which one is it ?

If it is the first then just change the decimal from 6 to 4.|||Does the precision matter if you're going to varchar?

And I still want to know

What exit?

Convert real => decimal (3,2)

Folks,
How can I convert a real value to a decimal value? As long as the real
value is not zero I am getting an arithmetic overflow error.
I tried both convert and cast.
Does anyone have an idea?
Cheers
StephanzHI Thiere
there is no need of type costing ...it is implecit type costing betweer
real and decimal numbers.
_
________________________________________
________
"Stephan Zaubzer" wrote:

> Folks,
> How can I convert a real value to a decimal value? As long as the real
> value is not zero I am getting an arithmetic overflow error.
> I tried both convert and cast.
> Does anyone have an idea?
> Cheers
> Stephanz
>|||Hmm - i can only duplicate if the real has more than 1 digit to the left
of the decimal... (implicit or explicit conversions)
e.g., 1.234 converts fine, but 12.34 does not
Stephan Zaubzer wrote:

> Folks,
> How can I convert a real value to a decimal value? As long as the real
> value is not zero I am getting an arithmetic overflow error.
> I tried both convert and cast.
> Does anyone have an idea?
> Cheers
> Stephanz|||On Thu, 22 Sep 2005 19:11:47 +0200, Stephan Zaubzer wrote:

>Folks,
>How can I convert a real value to a decimal value? As long as the real
>value is not zero I am getting an arithmetic overflow error.
>I tried both convert and cast.
>Does anyone have an idea?
Hi Stephan,
As Trey already indicated: you will get this error for data that is
above 9.99 or below -9.99. In the notation "decimal(3,2)", the first
number (3) is the TOTAL number of positions; the second number (2) is
the number if digits after the decimal point. That leaves only one digit
for the integer part.
If your values range from -999.99 to 999.99, use DECIMAN(5,2) instead.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Convert hexadecimal value to real data type

Hi ,

I want to convert hexadecimal to numeric data type. Using directly Cast or Convert function is not working.

Please suggest an alternative how to retrieve the numeric value of binary data .

Thanks in advance

Regards

Srinivas Govada

Srinivas,

Please show us what does not work for you. The following is

fine:

declare @.n numeric(10,2)

set @.n = 1.23

select cast(@.n as varbinary(20))

-- returns 0x0A0200017B000000

select cast(0x0A0200017B000000 as numeric(10,2))

If you are trying to convert binary representations of

[float] values to [float], you can use this code:

declare @.b binary(8)

set @.b = 0xC094D954FB549F95

declare @.s bit

declare @.e smallint

declare @.m float

set @.s = case when substring(@.b,1,1) >= 0x80 then 1 else 0 end

set @.e = (substring(@.b,1,2)&32752)/16-1022

set @.m = cast(substring(@.b,6,3) as int)/2097152e0/536870912e0

+ (substring(@.b,2,4)&268435455)/536870912e0+0.5e0

select case when @.s = 1 then -1e0 else 1e0 end * @.m * power(2e0,@.e)

Steve Kass

Drew University

www.stevekass.com

Srinivas Govada@.discussions.microsoft.com wrote:

> Hi ,

>

> I want to convert hexadecimal to numeric data type. Using directly Cast

> or Convert function is not working.

>

> Please suggest an alternative how to retrieve the numeric value of

> binary data .

>

> Thanks in advance

>

> Regards

>

> Srinivas Govada

>

>

>

>