hi daniel,

if i understand your question right, then in the interest of sanity i might normalize start/stop dates in an inner statement like:

select
h.startdate,
h.enddate,
--syntax might not be right
timestampdiff(h.enddate, h.startdate) as timedifference

from (
select
 
case
when h.date<STATEDATE THEN STATEDATE
else h.date
end as StateDate,

CASE
WHEN (h.enddate is null or h.enddate > ENDDATE) then ENDDATE
else h.enddate
END as enddate

)

this is also an example of why splitting it into multiple SQL statements can help. you have one that generates the overlaps then you can test/validate. you can have a second one to take this input and calculate days between or whatever else you need.