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