with set1 as
(select id,fkid,activity,tran_v,date,row_number() over (partition by fkid order by date desc) rn
from activitytable)
select id,fkid,activity,tran_v,date
from set1 a
where rn=1 and tran_v<>'Enroute' and not exists(select * from set1 b where b.fkid=a.fkid and b.tran_v='Enroute')
or rn<>1 and tran_v='Enroute';
Many Thanks & Best Regards,
HuaMin Chen
Marked as answer by Amy Peng - MSFT on Nov 22, 2012 01:38 AM
Arial12
Member
29 Points
160 Posts
Find last activity before selecting next
Nov 14, 2012 07:55 PM|LINK
Hi,
I have a activity table for vehicles and i need to find out last activity before activity changed to "Enroute" to each record.
table looks like this,
ID FKID activty Tran_v date
1 12 meal break v1 10/1/2012 10:00
2 13 Enroute v1 10/1/2012 10:35
3 13 at address v1 10/1/2012 11:23
4 13 leaving v1 10/1/2012 1:00
5 15 vehicle change v2 10/1/2012 7:00
6 15 misc v2 10/1/2012 9;30
7 15 waiting for direction v2 10/1/2012 10:00
8 16 Enroute v2 10/1/2012 10:30
i want result set to look like,
ID FK_ID Activity Tran_v time
1 12 meal break v1 10/1/2012 10:00
2 13 Enroute v1 10/1/2012 10:35
7 15 waiting for direction v2 10/1/2012 10:00
8 16 Enroute v2 10/1/2012 10:30
and so one.
I would actually like to keep one row where it can be seen status change before Enroute.
thanks,
wmec
Contributor
6219 Points
3217 Posts
Re: Find last activity before selecting next
Nov 15, 2012 04:20 AM|LINK
Try this
with set1 as
(select id,fkid,activity,tran_v,date,row_number() over (partition by fkid order by date desc) rn
from activitytable)
select id,fkid,activity,tran_v,date
from set1 a
where rn=1 and tran_v<>'Enroute' and not exists(select * from set1 b where b.fkid=a.fkid and b.tran_v='Enroute')
or rn<>1 and tran_v='Enroute';
HuaMin Chen