Help on CAST function

[amorous]

Hi All,

I have two database fields

1) SolvedJobs (Datatype- Numeric, actually i get this field using the expression COUNT (*) AS SolvedJobs, so i am not sure of what data type is it, so i am guessing it is numeric)
2) TimeTaken (Datatype- Decimal)

I am trying to display the average time per job but i am facing the type mismatch error.

On my ASP i have written something like this.

Dim Avgtime
Avgtime = rsObj("TimeTaken&quot/rsObj("SolvedJobs&quot

i also tried

Avgtime = CAST(rsObj("TimeTaken&quot AS Integer)/rsObj("SolvedJobs&quot

but did not work.


Any help would be appreciated.

Thanks in advance.

VJ

 

[Chopstik]

Because datatypes in ASP are typically considered variants unless otherwise formatted, you should likely set them as double and then do the math. Something akin to the following:

Dim Avgtime
Avgtime = cdbl(rsObj("TimeTaken"))/cdbl(rsObj("SolvedJobs"))

Something to bear in mind when doing this, however, is that you need to be sure that you have numeric values in those fields before you use the cdbl() functions, else you will get type mismatch. This could also occur if the values are blank/null. If this could occur, then you would need to also take that into account.


-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

 

[onpnt]

just to throw something in there, you may want to just consider the AVG() func in SQL to get this value

may be a bit easier and more efficient

_____________________________________________________________________

_{Hakuna matata!!}

 

[amorous]

Hi guys,

thanks for your replies.

onpnt, i am trying the Avg() in SQl, but i am getting error.
The below is my query.

SELECT OwnerID, COUNT(*) AS 'SolvedJobs', CAST(SUM(Total_Time/60.0) AS Decimal(6,2)) AS 'TimeTaken', AVG(TimeTaken) AS 'AvgTime' FROM REX"

I am getting the following error.

Microsoft OLE DB Provider for SQL Server (0x80040E07)
The average aggregate operation cannot take a varchar data type as an argument.

Any Suggestions??

Thanks a lot.

VJ

 

[onpnt]

try converting the Total_Time over
Convert(numeric(6,0), Total_Time)



_____________________________________________________________________

_{Hakuna matata!!}

 

[onpnt]

the 6,0 is just a example. jsut match the format of the time

_____________________________________________________________________

_{Hakuna matata!!}

 

[Chopstik]

Is Total_Time a varchar? If so, then I think you would need to first cast it as a numeric data type and then run your mathematical operation.

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

 

[onpnt]

btw: the AS does not need ' ' around the names
eg:
SELECT OwnerID, COUNT(*) AS SolvedJobs,
Convert(numeric(20,0), Total_Time/60) AS TimeTaken,
AVG(TimeTaken) AS AvgTime FROM REX



_____________________________________________________________________

_{Hakuna matata!!}

 

[Chopstik]

I must be getting much slower in my old age... geez... C-a-n I t-y-p-e a-n-y s-l-o-w-e-r-?...

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

 

[amorous]

I tried the query as follows:

SELECT OwnerID, COUNT(*) AS SolvedJobs,
Convert(numeric(20,0), Total_Time/60) AS TimeTaken,
AVG(TimeTaken) AS AvgTime FROM REX


It am getting error as :

Invalid Column name TimeTaken



VJ

 

[onpnt]

that was jsut a example. you'll need to format that one to your needs

are you testing these in Analyzer first?

_____________________________________________________________________

_{Hakuna matata!!}

 

[amorous]

Hi Again,

the below is my exact query. I tried it in analyzer too.

*****************************
SELECT OwnerID,
COUNT(*) AS TotalRequisitions,
CAST(SUM(Total_Time/60.0) AS Decimal(6,2)) AS TimeSpent,
AVG(TimeSpent) As AvgTime
FROM REX
Group BY OwnerID
Order By TotalRequisitions DESC
*********************************

I get the error as
Invalid Column Name TimeSpent

when i remove the Avg function the query works fine.

Any ideas.

VJ

 

[Chopstik]

Am not sure on this, but can you reference a calculated variable in the same SELECT statement in another calculation?

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

 

[onpnt]

K I'm a idiot. didn't even notice that. coffee break



_____________________________________________________________________

_{Hakuna matata!!}

 

[onpnt]

try forum183

I am not able to test the statements at the tiem and don't want to misslead any further unless someone else chimes in

_____________________________________________________________________

_{Hakuna matata!!}