Select the first record after the last but one X-CodePudding

I'm trying to get the first BEG_PERIOD date immediately after the last but one record of X (DEF_ENDING) of each user (USER_ID).

So I have this:

USER_ID	BEG_PERIOD	END_PERIOD	DEF_ENDING
159	01-07-2022	31-07-2022	X
159	25-09-2022	15-10-2022	X
159	01-11-2022	13-11-2022
159	14-11-2022	21-12-2022	X
159	01-01-2023	30-01-2023	X
414	01-04-2022	31-05-2022	X
414	01-07-2022	30-09-2022
414	01-10-2022	01-12-2022	X
480	01-07-2022	30-06-2022
480	01-07-2022	30-08-2022	X
480	02-09-2022	01-11-2022	X
503	15-03-2022	16-06-2022	X
503	19-07-2022	23-07-2022
503	24-07-2022	31-10-2022
503	01-11-2022	21-12-2022	X

The dates I need are the ones in bold

Can you help me?

I tried this but I only get the latest dates :(

SELECT
    p.USER_ID,
    p.BEG_PERIOD
FROM
    PERIODS p
    INNER JOIN PERIODS p2 ON
        p.USER_ID = p2.USER_ID
        AND
        p.BEG_PERIOD = (
            SELECT
                MAX( BEG_PERIOD )
            FROM
                PERIODS
            WHERE
                PERIODS.USER_ID = p.USER_ID
        )
WHERE
    p.USER_ID > 10

CodePudding user response：

Try the following using the ROW_NUMBER and `LAG' window functions:

/* this to assign row numbers only for rows where def_ending = 'X' */
with order_def_ending as 
(
  select *,
   case def_ending when 'X' then
    row_number() over (partition by user_id order by 
                        case def_ending when 'X' then 1 else 2 end, 
                        end_period desc)
    else null end rn,
    lag(def_ending, 1, def_ending) over (partition by user_id order by end_period) pde /* previous end_period value */
  from yourTbl
),
lag_rn as
(
  select *, 
    lag(rn) over (partition by user_id order by end_period) prn /* previous row_number value */
  from order_def_ending
)
select user_id, beg_period, end_period, def_ending
from lag_rn
where (
        prn = 2 or /* when there are multiple rows with def_ending = 'X' */
        (prn = 1 and rn is null) /* when there is only one row with def_ending = 'X' */
      ) and pde = 'X' /* ensure that the previous value of def_ending is = 'X' */
order by user_id, end_period

See demo

CodePudding user response：

This should work based on the sample data:

with data as (
    select *,
        sum(case when DEF_ENDING = 'X' then 1 end)
            over (partition by USER_ID order by BEG_PERIOD desc) as grp
    from PERIODS
)
select
    USER_ID,
    min(BEG_PERIOD) as BEG_PERIOD,
    min(END_PERIOD) as END_PERIOD,
    min(DEF_ENDING) as DEF_ENDING
from data
group by USER_ID
where grp = 1;

If you can't rely on the two dates being minimums then:

with data as (
    select *,
        sum(case when DEF_ENDING = 'X' then 1 end)
            over (partition by USER_ID order by BEG_PERIOD desc) as grp
    from PERIODS
), data2 as (
    select *,
        row_number() over (partition by USER_ID order by BEG_PERIOD) as rn
    from data
    where grp = 1
)
select *
from data2
where rn = 1;