I have a query to find out the address details of an employee

Person_number           country     town/city   start_Date          end_date
--------------------------------------------------------------------------------
10                      CA          Toronto     01-feb-2022         10-feb-2022
10                      CA          Ottawa      11-feb-2022         31-dec-4712

11                      CA          Toronto     10-feb-2022         31-dec-4712

12                      AB          Greenvilee  15-feb-2022         20-feb-2022
12                      AB          Portvilee   21-feb-2022         31-dec-4712

13                      CA          North york  01-feb-2022         09-feb-2022
13                      CA          Waterloo    10-feb-2022         31-dec-4712

I want I want to create a query to get the data only for those employees whose town/city value has changed within the parameter date.

For example: if I pass the parameter date as 01-feb-17-feb 2022, I should get the following output:

Person_number           country     current_town    previous_town   effective_date_ofchange
10                       CA          Toronto            Ottawa          11-feb-2022 
13                       CA          North york         Waterloo        10-feb-2022

person #11 will not come as there is no change within the date. Person#12 will not come as the change is after the parameter date.

Can this be achieved with any analytical function ?

CodePudding user response：

Here's one approach, with the LAG window function:

The fiddle

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

WITH xdata (person_number, country, town_city, start_date, end_date) AS (
        SELECT 10,  'CA',   'Toronto'   ,  '01-feb-2022', '10-feb-2022' FROM dual UNION
        SELECT 10,  'CA',   'Ottawa'    ,  '11-feb-2022', '31-dec-4712' FROM dual UNION
        SELECT 11,  'CA',   'Toronto'   ,  '10-feb-2022', '31-dec-4712' FROM dual UNION
        SELECT 12,  'AB',   'Greenvilee',  '15-feb-2022', '20-feb-2022' FROM dual UNION
        SELECT 12,  'AB',   'Portvilee' ,  '21-feb-2022', '31-dec-4712' FROM dual UNION
        SELECT 13,  'CA',   'North york',  '01-feb-2022', '09-feb-2022' FROM dual UNION
        SELECT 13,  'CA',   'Waterloo'  ,  '10-feb-2022', '31-dec-4712' FROM dual
     )
   , xtable (person_number, country, town_city, start_date, end_date) AS (
        SELECT person_number, country, town_city
             , to_date(start_date, 'DD-MON-YYYY')
             , to_date(end_date, 'DD-MON-YYYY')
          FROM xdata
     )
   , table2 (person_number, country, current_town, prev_town, start_date) AS (
        SELECT person_number, country, town_city
             , LAG(town_city) OVER (PARTITION BY person_number ORDER BY start_date)
             , start_date
          FROM xtable
     )
SELECT * FROM table2
 WHERE current_town <> prev_town
   AND start_date BETWEEN DATE'2022-02-01' AND DATE'2022-02-17'
;

LAG(x) OVER (PARTITION BY person_number ORDER BY start_date)

This obtains the prior x for each row for the corresponding person, based on the start_date order.

The result: