I've been trying to filter rows with a certain value for code based on the values of task_dts. Essentially I only want the rows per id where the task_dts timestamp falls between the code_dts timestamp of the current code and the code_dts of the following code.

For example; for the rows where code equals 'z', I only want the rows where the task_dts is in the range of the code_dts for value 'z' and value 'y'. For the rows where the code equals 'y', I only want the rows where the task_dts is in the range of the code_dts for value 'y' and value 'x' etc

My table looks the following:

The output would look like this:

I've tried to solve this with qualify without succes. help would be much appreciated

CodePudding user response：

You can use a table expression to pre-compute the timestamp ranges. Then, filtering is easy.

For example:

select t.*
from t
join (
  select code, dt, lead(dt) over(order by dt) as next_dt
  from (select code, min(code_dts) as dt from t group by code) x
) y on t.code = y.code
where t.task_dts between y.dt and y.next_dt or y.next_dt is null

CodePudding user response：

After reading The Impaler's answer I finally understood your requirements :-)

This is the same logic based on Window Functions:

with cte as
 ( 
   select t.*
     -- next code_dts, i.e. at least one row will return 
     -- the code_dts of the following code 
     ,lead(code_dts,1,task_dts) over (order by code_dts) as next_dts
   from tab as t
 )
select *
from cte
qualify task_dts between code_dts
                     -- assign the next code's dts to all rows within the same code
                     and max(next_dts) over (partition by code)
; 

Hard to tell which one will perfrom better...