Script: Filtering Dates

[#1]

Hello!

I have the following code that generates a single table with 3 dates that come from different tables in my data model:

TMP_LT_ACTIONS:
   LOAD
     ACCOUNT_ID,
     PAY_DT
   RESIDENT PAYMENTS
   WHERE YEAR (PAY_DT) >= 2000 AND YEAR (PAY_DT) <= 3000;
JOIN
   LOAD
     ACCOUNT_ID,
     SCHEDULED_DT
   RESIDENT SCHEDULES
   WHERE YEAR (SCHEDULED_DT) >= 2000 AND YEAR (SCHEDULED_DT) <= 3000;
JOIN
   LOAD
     ACCOUNT_ID,
     INSPECTION_DT
   RESIDENT INSPECTIONS
   WHERE YEAR (INSPECTION_DT) >= 2000 AND YEAR (INSPECTION_DT) <= 3000;

NOCONCATENATE
LT_ACTIONS:
   LOAD
     ACCOUNT_ID,
     PAY_DT,
     SCHEDULED_DT,
     INSPECTION_DT,
     ROWNO ()     AS DATE_KEY
   RESIDENT TMP_LT_ACTIONS;

DROP TABLE TMP_LT_ACTIONS;

Then I create a couple of tables more that use the table LT_ACTIONS.

The thing is that from all the dates (PAY_DT, SCHEDULED_DT, INSPECTION_DT) I have to make a filter for those dates between year 2000 and 3000 (because there are some entry errors where a date is greater than year 3000 and I have to filter those ones).

So my question is where to put the filters?

Is it okay to do it in the first table I use, for every date field, like I did in TMP_LT_ACTIONS?

Or which is the best way to do it?

Thank you!!!

[shawn-qv · Accepted solution]

The way you've done it is fine. Others might prefer to do it on the second load because when the criteria changes (i.e. say from 3000 to 4000), then they would only have to change it in one place (instead of the 3 places during the first load).

The other thing you might want to consider is the load time. Perhaps one method would yield a quicker load time than another, but this is something you'll have to test.

S.