CODEWITHSUNDEEP
sqlsql-server
Simhadri
Simhadri

Reputation: 931

Calculate time difference in SQL Server?

I have table tblTask with data like below.

TID   StartTime                   Uid   WId
1     2011-06-06 09:30:00.000     10    1.5
2     2011-06-06 09:40:00.000     10    2.5
3     2011-06-06 09:50:00.000     10    1.8
4     2011-06-06 09:55:00.000     10    2.5
5     2011-06-06 10:30:00.000     10    1.5
6     2011-06-06 11:30:00.000     11    3.0

I need to write to query to calculate sum(Wid) based on time difference of each Starttime with another starttime having time difference 1 hour or close to 1 hr.

For example take first start time(2011-06-06 09:30:00.000); the nearest start time having <=1 hr is (2011-06-06 10:30:00.000) and the SUM(Wid) is 1.5+2.5+1.8+2.5+1.5 = 9.8. Likewise I need to calculate for all rows.

Desired output will be:

TID    StartTime                 EndTime                    TimeDIff(Min) Uid    WId
1      2011-06-06 09:30:00.000   2011-06-06 10:30:00.000    60            10     9.8
2      2011-06-06 09:40:00.000   2011-06-06 10:30:00.000    50            10     8.3

Upvotes: 1

Views: 13679

Answers (2)

Ankush
Ankush

Reputation: 1

This block of code will give you the time difference between StartTime and EndTime

DECLARE @STARTDATE DATETIME = '10:15:45'
DECLARE @ENDDATE DATETIME = '14:00:15'
DECLARE @24DATE DATETIME
SET @24DATE = '23:59:59.000'

IF (@STARTDATE > @ENDDATE)
  SELECT DATEADD(SECOND, 1, CONVERT(TIME(0), (@24DATE -(@ENDDATE - @STARTDATE))))
ELSE
  SELECT CONVERT(TIME(0), ((@ENDDATE - @STARTDATE)))

Upvotes: 0

bobs
bobs

Reputation: 22184

Does this give you what you need?

SELECT t1.TID, min(t1.StartTime) AS StartTime, MAX(t2.StartTime) AS EndTime,
    MAX(DATEDIFF(MI, t1.StartTime, t2.StartTime)) AS [TimeDiff(Min],
    t1.Uid, SUM(t2.WId) AS WId
FROM MyTable t1
JOIN MyTable t2 on datediff(MI, t1.starttime, t2.StartTime) BETWEEN 0 AND 60
GROUP BY t1.TID, t1.Uid
ORDER BY t1.TID

Upvotes: 2

Related Questions