November 11, 2006 at 12:33 am
Hi,
In the following table with test data there are two tickets with their associated start work time and end work time.
Could you please advise on how to write a query to return the sum of work time in minutes for each ticket and code
id ticket_num code start_work_dt end_work_dt
1 x1 ACC 2006-11-10 01:00:00 2006-11-10 01:00:00
2 x1 SWO 2006-11-10 12:00:00 2006-11-10 12:10:00
3 x1 SWO 2006-11-10 13:00:00 2006-11-10 13:10:00
4 x2 ACC 2006-11-10 14:00:00 2006-11-10 14:00:00
5 x1 SWO 2006-11-10 20:00:00 2006-11-10 20:10:00
6 x2 SWO 2006-11-10 22:00:00 2006-11-10 22:05:00
From the above data the query should return the following
ticket_num code minutes
x1 ACC 0
x1 SWO 30
x2 ACC 0
x2 SWO 5
Thanks in advance for your help.
Tuan
November 11, 2006 at 3:02 am
SUM(DATEDIFF(n, Start, End))
_____________
Code for TallyGenerator
November 12, 2006 at 8:00 am
select ticket_num, code, minutes = sum(datediff(minute, start_work_dt, end_work_dt))
from table
group by ticket_num, code
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply