SQL QUERY to get previous date-CodePudding

PERIOD_SERV

PERSON_NUMBER           DATE_sTART          PERIOD_ID       
10                      06-JAN-2020         192726
10                      04-APR-2019         12827
11                      01-FEB-2021         282726
11                      09-APR-2018         827266

For each person_number I want to add a column with previous date start. When i am using the below query, it is giving me repeated rows. I want to get only row, with an additional column of the most recent "last date_start". For example -

PERSON_NUMBER           DATE_sTART          PERIOD_ID         PREVIOUS_DATE
10                      06-JAN-2020         192726              04-APR-2019 
11                      01-FEB-2021         282726              09-APR-2018

I am using the below query but getting two rows,

select person_number,
    PERIOD_ID pv_period_id,
     lag(date_start) OVER ( partition BY person_number order by DATE_sTART )PREVIOUS_DATE    
     from period_serv

CodePudding user response：

You can restrict the set of rows in the outer query

select person_number, pv_period_id, PREVIOUS_DATE
from (
  select person_number,
    PERIOD_ID pv_period_id,
    lag(date_start) OVER ( partition BY person_number order by DATE_sTART ) PREVIOUS_DATE ,
    row_number() OVER ( partition BY person_number order by DATE_sTART desc)  rn 
  from period_serv
) t
where rn = 1

CodePudding user response：

One option is to use MAX(..) KEEP (DENSE_RANK ..) OVER (PARTITION BY ..) analytic function such as

WITH p AS
(
  SELECT MAX(date_start) KEEP (DENSE_RANK FIRST ORDER BY date_start) 
                         OVER (PARTITION BY person_number) AS previous_date,
         p.*                 
    FROM period_serv p                  
)
SELECT p.person_number, p.date_start, p.period_id, p.previous_date
  FROM p
  JOIN period_serv ps
    ON ps.person_number = p.person_number
   AND ps.period_id = p.period_id 
 WHERE ps.date_start != previous_date

Demo