Show dates that are after a joined tables date

Database
Enthusiast

Show dates that are after a joined tables date

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
2 REPLIES 2
Teradata Employee

Re: Show dates that are after a joined tables date

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)
Enthusiast

Re: Show dates that are after a joined tables date

Yes, that is exactly what I was looking for.  I knew I was overthinking it! thank you!