Using SQL Server 2000
My table column values are datetime
Column1 Column2
1/1/1900 12:20:45 1/1/1900 23:22:25
1/1/1900 09:00:00 1/1/1900 18:10:30
and so on…
12:20:45 means (HH:MM:SS)`
Here I want to take a total of column 1 and total of column 2
Expected Output
column1 column2 1/1/1900 21:20:45 1/1/1900 41:32:55
and so on…
Note Hours, minutes and second should not exceeded 60
How to make a sum query in Sql?
Need Query Help.
This sample should illustrate what you need to do:
declare @Base datetime set @base = '1900-01-01'
declare @first datetime set @first = '1900-01-01 12:15:00'
declare @other datetime set @other = '1900-01-01 01:00:00'
select DATEADD(s, SUM(DATEDIFF(s, @base, dt)),@base)
from
(
select @first as dt
union select @other
) t
Output from QA on my Sql Server 2000 box is:
1900-01-01 13:15:00.000
Expected Output column1 column2 1/1/1900 21:20:45 1/1/1900 41:32:55
That won't work, since the DATETIME in SQL Server 2000 is limited to 24 hours.
So in order to sum up your hours, I guess you'll have to break it up into hours, minutes, seconds as INT columns, and then do something like
UPDATE dbo.YourTable
SET SumHours = DATEPART(HOUR, CONVERT(DATETIME, Column1, 100)),
SumMinutes = DATEPART(MINUTE, CONVERT(DATETIME, Column1, 100)),
SumSeconds = DATEPART(SECOND, CONVERT(DATETIME, Column1, 100))
WHERE........
(and the same for column2, also).
I don't really see any other way. Also, it's up to you to convert minutes > 60 into hours+minutes and seconds > 60 into minutes+seconds.
Marc
My assumption: your datetimes are tracking elapsed time. For example, your first value of "1/1/1900 12:20:45" means that 12 hours, 20 minutes, and 45 seconds elapsed during (whatever). Given that, you want to sum all these elapsed times, producing a "total elapsed time".
Here's one way to do it. (There may be others--there always seems to be a better way to implement time mathematics.) I'm querying only one column to make the example clearer, and to be properly paranoid. I'm further assuming the date portion is (a) completely irrelevant and (b) could be set to anything. [And of course, if you're using SQL 2008, you want to set the column with the Time datatype.] The last column [5] is the one you want; the others explain how I got there.
select
MyColumn -- [1]
,dateadd(dd, -datediff(dd, 0, MyColumn), MyColumn) -- [2]
,datediff(ss, 0, dateadd(dd, -datediff(dd, 0, MyColumn), MyColumn)) -- [3]
from MyTable
select
sum(datediff(ss, 0, dateadd(dd, -datediff(dd, 0, MyColumn), MyColumn))) -- [4]
,dateadd(ss, sum(datediff(ss, 0, dateadd(dd, -datediff(dd, 0, MyColumn), MyColumn))), 0) -- [5]
from MyTable
[1] shows the initial value for each row.
[2] converts every column's date porition to 'Jan 1, 1900'. If you are dead-certain that your dates will always be the same, you don't need to factor this in.
[3] This calculates the number of seconds elapsed between midnight and the recorded time.
[4] This sums the seconds.
[5] This adds the elapsed time (in seconds) back on to 'Jan 1, 1900'.
From there, use whatever standard formatting technique you use to display results as elapsed time (don't forget to factor in dates, since elapsed time may exceed 24 hours). This technique will be accurate for any unit of time (here, as you wanted, I used seconds), but note that if the total number of units of elapsed time exceeds (2^31 - 1), as it will for milliseconds, you may need to cast to bigints.
Hmm, just thought: what if a single elapsed time is greater than 24 hours? In that case the date of your datetime is significant, and has to represent the "starting date". Worse, we cannot assume that the earliest date from the sample pool is the base date, since all your elapsed times could be for 24 hours or more. To do this you need some way to determine the "base date" to calculate from. As there is no indication within your question of what the base date could be, I shall as a final assumption presume that this is not a problem--that all your elapsed times will be under 24 hours.
SELECT DATEADD(s, 0, SUM(DATEDIFF(s, 0, Column1))) AS SUMColumn1
,DATEADD(s, 0, SUM(DATEDIFF(s, 0, Column2))) AS SUMColumn2
FROM tbl
this will give you what you are after:
CREATE TABLE #Times
(
Column1 datetime
,Column2 datetime
)
INSERT INTO #Times VALUES ('1/1/1900 12:20:45', '1/1/1900 23:22:25')
INSERT INTO #Times VALUES ('1/1/1900 09:00:00', '1/1/1900 18:10:30')
SELECT
CONVERT(varchar(10),dateadd(day,datediff(day,0,dt2.MinColumn1),0),101)
+' '
+CASE WHEN TotalSecondsColumn1/3600>60 then '60' ELSE CONVERT(varchar(2),TotalSecondsColumn1/3600) END
+':'
+CASE WHEN (TotalSecondsColumn1-(TotalSecondsColumn1/3600*3600))/60 >60 then '60' ELSE CONVERT(varchar(2),(TotalSecondsColumn1-(TotalSecondsColumn1/3600*3600))/60) END
+':'
+CASE WHEN TotalSecondsColumn1-((TotalSecondsColumn1/3600*3600)+(((TotalSecondsColumn1-(TotalSecondsColumn1/3600*3600))/60)*60)) >60 then '60' ELSE CONVERT(varchar(2),TotalSecondsColumn1-((TotalSecondsColumn1/3600*3600)+(((TotalSecondsColumn1-(TotalSecondsColumn1/3600*3600))/60)*60))) END
AS Column1
,CONVERT(varchar(10),dateadd(day,datediff(day,0,dt2.MinColumn2),0),101)
+' '
+CASE WHEN TotalSecondsColumn2/3600>60 then '60' ELSE CONVERT(varchar(2),TotalSecondsColumn2/3600) END
+':'
+CASE WHEN (TotalSecondsColumn2-(TotalSecondsColumn2/3600*3600))/60 >60 then '60' ELSE CONVERT(varchar(2),(TotalSecondsColumn2-(TotalSecondsColumn2/3600*3600))/60) END
+':'
+CASE WHEN TotalSecondsColumn2-((TotalSecondsColumn2/3600*3600)+(((TotalSecondsColumn2-(TotalSecondsColumn2/3600*3600))/60)*60)) >60 then '60' ELSE CONVERT(varchar(2),TotalSecondsColumn2-((TotalSecondsColumn2/3600*3600)+(((TotalSecondsColumn2-(TotalSecondsColumn2/3600*3600))/60)*60))) END
AS Column1
FROM #Times t
INNER JOIN (SELECT
SUM(DATEDIFF(second,CONVERT(datetime,LEFT(CONVERT(char(23),Column1,121),10)),Column1)) AS TotalSecondsColumn1
,SUM(DATEDIFF(second,CONVERT(datetime,LEFT(CONVERT(char(23),Column2,121),10)),Column2)) AS TotalSecondsColumn2
FROM #Times
) dt ON 1=1
INNER JOIN (SELECT
MIN(Column1) AS MinColumn1
,MIN(Column2) AS MinColumn2
FROM #Times
) dt2 ON 1=1
WHERE t.Column1=dt2.MinColumn1
OUTPUT:
Column1 Column1
------------------- -------------------
01/01/1900 21:20:45 01/01/1900 41:32:55
(1 row(s) affected)
However, this will accurately sum the times, incrementing the day, month, and year as well:
SELECT
DATEADD(second,dt.TotalSecondsColumn1,dateadd(day,datediff(day,0,dt2.MinColumn1),0)) AS Column1
,DATEADD(second,dt.TotalSecondsColumn2,dateadd(day,datediff(day,0,dt2.MinColumn2),0)) AS Column2
FROM #Times t
INNER JOIN (SELECT
SUM(DATEDIFF(second,CONVERT(datetime,LEFT(CONVERT(char(23),Column1,121),10)),Column1)) AS TotalSecondsColumn1
,SUM(DATEDIFF(second,CONVERT(datetime,LEFT(CONVERT(char(23),Column2,121),10)),Column2)) AS TotalSecondsColumn2
FROM #Times
) dt ON 1=1
INNER JOIN (SELECT
MIN(Column1) AS MinColumn1
,MIN(Column2) AS MinColumn2
FROM #Times
) dt2 ON 1=1
WHERE t.Column1=dt2.MinColumn1
OUTPUT:
Column1 Column2
----------------------- -----------------------
1900-01-01 21:20:45.000 1900-01-02 17:32:55.000
(1 row(s) affected)