Item	Rundate	         Stddate	Status
P1	03-Nov-12	03-Nov-12	A
P1	04-Nov-12	04-Nov-12	D
P2      04-Nov-12	03-Nov-12	A 

create table Item_tbl
(
Item  varchar2(10),
rundate date,
stddate date,
status varchar2(1)
);

Insert into item_tbl values('P1',to_date('03/11/2012','DD/MM/YYYY'),to_date('03/11/2012','DD/MM/YYYY'),'A');

Insert into item_tbl values('P1',to_date('04/11/2012','DD/MM/YYYY'),to_date('04/11/2012','DD/MM/YYYY'),'D');

Insert into item_tbl values('P2',to_date('04/11/2012','DD/MM/YYYY'),to_date('03/11/2012','DD/MM/YYYY'),'A');

Insert into item_tbl values('P3',to_date('04/11/2012','DD/MM/YYYY'),to_date('03/11/2012','DD/MM/YYYY'),'D');

Item	Rundate	         Stddate	Status
P1	03-Nov-12	03-Nov-12	A

-- To get the Items disabled for Input date
with Itemdisabled as
(
select item,stddate maxcycledate 
from Item_tbl
where rundate = stddate
and rundate = <Inputdate>
and status = 'D')
-- Getting the Previous Active cycle for all disabled Items for Input date
select * from
(
select a.Item,a.stddate,a.status,rank() over (Partition by Item order by stddate desc) rank
from Item_tbl a,Itemdisabled b
where a.item=b.item
and a.rundate = a.stddate
and a.rundate < b.maxcycledate
)
where rank= 1
and status = 'A'

--
-- assuming no multiple D rows between A rows and thus we cannot have a D row that is a prior for another D row
--
-- this calculates prior values for both D and A rows then tosses away the A rows
-- this can be way less work because
-- although it is not intuitive in that we calculate prior values we do not need
-- and we do not eliminate rows as early as possible
-- this solution requires only one pass of the data (eg. no join back)
--
-- this works because both D and A rows have the same logical where clause that can be applied to them
--
-- column naming should be intuitive
--
select item,stddate D_STDDATE,prior_stddate A_STDDATE
from (
       select item,stddate,status,lag(stddate) over (partition by item order by stddate) prior_stddate
       from item_tbl
       where rundate = stddate
       and rundate <= <inputdate>
       and status in ('D','A')
     )
where status = 'D'
/

create table kevt1
(
   item number
 , status varchar2(1)
 , stddate date
)
/


insert into kevt1 values (1,'D',SYSDATE);

insert into kevt1 values (1,'C',sysdate-1);

insert into kevt1 values (1,'B',sysdate-2);

insert into kevt1 values (1,'A',sysdate-3);


select * from kevt1 order by item,stddate;


select *
from (
       select item,status,stddate,lag(case when STATUS = 'A' then stddate else null end ignore nulls) over (partition by item order by stddate) prior_a_stdddate
       from kevt1
       where status in ('D','A')
     )
where status = 'D'
/