Mycode

declare

t_ticketid varchar2(200):='SRQ15654';

type rec is record

(tkstatusid number,

tkstatusid_next number,

ticketid varchar2(200),

status varchar2(20),

status_next varchar2(20),

changedate varchar2(200),

changedate_next varchar2(200)

);

type tab1 is table of

rec index by binary_integer;

Tab tab1;

i number;

v_net_date varchar2(200);

v_time varchar2(200);

begin

i:=0;

for rec in (select a.* from TKSTATUS a where ticketid=t_ticketid

order by tkstatusid

)

loop

for rec1 in (select * from tkstatus a where ticketid=rec.ticketid

and tkstatusid>rec.tkstatusid

order by tkstatusid

)

loop

i:=i+1;

tab(i).tkstatusid:=rec.tkstatusid;

tab(i).tkstatusid_next:=rec1.tkstatusid;

tab(i).ticketid:=rec.ticketid;

tab(i).status:=rec.status;

tab(i).status_next:=rec1.status;

tab(i).changedate:=rec.changedate;

tab(i).changedate_next:=rec1.changedate;

exit;

end loop;

end loop;

v_net_date:=0;

for i in tab.first..tab.last

loop

if tab(i).status = 'INPROG' then

select (to_date(tab(i).changedate_next,'DD-MON-RRRR HH:MI:SS PM')-to_date(tab(i).changedate,'DD-MON-RRRR HH:MI:SS PM')+1)-

(SELECT COUNT(DAYS) FROM (SELECT TO_CHAR(TO_DATE(tab(i).changedate,'DD-MON-RRRR HH:MI:SS PM')+LEVEL ,'D') DAYS

FROM DUAL CONNECT BY LEVEL < round(to_date(tab(i).changedate_next,'DD-MON-RRRR HH:MI:SS PM')-to_date(tab(i).changedate,'DD-MON-RRRR HH:MI:SS PM')))

WHERE DAYS IN ('7' ,'1')) DAY

INTO V_TIME

FROM DUAL;

V_NET_DATE:=V_NET_DATE+V_TIME;

end if;

--dbms_output.put_line(tab(i).tkstatusid||'-'||tab(i).tkstatusid_next||'---'||tab(i).changedate||'----'||tab(i).changedate_next||tab(i).status||'----'||tab(i).status_next);

end loop;

dbms_output.put_line(v_net_date);

exception

when others then

dbms_output.put_line(Sqlerrm);

end;

select (to_date('24-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')-to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')+1)-

(SELECT COUNT(DAYS) FROM (SELECT TO_CHAR(TO_DATE('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')+LEVEL ,'D') DAYS

FROM DUAL CONNECT BY LEVEL <= to_date('24-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')-to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM'))

WHERE DAYS IN ('7' ,'1')) DAY

FROM DUAL;

select to_char(to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS PM')+LEVEL,'D') t,to_char(to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')+LEVEL,'DAY') d,

to_char(to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')+LEVEL,'DD') date1

FROM DUAL CONNECT BY LEVEL <= ROUND(to_date('24-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')-to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM');

select round(to_date('24-MAR-09 11.56.26 AM','DD-MON-RRRR HH:MI:SS PM')-to_date('11-MAR-09 08.35.15 AM','DD-MON-RRRR HH:MI:SS PM')) from dual