Dividing two columns whilst using Cast & Union

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

Dividing two columns whilst using Cast & Union

Author

Topic

crazyco
Starting Member

30 Posts

Posted - 2010-11-26 : 14:36:04

I would like to divide the Duration column by the Outcome column and wondered how I could do this whilst using cast and a union. So far my query looks like this:

SELECT forenames + ' ' + surnames as StaffName, Duration, Outcome
FROM Staff_Tasks st LEFT JOIN Staff s ON st.StaffID = s.StaffID
WHERE job_id IN (SELECT job_id FROM Jobs WHERE task_id = 1)

UNION

SELECT 'Total' as StaffName,
CAST(CAST((SUM(CAST(SUBSTRING(Duration, 0, CHARINDEX(':',Duration)) AS INT))*60 +
SUM(CAST(RIGHT(Duration,2) AS INT)))/60 AS VARCHAR(10)) + ':' + CAST((SUM(CAST(RIGHT(Duration,2) AS INT)))%60 AS VARCHAR(10)) AS VARCHAR(10)) AS TotalDuration,
SUM(CAST(Outcome AS int)),FROM Staff_Tasks st LEFT JOIN Staff s ON st.StaffID = s.StaffID
WHERE job_id IN (SELECT job_id FROM Jobs WHERE task_id = 1)

I want to be able to calculate the Average per Hour and wondered if I had to add in something like Sum(CAST(Duration))/(SUM(Outcome)) as AveragePerHour? and also Average per minute as Sum(CAST(Duration))/(SUM(Outcome)*60)

Any help would be much appreciated

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 15:28:08

What type of data does duration have in it? is it a string containing the time?

What is in the outcome field?

Why are you casting the bejesus out of everything? you shouldn't have to do that....

If the field types aren't datetime or numeric, there is a flaw in the table design.

Show some sample data and desired result..

Poor planning on your part does not constitute an emergency on my part.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-26 : 15:31:24

Whilst?

You sure you're a dba?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-27 : 01:36:55

what are datatypes of Outcome and Duration?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crazyco
Starting Member

30 Posts

Posted - 2010-12-06 : 04:44:05

The datatypes for Outcome and Duration are both varchar(5).

A sample outcome is:

Jim Smith 3:00 1370
Jim Smith 3:75 1291
Total 7:15 2661

I am new to this, have never used cast before but this is what I was told it had to be done like.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources