EXPERT RESPONSE
So far, we've developed a self-join query with a NOT EXISTS subquery that
produces the following results:
Loc_ID s1 s2
NY 01 01
NY 01 02
NY 01 03
NY 02 02
NY 02 03
NY 03 03
NY 06 06
NY 06 07
NY 07 07
NY 09 09
NY 09 10
NY 10 10
Each row represents a pair of Status 'A' SerialNos which have no
intervening Status 'B' SerialNo. However, there are multiple rows for each
t1, so let's just take the pair with
the highest t2.SerialNo for each t1.SerialNo.
select t1.Loc_ID
, t1.SerialNo as s1
, max(t2.SerialNo) as s2
from yourtable as t1
inner
join yourtable as t2
on t1.Status = t2.Status
and t1.Loc_ID = t2.Loc_ID
and t1.SerialNo <= t2.SerialNo
where t1.Status = 'A'
and not exists
( select 1
from yourtable
where Loc_ID = t1.Loc_ID
and SerialNo between t1.SerialNo
and t2.SerialNo
and Status = 'B'
)
group
by t1.Loc_ID
, t1.SerialNo
This query gets us part way to the final goal:
Loc_ID s1 s2
NY 01 03
NY 02 03
NY 03 03
NY 06 07
NY 07 07
NY 09 10
NY 10 10
Now all we have to do is select only those rows which have the lowest s1
for each s2.
select Loc_ID as Location
, min(s1) as SerNo_From
, s2 as SerNo_To
from (
select t1.Loc_ID
, t1.SerialNo as s1
, max(t2.SerialNo) as s2
from yourtable as t1
inner
join yourtable as t2
on t1.Status = t2.Status
and t1.Loc_ID = t2.Loc_ID
and t1.SerialNo <= t2.SerialNo
where t1.Status = 'A'
and not exists
( select 1
from yourtable
where Loc_ID = t1.Loc_ID
and SerialNo between t1.SerialNo
and t2.SerialNo
and Status = 'B' )
group
by t1.Loc_ID
, t1.SerialNo
) as dt
group
by Loc_ID
, s2
This gives us the desired final result:
Location SerNo_From SerNo_To
NY 01 03
NY 06 07
NY 09 10
To obtain a list of Status 'B' numbers, just switch 'A' and 'B'
in the above query.
|
