Creating a view with a Decimal column

I'm working with a database that logs user activity.  The database was created by the application that does the logging.  One of the fields is a VARCHAR field that contains only numeric data.  It represents the number of secends a user was logged on dring a session.

I'm trying to create a view that I'll report against.  In this view, I'm including the user name, the time the event was logged, the original elapsed time (in seconds) and a field calculated from elapsed time to give me the elapsed time in minutes (elapsed_time/60).  The problem I have is that the calculated field is created as an integer, and therefore the result is rounded.  I expect that I'll be using the data to provide a roll-up summary, so the rounding errors can become significant over time.  I'd like to know how I might make this calculated field a decimal field.  Am I approaching this correctly?

Below is the SQL code that I used to creat the view as it is now.  Any help would be appreciated.


USE CiscoACS


go
DROP VIEW time_detail
go
CREATE VIEW time_detail
AS
SELECT LoggedAt,User_Name,elapsed_time,(elapsed_time/60) AS elapsed_minutes
FROM dbo.tacacsAccounting
WHERE (elapsed_time is NOT NULL)
corwin_rangerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Lori99Connect With a Mentor Commented:
How about this.

CREATE VIEW time_detail
AS
SELECT LoggedAt,User_Name,elapsed_time,convert(decimal(10,2),elapsed_time)/60 AS elapsed_minutes
FROM dbo.tacacsAccounting
WHERE (elapsed_time is NOT NULL)

You would need to change the decimal field definition to whatever is appropriate for your field.
0
 
Shailesh15Commented:
Since it is varchar You should be using...

 ROUND(CAST(elapsed_time AS float) / 60, 2) AS  elapsed_minutes
0
 
arbertCommented:
Of course, the use of CAST/CONVERT if the varchar data ends up having non-numeric data in the column.  You should take that into account and maybe wrap an ISNUMERIC function inside the cast/convert.

Brett
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
HilaireCommented:
Wouldn't a single field with hh:mm:ss format fit your needs better ?

SELECT LoggedAt,User_Name,elapsed_time,
select convert(varchar,dateadd(second, cast(elapsed_time as int), 0),108) as elapsed_hhmiss
FROM dbo.tacacsAccounting
WHERE (elapsed_time is NOT NULL)

can be truncated to mi:ss if you prefer

SELECT LoggedAt,User_Name,elapsed_time,
select right(convert(varchar,dateadd(second, cast(elapsed_time as int), 0),108),5) as elapsed_miss
FROM dbo.tacacsAccounting
WHERE (elapsed_time is NOT NULL)

HTH

Hilaire
0
 
corwin_rangerAuthor Commented:
Thanks to everyone for all the great info.  I'm new tothe whole development thing and even newer to the SQL thing.  I'm really just now getting a handle on what it CAN do, much less how to do it all.
0
 
Lori99Commented:
You're welcome, corwin_ranger.  Glad to help.  Thanks for the points!
0
All Courses

From novice to tech pro — start learning today.