Function - 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

Function

Author

Topic

joel louis
Starting Member

4 Posts

Posted - 2009-05-12 : 18:08:55

H Gurus,

I have a table with Name (few names), Date and separate colum for Time(in minute)
How can I write a query for two different name with the total time in both minute and hour between a certain date?
I have an idea like:

SELECT Name, SUM(Time) AS T_Min FROM Table
WHERE Name='Joe Louis' OR 'Steve Louis'
AND Date BETWEEN'2/12/2008' AND '1/01/2009'
GROUP BY Name, T_Min
I should have a table with Name, T_Min and T_Hour but I dont have a clue how to convert and insert a new colum T_Hour which is the product of T_Min/60.

BIG Thanks in advance

Joel

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-12 : 19:02:23

Perhaps some sample data and expected output would help, but maybe this will help you:

INSERT
	DestinationTable
SELECT 
	Name, 
	SUM(Time) % 60 AS T_Min ,
	SUM (Time) / 60 AS T_Hout

FROM 
	Table
WHERE 
	Name='Joe Louis' 
	OR 'Steve Louis'
	AND Date BETWEEN'2/12/2008' AND '1/01/2009'
GROUP BY 
	Name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 09:55:08

whats the datatype of T_Min and T_Hour columns?

kishore_pen
Starting Member

49 Posts

Posted - 2009-05-13 : 10:01:45

sum (time) % 60 -> gives int value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 10:08:53

quote:
Originally posted by kishore_pen

sum (time) % 60 -> gives int value.

so what all should you get as output?

joel louis
Starting Member

4 Posts

Posted - 2009-05-13 : 13:35:29

Thanks All,
There is only one table called Time_summary
colum 1 - Date (dd/mm/yyyy 00:00)
colum 2 - Name (First Name Last Name)
colum 3 - Lap (Number)
Colum 4 - Total_time (number in minutes)

The result should be something like

Colum 1 - Name ( first name Last name)
Colum 2 - Total time in Minute
Colum 3 - Total time in hour

Is this mean according to Lamprey Ineed to create a new table for the result?

Thanks again all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 13:38:55

so you want time taken between consecutive laps?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-13 : 13:49:56

You should read this link, then get back to us: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 14:33:31

Beware of operator presedence!

WHERE	(Name = 'Joe Louis' OR Name = 'Steve Louis')
	AND Date BETWEEN '2/12/2008' AND '1/01/2009'

E 12°55'05.63"
N 56°04'39.26"

joel louis
Starting Member

4 Posts

Posted - 2009-05-13 : 15:08:02

Hi Visakh16,

I can just write:

SELECT
Name,
SUM(Time)AS TotalMinute
FROM
Time_Summary
WHERE
(Name='joel louis' AND 'steve louis')
AND
DATE BETWEEN '2/12/2008' AND '1/01/2009'
GROUP BY Name

But then I need to add the TotalHour this is where I am stuck, because cant figure out how to add another colum with time in hour.

Rgs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-13 : 15:21:09

We are still guessing as you have not provided any sample data or expected output, but is this what you want:

SELECT
Name, 
SUM(Time)AS TotalMinute,
SUM(Time) / 60.0 AS TotalHour
FROM
Time_Summary
WHERE
(Name='joel louis' AND 'steve louis') -- Is that right? I don't see how you'd get any rows back
AND
DATE BETWEEN '2/12/2008' AND '1/01/2009'
GROUP BY Name

joel louis
Starting Member

4 Posts

Posted - 2009-05-13 : 16:38:42

Thank you very much Lamprey, this exactly what I was looking for, in red mean that 'TotalHour' will be created in the query result.
I will try next time to provided more details.

Thank you very much to all of you..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources