with CTE as
(
select
vehicle,
Date_Time,
Activity,
ROW_NUMBER() OVER(PARTITION BY vehicle ORDER BY Date_Time) as RowNum
from MyTable
)
select
c.vehicle,
a.Date_time as Date_Time_Ant,
c.Date_time,
DATEDIFF(minute, a.Date_time, c.Date_Time) as Dif_Minutes,
a.Activity as Activity_Ant,
c.Activity
from CTE as c
left join CTE as a
on a.vehicle = c.vehicle and
a.RowNum = c.RowNum - 1
select
s.vehicle,
s.Date_time,
s.Date_Time_Next,
DATEDIFF(MINUTE, s.Date_time, s.Date_Time_Next) as Dif_Minutes,
s.Activity,
s.reason
from
(
select
t.vehicle,
t.Date_time,
t.Activity,
t.reason,
(select top 1 n.Date_Time
from MyTable as n
where n.vehicle = t.vehicle and
n.Date_time > t.Date_time
order by n.Date_time) as Date_Time_Next
from MyTable as t
) as s
order by
vehicle,
Date_time
so, i have to calcuate datediff for how long ups1 was out of service and when it became avialable per day.
avl is when vehicle was back in service.
ups1
don't know how i can do datediff on a same column.
can someone help please?
You can do a datediff on the same column by doing a self join, but that won't help you here. The data you presented shows two consecutive record for unavailable and only one record of becoming available. Plus, while you didn't show it, the same thing could
happen again in the afternoon. Plus a truck could become unavailable one day and stay unavailable for a week.
I'm not going to try to solve this, but will suggest that you thoroughly test any answers presented to you.
Marked as answer by Chen Yu - MSFT on Nov 13, 2012 02:31 AM
Arial12
Member
29 Points
161 Posts
datediff on a same column
Nov 05, 2012 08:32 PM|LINK
Hi,
My table looks tike this:
Activity Date_time vehicle reason
OOS 10/1/2012 10:10:34 UPS1 road block
OOS 10/1/2012 10:30:45 ups1 direction issue
avl 10/1/2012 10:20:45 ups1
avl 10/1/2012 11:00:10
oos 10/1/2012 8:07:10 ups2 road block
avl 10/1/2012 9:10:10 ups2
so, i have to calcuate datediff for how long ups1 was out of service and when it became avialable per day.
avl is when vehicle was back in service.
ups1
don't know how i can do datediff on a same column.
can someone help please?
spapim
Contributor
2676 Points
393 Posts
Re: datediff on a same column
Nov 05, 2012 08:56 PM|LINK
Hi,
Try something like this:
with CTE as ( select vehicle, Date_Time, Activity, ROW_NUMBER() OVER(PARTITION BY vehicle ORDER BY Date_Time) as RowNum from MyTable ) select c.vehicle, a.Date_time as Date_Time_Ant, c.Date_time, DATEDIFF(minute, a.Date_time, c.Date_Time) as Dif_Minutes, a.Activity as Activity_Ant, c.Activity from CTE as c left join CTE as a on a.vehicle = c.vehicle and a.RowNum = c.RowNum - 1Hope it's useful.
www.imobiliariasemsuzano.com.br
Arial12
Member
29 Points
161 Posts
Re: datediff on a same column
Nov 05, 2012 08:58 PM|LINK
we are using SQL server 2000.
spapim
Contributor
2676 Points
393 Posts
Re: datediff on a same column
Nov 05, 2012 09:18 PM|LINK
Try:
select s.vehicle, s.Date_time, s.Date_Time_Next, DATEDIFF(MINUTE, s.Date_time, s.Date_Time_Next) as Dif_Minutes, s.Activity, s.reason from ( select t.vehicle, t.Date_time, t.Activity, t.reason, (select top 1 n.Date_Time from MyTable as n where n.vehicle = t.vehicle and n.Date_time > t.Date_time order by n.Date_time) as Date_Time_Next from MyTable as t ) as s order by vehicle, Date_timeHope it's useful.
www.imobiliariasemsuzano.com.br
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: datediff on a same column
Nov 05, 2012 09:51 PM|LINK
You can do a datediff on the same column by doing a self join, but that won't help you here. The data you presented shows two consecutive record for unavailable and only one record of becoming available. Plus, while you didn't show it, the same thing could happen again in the afternoon. Plus a truck could become unavailable one day and stay unavailable for a week.
I'm not going to try to solve this, but will suggest that you thoroughly test any answers presented to you.