How to Implement a Dynamic Date Filter in a Job?

dataservices job design
1

I have a job that runs once every week. I need to filter the data for previous week. If , for example, my job runs every monday, I need the data to be from previous week, lets say, previous monday to sunday. Does anyone has any insight as to how to accomplish this in data services?
Thanks.

2

Hi,

Here is one solution for you.
You could create 2 global variables and assign value to them in a script before the dataflow runs.

The variables should be date.
$G_DateTo = to_date(to_char(sysdate()-1,‘yyyymmdd’),‘yyyymmdd’);
$G_DateFrom = $G_DateTo - 7;

the to_date(to_char(sysdate(),‘yyyymmdd’),‘yyyymmdd’) is to remove the time from sysdate().

Then you could add a where clause in a query
tablename.date_column >= $G_DateFrom and
tablename.date_column <= $G_DateTo.

If you are using a SQL Transform as a source you could add it like this:
tablename.date_column >= [$G_DateFrom] and
tablename.date_column <= [$G_DateTo].

Hope this helps

haraldur :iceland: (BOB member since 2006-05-16)