I have two tables table a and table b. table a shows historical data each day represented by a field "PROCESS_DATE"
table b contains calendar information with holiday dates etc. table b is also historical and updates each day with the same "PROCESS_DATE" field. table b has a field called "PREV_WORKDATE". This previous workdate is generally 2 days back from the "PROCESS_DATE" however if a holiday occurs then this field would show the last business date.
I'm trying to select all records from table a where the "PROCESS_DATE" in table a is >= the "PREV_WORKDATE" from table b.
The following code doesn't seem to give me what I need...
SELECT a.PROCESS_DATE, a.ID, a.BILLED_AMT FROM a LEFT JOIN ( SELECT PROCESS_DATE, PREV_WORKDATE FROM b ) ON a.PROCESS_DATE = b.PROCESS_DATE WHERE a.PROCESS_DATE >= b.PREV_WORKDATE QUALIFY RANK() OVER (ORDER BY b.PROCESS_DATE DESC)=1
I'm not clear regarding what you are trying to do. The outer join and QUALIFY don't seem to fit your description in words. Maybe you just need something like
SELECT … FROM a WHERE PROCESS_DATE >= (SELECT MAX(PREV_WORKDATE) FROM b)