sql - Find subsequent records that aren't active -
i've got table of dd information , need select records cancelled don't have subsequent active record
i have tried
select d1.ddireference ddi d1 left outer join ddi d2 on (d1.ddireference = d2.ddireference , d1.transactionid < d2.transactionid) inner join [tm113 file jo check] tm on tm.[ddi reference] = d1.ddireference d1.ddstatus = 'can' , d2.ddstatus <> 'act'
but return me ddireference of record cancelled, cancelled, cancelled, active.
i want return records cancelled no active.
thanks
darren
i'm not entirely sure understanding question correctly, query gets cancelled records no active record higher transactionid
exists. note original query, if multiple records status can
exist, 1 row in results each of records. use select distinct
avoid in case.
select d1.ddireference ddi d1 inner join [tm113 file jo check] tm on tm.[ddi reference] = d1.ddireference d1.ddstatus = 'can' , not exists( select * ddi d2 d2.ddireference = d1.ddireference , d2.transactionid > d1.transactionid , d2.ddstatus = 'act')
Comments
Post a Comment