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.
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 INTSET @p_intPlayBackUserID = 3SET @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 GPSINNER JOIN tblUsers U ON GPS.intUserID = U.intUserIDWHERE U.intUserID = @p_intPlayBackUserIDAND (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 intdeclare @start datetime, @end datetimeset @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 DateTimeDifferenceselect @s as TotalSecondsselect @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 BackToDateTimeDateTimeDifference ------------------------------------------------------ 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) - Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
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 |
|
|
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 DateTimeTotalfrom( 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) xHours 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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
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.000But, 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! |
|
|
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...- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
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 |
|
|
|
|
|
|
|