CODEWITHSUNDEEP
sql-server
saranya
saranya

Reputation: 179

SQL query for finding difference of two time and sum

I have this table in SQL Server:

UserId    Date                       Start Time              End Time
------------------------------------------------------------------------------------
20       2012-04-02 00:00:00.000     NULL                    2012-04-02 09:17:57.000
20       2012-04-02 00:00:00.000    2012-04-02 09:17:57.000  2012-04-02 09:57:55.000
20       2012-04-02 00:00:00.000    2012-04-02 09:57:55.000  2012-04-02 10:04:58.000
20       2012-04-02 00:00:00.000    2012-04-02 10:04:58.000  2012-04-02 10:21:40.000
20       2012-04-02 00:00:00.000    2012-04-02 10:22:15.000  2012-04-02 10:22:20.000
20       2012-04-02 00:00:00.000    2012-04-02 10:22:56.000  2012-04-02 10:23:33.000

I want to find the difference between start time and end time and sum up the difference hours based on date

Output required is:

UserID    Time_Duration
-----------------------
 20           1:20:20      (this is example not actuals)

Can anyone help me write a SQL query.. I have tried with the below query,

select sum(sub1.TotalSeconds / 3600) as [Hours], sum((sub1.TotalSeconds % 3600) / 60)
as [Minutes], 
sum((sub1.TotalSeconds % 3600) % 60) as [Seconds],sub1.Date
from
(
SELECT
  sub.UserID,
  sub.Date,   
sum(datepart(hour, sub.end_time-sub.start_time) * 3600) + sum(datepart(minute,        sub.end_time-sub.start_time) * 60) +
sum(datepart(second, sub.end_time-sub.start_time)) as TotalSeconds
from.......
) AS sub 
 group by sub.UserID,sub.Date,sub.start_time,sub.end_time)
  as sub1 group by sub1.Date;

I get the following result

 Hours   Minutes Seconds     Date
 3      347      515    2012-04-02 00:00:00.000

But i want to add the minutes if greater then 60 it has to hours as 3+1 hr and so on. Can any 1 help me where Im making mistake

Upvotes: 0

Views: 7529

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

Hi take a look at this:

SQLFIDDLE

It is NOT the most efficient and I believe it's ugly for a code. However put it across for you to see the steps. You may get an idea out of it. Mainly using arimethic operators to get the reusults. There are extra fields as I was using it mainly to show you the data flow. I am sure other approaches are much much efficient in terms of performance. You can try.

Query:

select q.userid, t.date,
q.h + round((q.m + (q.s/60))/60,0) as hh,
round((q.m + (q.s/60)) mod 60,0) as mm,
q.s mod 60 as ss
from 
(select t.userid,t.date, 
sum(t.hours) as h,
sum(t.minutes) as m,
sum(t.seconds) as s
from (select userid,date,
TIMEDIFF(EndTime, StartTime) as duration,
TIMESTAMPDIFF(hour,starttime,endtime) as hours,
TIMESTAMPDIFF(minute,starttime,endtime) mod 60 as minutes,
TIMESTAMPDIFF(second,starttime,endtime) mod 60 seconds
from datestimes) as t
) as q
 ;

Resutls

  USERID    DATE                            HH  MM  SS
  20        April, 02 2012 00:00:00+0000    13  4   25

Updated Query only with one nested query

Infact you may achieve this with one nested query. Apology as this whole answer is MYSQL based. So hopefully you may take the logic out to implement that using SQL Server syntax :)

select t.userid, t.date, 
(sum(t.hours) + round((sum(t.minutes) +
sum(t.seconds)/60)/60,0)) as h,
(round((sum(t.minutes) +
sum(t.seconds)/60) mod 60,0)) as m,
sum(t.seconds) mod 60 as s

from 

(select userid, date,
TIMEDIFF(EndTime, StartTime) as duration,
TIMESTAMPDIFF(hour,starttime,endtime) as hours,
TIMESTAMPDIFF(minute,starttime,endtime) mod 60 as minutes,
TIMESTAMPDIFF(second,starttime,endtime) mod 60 seconds
from datestimes) as t
 ;

Upvotes: 2

bummi
bummi

Reputation: 27377

Declare @test table(start datetime,ende datetime)
insert into @test Values ('20120101 10:00','20120101 18:00')
insert into @test Values ('20120101 10:00','20120101 18:13')
insert into @test Values ('20120101 10:00','20120102 18:25')
insert into @test Values ('20120101 10:00','20120101 18:00')
insert into @test Values ('20120101 10:00','20120101 18:00')


Select 
FLOOR(Cast(Dummy as Float)) as Days
,DATEPART(Hour,dummy) as Hours
,DATEPART(MINUTE,dummy) as Minutes
,DATEPART(Second,dummy) as Seconds
from
(
Select 
DateAdd(second, SUM(DATEDIFF(SECOND,start,ende)),Cast(0 as Datetime)) as Dummy 
from @test
) a

Upvotes: 1

veljasije
veljasije

Reputation: 7092

You can do this matematically:

Select 
    CAST(DATEDIFF(SECOND, StartDate, EndDate) / 3600 as VARCHAR) + ':' + 
    CAST((DATEDIFF(SECOND, StartDate, EndDate) % 3600) / 60 as VARCHAR) + ':' + 
    CAST(DATEDIFF(SECOND, StartDate, EndDate) % 60 as VARCHAR) as DateDifference
From YourTable

DATEDIFF function gives you difference in seconds between start and end time as integer value. Simple dividing that value with 3600, or 60, you've got hours, or minutes, and with % you've got remain values.

Upvotes: 1

Related Questions