CODEWITHSUNDEEP
sqloracle-database
SSA_Tech124
SSA_Tech124

Reputation: 599

query to retrieve changes in multiple columns in sql

I have the below table with the assignments details such as location, org, job, grade etc. I want to build a query such that changes in the location, org are fetched for all system_person_type = 'EMP' only. 

per_assignments

Person_id    locat_id    org_id    job_id   grade_id       system_person_type   START_DT           END_DT     
1             Toronto    XYZ        1        GR1             EMP                01-JAN-2019        20-JAN-2019
1             US         XYZ        1         GR1            EMP                21-JAN-2019        31-DEC-4712
2             Chicago    ABC        2         GR1            EX-EMP             01-jul-2017        30-Nov-2017
2             Toronto    XYZ        3         GR2            EMP                01-JAN-2019        03-JUL-2019
2             India      GFH        3         GR2            EMP                04-JUL-2019        08-SEP-2019
2             India      GFH        4         GR2            EMP                09-SEP-2019        31-DEC-4712

so in the above example the output should be :

person_id old_locat_id  new_locat_id      old_org_id    new_org_id old_start_dt   new_start_dat
1          Toronto         US              -               -        01-jan-2019     21-jan-2019
2          Toronto       India             XYZ            GFH       01-JAN-2019     04-JUL-2019

I created the below query But from the below query I am getting old_start_dt> new_start_dt and I am not getting all the changes required, only 1 column change is retrieving. How can the below query be changed to accomadtae the above requirement ?

SELECT DISTINCT paam_change_loc.person_id ,  
                to_char(paam_change1.start_date,'YYYY-MM-DD')    AS old_effective_start_dt , 
                to_char(paam_change_loc.start_date,'YYYY-MM-DD') AS new_effective_start_dt ,
                paam_change1.location_id    AS old_loc_value , 
                paam_change_loc.location_id AS new_loc_value 
FROM            per_assignments paam_change_loc, 
                per_assignments paam_change1 
WHERE           paam_change_loc.person_id =paam_change1.person_id 
AND            ( 
                                paam_change_loc.location_id IS NOT NULL 
                AND             paam_change_loc.location_id <> paam_change1.location_id ) 
AND             paam_change_loc.system_person_type = 'EMP' 
AND             paam_change1.system_person_type = 'EMP' 
AND             to_char(to_date(paam_change_loc.start_date),'DD-MM-YYYY') BETWEEN ('05-08-2019') AND             '05-12-2019' 
AND             ( 
                              to_char(to_date(paam_change_loc.start_date)-1,'DD-MM-YYYY') BETWEEN ('05-08-2019') AND             '05-12-2019' )

'05-08-2019' and '05-12-2019' is the transfer dates which will be passed to the query and the dates are to be compared in between these two dates

Upvotes: 0

Views: 470

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

This query gives expected result:

select person_id, prev_start_dt, start_dt, 
       case loc_new when loc_old then ' - ' else loc_old end loc_old,
       case loc_new when loc_old then ' - ' else loc_new end loc_new,
       case org_new when org_old then ' - ' else org_old end org_old,
       case org_new when org_old then ' - ' else org_new end org_new
  from (
    select person_id, locat_id loc_new, org_id org_new, start_dt, 
               lag(locat_id) over (partition by person_id order by start_dt) loc_old,
               lag(org_id) over (partition by person_id order by start_dt) org_old, 
               lag(start_dt) over (partition by person_id order by start_dt) prev_start_dt, 
               case start_dt when 1 + lag(end_dt) over (partition by person_id order by start_dt) 
                             then 1 end flag
          from per_assignments)
  where flag = 1 and (loc_new <> loc_old or org_new <> org_old)

dbfiddle

In the inner query apply filters for system_person_type and dates as needed. At first I used lag() three times and also to mark continuous rows, in column flag. Then only flagged rows are shown where location or organization changed.

Upvotes: 1

Popeye
Popeye

Reputation: 35930

I am not sure about data structure of your db. Considering the sample data as table, you can achieve the expected outptut using analytical function:

Select person_id, 
       Locat_id as old_locat_id,
       New_locat_id,
       org_id as old_org_id,
       New_org_id,
       Start_date as old_start_date,
       New_start_date
  From
(Select t.*, 
       Lead(org_id) over (partition by person_id order by start_date) as new_org_id,
       Lead(start_date) over (partition by person_id order by start_date) as new_start_date,
       Lead(locat_id) over (partition by person_id order by start_date) as new_locat_id,
  From your_table t where system_person_type = 'EMP')
Where locat_id <> new_locat_id or org_id <> new_org_id;

Cheers!!

Upvotes: 0

Related Questions