PLSQL Help 2

[notadba]

Hi,

I need to get a plsql script to clean up a dataset that I have...

I have 2 tables, the first is the main dataset and the second a lookup table (location/region).

The first table has data that looks like:
TYPE EMP_ID CODE START_DT END_DT
1 177 DEP1 04-OCT-01 07-NOV-01
1 177 DEP1 08-NOV-01 15-FEB-02
1 177 DEP1 16-APR-02 08-MAY-02
1 177 DEP2 09-MAY-02 24-MAY-02
1 177 DEP2 05-JUN-02 12-JUN-02
2 178 DEP6 12-AUG-06 28-SEP-06

And the Second is basically:

CODE REGION
DEP1 LOCATION1
DEP2 LOCATION1
DEP3 LOCATION2

What I am looking to do is consolidate the dataset so that continuous periods within the same location are linked up and reduced to a single row.

EG. Results for above should look like:
TYPE EMP_ID CODE START_DT END_DT
1 177 DEP1 04-OCT-01 24-MAY-02
1 177 DEP2 05-JUN-02 12-JUN-02
2 178 DEP6 12-AUG-06 28-SEP-06

Variation on the type column for the Employee does not need to be considered, change in the code are only relevant when the location changes.

Thanks in advance. I think I should probably take a plsql course ;-)

 

[johnherman]

In your data, the period for DEP1 is not continuous in terms of days, the second record ending 15-FEB-02 and the third beginning 16-APR-02. Is this data correct? It could affect the solution method.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[notadba]

Thanks John,

My oversight - any break of 1 day or more should result in a separate row.

results should be
TYPE EMP_ID CODE START_DT END_DT
1 177 DEP1 04-OCT-01 15-FEB-02
1 177 DEP1 16-APR-02 24-MAY-02
1 177 DEP2 05-JUN-02 12-JUN-02

Just having one of those days

 

[notadba]

With a little assistance from someone else who can't write plsql either, we managed a solution (?), though happy for feedback about best practice or coding standards....

This code reads in line by line into a second table, compares the results and updates/inserts rows as necessary. I am sure it could be assisted by regular commits among other things.

DECLARE
v_srv DEP_ALLOWANCE_TBL.SRV%TYPE;
v_srv2 DEP_ALLOWANCE_TBL.SRV%TYPE;
v_id DEP_ALLOWANCE_TBL.ID%TYPE;
v_id2 DEP_ALLOWANCE_TBL.ID%TYPE;
v_allow DEP_ALLOWANCE_TBL.allowance%TYPE;
v_allow2 DEP_ALLOWANCE_TBL.allowance%TYPE;
v_start_dt DEP_ALLOWANCE_TBL.start_dt%TYPE;
v_start_dt2 DEP_ALLOWANCE_TBL.start_dt%TYPE;
v_end_dt DEP_ALLOWANCE_TBL.end_dt%TYPE;
v_end_dt2 DEP_ALLOWANCE_TBL.end_dt%TYPE;
v_start_dtkeep DEP_ALLOWANCE_TBL.start_dt%TYPE;
CURSOR DEPLOY_CURSOR IS
select A.srv, A.id, A.allowance, A.start_dt, A.end_dt
from DEP_ALLOWANCE_TBL A
order by A.srv, A.id, A.allowance, A.start_dt, A.end_dt;
BEGIN
OPEN DEPLOY_CURSOR;

FETCH DEPLOY_CURSOR
INTO v_srv, v_id, v_allow, v_start_dt, v_end_dt;
-- INSERT THIS INTO NEW TABLE
insert into DEPLOY_COPY values(v_srv, v_id, v_allow, v_start_dt, v_end_dt);
commit;
-- keep track of start date.
v_start_dtkeep:= v_start_dt;

LOOP
FETCH DEPLOY_CURSOR
INTO v_srv2, v_id2, v_allow2, v_start_dt2, v_end_dt2;
IF v_srv = v_srv2 and v_id = v_id2 and v_start_dt2 = v_end_dt + 1
then
-- UPDATE NEW TABLE WHERE ID,DEPLOY
UPDATE DEPLOY_COPY
SET END_DT = v_end_dt2
where srv = v_srv
and ID = v_id
and allowance = v_allow
and start_dt = v_start_dtkeep;
ELSE
-- INSERT WITH SECOND SET OF VARIABLES
insert into DEPLOY_COPY values(v_srv2, v_id2, v_allow2, v_start_dt2, v_end_dt2);

v_start_dtkeep:= v_start_dt2;

END IF;
V_ID:=V_ID2;
V_SRV:=V_SRV2;
V_START_DT:=V_START_DT2;
V_END_DT:=V_END_DT2;


EXIT WHEN DEPLOY_CURSOR%NOTFOUND;
END LOOP;
CLOSE DEPLOY_CURSOR;
END;
/

 

[SantaMufasa]

Notadba,

Here is slightly tighter, alternate code to yours, above:

declare
    curr_location varchar2(20);
    loop_cnt number := 0;
    t tab1%rowtype; -- previous-row hold area
    procedure new_row is
        begin
            insert into tab3 values
                (t.type, t.emp_id,
                 t.code, t.start_dt, t.end_dt);
        end;
begin
    for x in (select t1.*,region
                from tab1 t1, tab2 t2 where t1.code = t2.code
               order by t1.code, t1.start_dt) loop
        if curr_location = x.region then
            if trunc(x.start_dt)
               between t.start_dt and t.end_dt+1 then
                t.end_dt := trunc(x.end_dt);
            else
                new_row;
                t.type     := x.type;
                t.emp_id   := x.emp_id;
                t.code     := x.code;
                t.start_dt := x.start_dt;
                t.end_dt   := x.end_dt;            
            end if;
        else
            curr_location := x.region;
            if loop_cnt > 0 then
                new_row;
            else
                loop_cnt := loop_cnt + 1;
            end if;
            t.type     := x.type;
            t.emp_id   := x.emp_id;
            t.code     := x.code;
            t.start_dt := x.start_dt;
            t.end_dt   := x.end_dt;            
        end if;
    end loop;
    new_row;
end;
/

PL/SQL procedure successfully completed.

select * from tab3;

 TYPE     EMP_ID CODE  START_DT  END_DT
----- ---------- ----- --------- ---------
    1        177 DEP1  04-OCT-01 15-FEB-02
    1        177 DEP1  16-APR-02 24-MAY-02
    1        177 DEP2  05-JUN-02 12-JUN-02

Let us know if this is of interest to you or if you have questions about this code.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

See if this works for you - might require some tweaking:-

select * from t1
/


TYPE CODE START_DT END_DT
---------- ---- --------- ---------
1 DEP1 04-OCT-01 07-NOV-01
1 DEP1 08-NOV-01 15-FEB-02
1 DEP1 16-APR-02 08-MAY-02
1 DEP2 09-MAY-02 24-MAY-02
1 DEP2 05-JUN-02 12-JUN-02
2 DEP6 12-AUG-06 28-SEP-06



select type,code,start_dt,decode(start_dt-e3,0,end_dt,e3)
from
(
select type,code,start_dt,end_dt,decode(s2,end_dt+1,e2,null,start_dt) e3
from
( select type,code,start_dt,end_dt,
lead(start_dt) over(partition by type order by start_dt) s2,
lead(end_dt) over(partition by type order by start_dt) e2
from t1 )
)
where e3 is not null



TYPE CODE START_DT DECODE(ST
---------- ---- --------- ---------
1 DEP1 04-OCT-01 15-FEB-02
1 DEP1 16-APR-02 24-MAY-02
1 DEP2 05-JUN-02 12-JUN-02
2 DEP6 12-AUG-06 28-SEP-06

 

[notadba]

Thanks all for the feedback - 2 quite different approaches. Have not had time to try either due to work (!!! - very inconvenient when it gets in the way). We are having to re-run the data on a number of different parameters over the next few days/weeks, so will look at these during the next iteration.