Hi,
I am using LAG function to compare current row data versus previous row data , start_dt/ end_dt.
in below SQL inner query returning results correctly, but when I use outer where condition (where start_dt<prev_end_dt) considering all data and returning results,
in there any way I can use poli_id&iss_cd columns to get correct results. please advise.
SQL statements are posted as text. create table pol_tbl (pol_sk integer,pol_id integer, iss_cd varchar(10), start_dt date, end_dt date); insert into pol_tbl values(1,821,'BE','01-JAN-2013','31-dec-2013'); insert into pol_tbl values(2,821,'BE','01-JAN-2014','26-DEC-2015'); insert into pol_tbl values(3,821,'BE','23-Dec-2013',''); insert into pol_tbl values(4,821,'OP','01-JAN-2013','13-JUL-2015'); insert into pol_tbl values(5,821,'OP','14-JUL-2015',''); insert into pol_tbl values(6,821,'PU','01-JAN-2014','15-JUL-2014'); insert into pol_tbl values(7,821,'PU','14-JUL-2015',''); From above data query need to pull record where pol_sk=2 (with data combination pol_id and iss_cd ), because end_dt of this record is grater than start_dt of pol_sk=3 (with data combination pol_id and iss_cd ) in same way query need to pull record where pol_sk=6 (with data combination pol_id and iss_cd ), because end_dt of this record is grater than start_dt of pol_sk=7 (with data combination pol_id and iss_cd )