Hello,

Db2 Linux LUW FP 15.

Consider table A (ID varchar, EXECUTION_DATE date).

a) I want to first retrieve all IDs that have not executed during the
last 90 days:

select distinct ID from table_B where NOT EXISTS
(select 1
from table_A
where EXECUTION_DATE between current date - 90 days and current
date AND
table_A.ID = table_B.ID)



b) Now I need the last execution for all IDs that have NOT executed
during the last 90 days:

select TABLE_A.ID, MAX(EXECUTION_DATE) from TABLE_A,

(
select distinct ID from table_B where NOT EXISTS
(select 1
from table_A
where EXECUTION_DATE between current date - 90 days and current
date AND
table_A.ID = table_B.ID)
) as TABLE_C

where TABLE_A.ID = TABLE_C.ID and
EXECUTION_DATE < current date - 90 days

group by TABLE_A.ID

That was easy. The question is ... is there any way to do the same
thing using a better approach, perhaps SINGLE table scan on table_A ?
This table is really big.

TIA, Michel.