I have a table (Not all columns shown) that shows type. Typeid is the PK. There is some date overlap that should not be there. I want to remove those from my query.
Custid typeid start_dt end_dt
101 352 3/28/2017 1/16/2019
101 353 1/15/2018 1/15/2019
Data shows the below (example of overlap).
Custid typeid start_dt end_dt overlap
101 352 3/28/2017 1/16/2019 1
101 353 1/15/2018 1/15/2019 1
201 426 1/1/2019 12/31/9999 0
SQL. (Index are on all of the above table columns)
select *
from (select
Custid,
typeid,
start_dt,
case
when end_dt > lead (start_dt) over (partition by custid order by start_dt) then 1
when start_dt < lag (end_dt) over (partition by custid order by start_dt) then 1
else 0
end overlap)
where overlap = 0
The filtering on the case expression slows down the query returning results. Is there any tips/tricks to improve the query performance?
It may help if you can show the execution plan, and more specifics on the existing indexes.
My immediate thought is that an index on
(custid, start_dt)
might avoid the need to sort to implement the windowing logic.