Calculate Time between pairs of records (TimeTicket)

I have a table like this
Job, EventType,TimeEvent
A,Start,12/10/2005 10:00
A,End, 12/10/2005 10:30
B,Start, 12/10/2005 10:31
B,End, 12/10/2005 11:45
and so on...
Which is the way to have a quey result with
Job, JobTime (in minutes)
A, 30
B, 74
ThanksDamiano wrote:

> I have a table like this
> Job, EventType,TimeEvent
> A,Start,12/10/2005 10:00
> A,End, 12/10/2005 10:30
> B,Start, 12/10/2005 10:31
> B,End, 12/10/2005 11:45
> and so on...
> Which is the way to have a quey result with
> Job, JobTime (in minutes)
> A, 30
> B, 74
> Thanks
SELECT Job, DATEDIFF(mi, MIN(TimeEvent), MAX(TimeEvent)) AS Jobtime
FROM table
GROUP BY Job
This needs to have the Job completed for "usable" results.
HTH,
Pierre
/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news|||"Pierre Albisser" wrote:

> SELECT Job, DATEDIFF(mi, MIN(TimeEvent), MAX(TimeEvent)) AS Jobtime
> FROM table
> GROUP BY Job
> This needs to have the Job completed for "usable" results.
> --
> HTH,
> Pierre
> /"\ ASCII Ribbon Campaign
> \ /
> X Against HTML
> / \ in e-mail & news
>
Thanks, this was usefull
but now I have some more diffucult (for me)
The table (TimeTicket) now is like this:
Job,Operator,EventType,EndOperationType,
TimeEvent
A;X;Start;null;12/10/2005 10:00
A;X;End;OperType1;12/10/2005 10:30
A;X;Start;null;12/10/2005 10:31
A;X;End;OperType1;12/10/2005 11:45
A;Y;Start;null;12/10/2005 13:00
A;Y;End;OperType2;12/10/2005 14:00
B;X;Start;null;12/10/2005 13:00
B;X;End;OperType2;12/10/2005 13:15
table is sort by Job,Operator,TimeEvent
the result should be
Job;Operator;EndOperationType;JobTime
A;X;OperType1;30
A;X;OperType1;76
A;Y;OperType2;60
B;X;OperType2;15
I think in this case is necessary to parse the table row by row,
to take the start time from the first,
and the oder data from the second,
calculate the time and the fields to ouput.
Probably I need a function
select * from CalculateTimeTicket()
Thanks again.|||First, get your start and end dates...
select job,
operator,
eventtype,
operatortype,
starttime = timeevent,
endtime = ( select top 1 timeevent
from timeticket te
where te.job = t.job
and te.operator = t.operator
and te.operatortype = t.operatortype
and te.eventtype = 'End'
and te.timeevent > t.timeevent
)
from timeticket t
where eventtype = 'start'
then you can do a datediff...
select job,
operator,
operatortype
jobtime = datediff( minute, starttime, endtime )
from (
select job,
operator,
starttime = timeevent,
endtime = ( select top 1 timeevent
from timeticket te
where te.job = t.job
and te.operator = t.operator
and te.eventtype = 'End'
and te.timeevent > t.timeevent
)
from timeticket t
where eventtype = 'start' ) as d
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Damiano" <Damiano@.discussions.microsoft.com> wrote in message
news:AC8308CB-D424-4387-B04C-15DC8DA515DB@.microsoft.com...
> "Pierre Albisser" wrote:
>
> Thanks, this was usefull
> but now I have some more diffucult (for me)
> The table (TimeTicket) now is like this:
> Job,Operator,EventType,EndOperationType,
TimeEvent
> A;X;Start;null;12/10/2005 10:00
> A;X;End;OperType1;12/10/2005 10:30
> A;X;Start;null;12/10/2005 10:31
> A;X;End;OperType1;12/10/2005 11:45
> A;Y;Start;null;12/10/2005 13:00
> A;Y;End;OperType2;12/10/2005 14:00
> B;X;Start;null;12/10/2005 13:00
> B;X;End;OperType2;12/10/2005 13:15
> table is sort by Job,Operator,TimeEvent
> the result should be
> Job;Operator;EndOperationType;JobTime
> A;X;OperType1;30
> A;X;OperType1;76
> A;Y;OperType2;60
> B;X;OperType2;15
> I think in this case is necessary to parse the table row by row,
> to take the start time from the first,
> and the oder data from the second,
> calculate the time and the fields to ouput.
> Probably I need a function
> select * from CalculateTimeTicket()
> Thanks again.|||Hi Damiano,
how about this one:
SELECT Job, Operator, MAX(EventType) AS EventType, DATEDIFF(mi,
MIN(TimeEvent), MAX(TimeEvent)) AS Jobtime
FROM table
GROUP BY Job, Operator
This should do, as 'Operator' > null (as any value which is not null
would).
HTH,
Pierre
/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news