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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Sum(DateTime) not working... trying something else

Author  Topic 

rternier
Starting Member

44 Posts

Posted - 2007-10-10 : 16:30:00
Hey guys,

I'm looking for a way to select a SUM(DateTime) in a select row select statement but I ran into issues.

I since turned it into this:


DECLARE @p_intUserID INT, @p_datStartDate DATETIME, @p_datEndDate DATETIME, @p_intPlayBackUserID INT

SET @p_intPlayBackUserID = 3
SET @p_datStartDate = '2007-10-05 08:55:40.000'
SET @p_datEndDate = '2007-10-05 08:55:55.000'


SELECT U.vchSVImage, U.vchFirstName + ' ' + U.vchLastName AS 'FullName', U.vchSVUnitName, COUNT(GPS.intGPSdataID) AS 'Segments',
CONVERT(VARCHAR(10), SUM(DATEPART(hh,datTimeStamp))) + ' : ' + CONVERT(VARCHAR(10),SUM(DATEPART(mi, datTimeStamp))) AS 'Total Time'
FROM tblGPSData GPS
INNER JOIN tblUsers U ON GPS.intUserID = U.intUserID
WHERE U.intUserID = @p_intPlayBackUserID
AND (GPS.datTimeStamp >= @p_datStartDate AND GPS.datTimeStamp <= @p_datEndDate)
GROUP BY U.vchSVImage, U.vchFirstName, U.vchLastName, U.vchSVUnitName



(To those that helped me with another issue, this is a different query :P )

This works.... to a point. The result set I get is:

vchSVUnitName Segments Total Time
------------- ----------- -----------------------
MyUnit 3 24 : 165


Is there any way in a select to get that TotalTime to a proper time?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-10 : 16:40:09
You should not be doing all this string formatting and converting ... math is all you need. Just get the date difference in whatever unit you need to round your answer to (i.e., hours or minutes or seconds) as integers, and do a SUM() of those integers values. Then simply do basic math to convert that total seconds/minutes/hours/whatever to the values that you need.

Here's an example to help you with some of the math, if necessary:


declare @s int
declare @start datetime, @end datetime

set @start = '1/1/2007 4:20:12 PM'
set @end = '1/1/2007 6:12:23 PM'

set @s = datediff(second,@start,@end)

select @end - @start as DateTimeDifference

select @s as TotalSeconds

select @s / 3600 as Hours, (@s % 3600) / 60 as Minutes, (@s % 60) as Seconds

-- return a true date/time value from your seconds:

select dateadd(second,@s, 0) as BackToDateTime


DateTimeDifference
------------------------------------------------------
1900-01-01 01:52:11.000

(1 row(s) affected)

TotalSeconds
------------
6731

(1 row(s) affected)

Hours Minutes Seconds
----------- ----------- -----------
1 52 11

(1 row(s) affected)

BackToDateTime
------------------------------------------------------
1900-01-01 01:52:11.000

(1 row(s) affected)


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 16:41:05
"Is there any way in a select to get that TotalTime to a proper time?"

DATEADD(Hour, SUM(DATEPART(hh,datTimeStamp)), DATEADD(Minute, SUM(DATEPART(mi, datTimeStamp)), 0))

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-10 : 16:46:49
another example to play with, summing from a table of start/end dates:



select
TotalSeconds / 3600 as Hours, (TotalSeconds % 3600) / 60 as Minutes,
TotalSeconds % 60 as Seconds, DateAdd(second, TotalSeconds,0) as DateTimeTotal
from
(
select sum(datediff(second, startdate, enddate)) as TotalSeconds
from
(
select '1/1/2007 4:20:12 PM' as startdate, '1/1/2007 6:12:23 PM' as enddate union all
select '1/2/2007 3:12:53 AM' as startdate, '1/2/2007 4:54:00 AM' as enddate union all
select '1/3/2007 12:32:01 AM' as startdate, '1/3/2007 8:02:19 AM' as enddate
) x
) x



Hours Minutes Seconds DateTimeTotal
----------- ----------- ----------- ------------------------------------------------------
11 3 36 1900-01-01 11:03:36.000



very, very simple and easy and you are doing true math, not string conversions .... if you need Hours/Minutes/Seconds, there they are. You can get the total number of days, if applicable, as well, using the same basic formulas.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

rternier
Starting Member

44 Posts

Posted - 2007-10-10 : 16:56:19
quote:
Originally posted by Kristen

"Is there any way in a select to get that TotalTime to a proper time?"

DATEADD(Hour, SUM(DATEPART(hh,datTimeStamp)), DATEADD(Minute, SUM(DATEPART(mi, datTimeStamp)), 0))

Kristen



Kristen thank you for the help again :)

That works but the output comes out as: 1900-01-02 02:45:00.000

But, thanks to the other examples I can work with that :)
-----

now for another question, is the query I wrote with your modification a good way of doing it? I'm not a wiz on SQL so I don't know how expensive Conversions are.


----
[url]http://weblogs.asp.net/rternier/[/url]
Killer ASP.NET ninja coding monkeys do exist!
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-10 : 19:00:30
rternier -- did you see anything that I wrote ? I showed you about 3 or 4 different ways to easily do what you need...

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 00:38:58
"That works but the output comes out as: 1900-01-02 02:45:00.000"

The time is base 01-01-1900.

So 1900-01-02 is one day later, plus 2 hours 45 minutes.

Just subtract 1900-01-01 if you want some other units:

SELECT DATEDIFF(Hours, '19000101', '19000102 02:45:00.000')

etc.

"is the query I wrote with your modification a good way of doing it?"

I would have SUM'd the number of Minutes, and then divided by 60 for the Hours and Modulus 60 for the minutes.

Which is what Jeff did for you already.

Kristen
Go to Top of Page
   

- Advertisement -