Fetching backward data for a month OR a year

system · February 27, 2012, 2:37pm

Hello all,

I am in the process of running a job, and it works well.
I am including in my WHERE clause to extract the data from this date to this date.


TABLE_NAME.EFF_END_DT >= '2009-10-01 00:00:00.000'

I also have the date which fetches the previous one month worth of data.
For ex:
Today’s date is Feb-27-2012, and if i want to fetch the previous one month of data, then this WHERE clause works fine.


year(TABLE_NAME.EFF_END_DT) = year(sysdate()) and
month(TABLE_NAME.EFF_END_DT) = month( sysdate()) -1

But here comes the trouble,

I want to fetch a year’s worth of data
And if i am running my job on 5th of Jan 2012, and i want the previous one year worth of data or one month of data, then this WHERE clause does not work.

Ex: If i execute my job on Jan 5th 2012, and i want to fetch one month of previous data i.e Dec 1 to Dec 31 2011, then how would i use the condition in my WHERE clause.

Can anyone pls suggest me some idea.

Previous one month of data from the current sysdate()
Previous one year worth of data from the current sysdate()

Appreciate any help.
Thanks!

msr4 (BOB member since 2011-03-08)

system · February 27, 2012, 3:32pm

1.Get the sysdate using a global variable
2.For your start date use addmonths function to get the previous month and subtract that
value with dayinmonth function to get previous month start value.
3.For end date = get the day in month using the function and subtract with sysdate, this gives last date for previous month.
4. Use the start and end date global variables in your where clause.

Regards,
Kiran

kirankk4u (BOB member since 2009-10-16)

system · February 27, 2012, 3:47pm

Clear theoritically
Practically

I created 3 variable’s like said, but how do i add the functions to it.

msr4 (BOB member since 2011-03-08)

system · February 27, 2012, 3:57pm

I tried this query in Sql server, and this is what i want.
This is just an example.

SELECT datepart(year,dateadd(month,-3,GETDATE())),datepart(month,dateadd(month,-3,GETDATE()))

I am not able to get a clear picture as to how to implement this in Data Services.

Any guesses anyone

msr4 (BOB member since 2011-03-08)

system · February 27, 2012, 4:01pm

Hope this helps

$G_CURRENT_DATE = sysdate();
PRINT(‘CURRENT DATE = ‘[$G_CURRENT_DATE]’’);
$G_START_DATE = add_months($G_CURRENT_DATE,-1) - day_in_month(add_months($G_CURRENT_DATE,-1) -1);
PRINT(‘START DATE = ‘[$G_START_DATE]’’);
$G_END_DATE = $G_CURRENT_DATE - day_in_month($G_CURRENT_DATE);
PRINT(‘END DATE = ‘[$G_END_DATE]’’);

Regards,
Kiran

kirankk4u (BOB member since 2009-10-16)

system · February 27, 2012, 4:11pm

I appologize for asking this weired question.

Should this be used in the WHERE clause of my Query?
Or should i add a Script before OR after the Query?

msr4 (BOB member since 2011-03-08)

system · February 27, 2012, 4:12pm


$G_MONTHS = 12;

print(to_date('01' || to_char(add_months( sysdate(),-1 * $G_MONTHS),'mmyyyy'), 'ddmmyyyy'));
print(to_date('01' || to_char(add_months( sysdate(), -1 * $G_MONTHS + 1),'mmyyyy'), 'ddmmyyyy') - 1);

just change $G_MONTHS to be the number of months that you want to look back to.

davc4 (BOB member since 2009-07-03)

system · February 27, 2012, 4:56pm

Kiran,

I added your script in the script editor.
I am getting weired results.

If i run the job by adding this in my WHERE clause to extract all the data from >= ‘2009-10-01 00:00:00.000’, then i get 8125 ROWS

When i add this script that you provided and execute the job, then i get 12602 ROWS

Isn’t it weired. It should be less rows.

Any guesses?

msr4 (BOB member since 2011-03-08)

system · February 27, 2012, 5:03pm

The script I provided is for previous month load based on sysdate. The where clause you use is different. Thats why the counts don’t match.
You want to run previous month timeframe based on sysdate rite?

Try running the date range you pass in dataservices against database and see if the counts are same.

Regards,
Kiran

kirankk4u (BOB member since 2009-10-16)

system · February 27, 2012, 5:08pm

Yes,
I want to run the job, which should fetch the last months data
OR
One year previous data from today’s sysdate().

What should i alter in your script to fetch a year’s worth of data? (Lets take one year worth of data for now).

msr4 (BOB member since 2011-03-08)

system · February 27, 2012, 5:18pm

Ok,
Here’s the catch.
I am using your script in the script editor, and joining it to the DF.
In the DF (WHERE clause), i have nothing.

When i run the job, i see that i am extracting the data from
1999-01-01 00:00:00.000.

So. the script that i am adding before the DF is not doing its job or just fetching one months OR one year’s worth of data.


9072	7688	PRINTFN	2/27/2012 11:12:16 AM	CURRENT DATE = '2012.02.27'
9072	7688	PRINTFN	2/27/2012 11:12:16 AM	START DATE = '2012.01.01'
9072	7688	PRINTFN	2/27/2012 11:12:16 AM	END DATE = '2012.01.31'

I ask again the same question. Should this script be added in the WHERE clause in the DF or in the script editor and join it to the DF?

Appreciate any suggestions?

msr4 (BOB member since 2011-03-08)

system · February 27, 2012, 5:25pm

I added this in my WHERE clause.


$G_CURRENT_DATE = sysdate() and 
$G_START_DATE = add_months($G_CURRENT_DATE,-1) - day_in_month(add_months($G_CURRENT_DATE,-1) -1) and
$G_END_DATE = $G_CURRENT_DATE - day_in_month($G_CURRENT_DATE)

Gives no error, but no data is fetched.

msr4 (BOB member since 2011-03-08)

system · February 27, 2012, 5:32pm

davc4

I used your query in the WHERE clause. But i am not able to validate it.


$G_MONTHS = 12 and
(TABLE_NAME.EFF_END_DT('01' || to_char(add_months( sysdate(), -1 * $G_MONTHS),'mmyyyy'), 'ddmmyyyy')) and
(TABLE_NAME.EFF_END_DT('01' || to_char(add_months( sysdate(), -1 * $G_MONTHS + 1),'mmyyyy'), 'ddmmyyyy') - 1)

Getting an error message:


[Query:Join_Query] 
Invalid WHERE clause. Additional information: <Syntax error at line <15>: <(DWSE_AFFILIATIONS.EFF_END_DT('01' || to_char(add_months( sys>: near <(> found <'('> expecting <')', ALL, CONVERT, DOUBLE, FILE, GENERATED, HAVING, INPUT, an integer, LEFTOUTERJOIN, LOAD, __AL_MESSAGE, *, <>, NFIXCHAR, SYSTEM, char 425>.
1 error(s), 0 warning(s).

Check and fix the syntax and retry the operation. (BODI-1112394)>. (BODI-1111078)

Not able to get the right script to fetch the previous months/year of data

msr4 (BOB member since 2011-03-08)

system · February 27, 2012, 5:37pm

I don’t think you understand you have defined the global variables for start date and end date in your script editor,

Inside your DF under join clause this should be your condition,

TABLE_NAME.EFF_END_DT >= $START_DT and
TABLE_NAME.EFF_END_DT <= $END_DT

Try this it will work.

Regards,
Kiran

kirankk4u (BOB member since 2009-10-16)

system · February 27, 2012, 5:49pm

Bingo. There you go.
My mistake. I appologize. Was getting so confused, that i didn’t declare in the variables.

I am so glad that it worked.
Thanks a million Kiran.

Appreciate your help.

msr4 (BOB member since 2011-03-08)

system · February 28, 2012, 5:25pm

The function below, does not take the current date.


$G_CURRENT_DATE=	sysdate()
$G_START_DATE=add_months($G_CURRENT_DATE,-12) - day_in_month(add_months($G_CURRENT_DATE,-12) -12)
$G_END_DATE=$G_CURRENT_DATE - day_in_month $G_CURRENT_DATE)

I have a job that run’s on every 5th of the month, and what i need is the data one month back.
Ex: I executed a job for our testing purpose, just now (Feb 28-2012).
If i run the job, it should take the data from Mar 27-2012 to Feb 27-2012)

But this is what i am getting
2011-02-14 00:00:00.000
2012-01-31 00:00:00.000

How do i tweek this script so that it will fetch the data from today’s date to one year back…

Apprecaite any help…

msr4 (BOB member since 2011-03-08)

system · February 29, 2012, 1:06am

I figured it out.

msr4 (BOB member since 2011-03-08)

system · December 3, 2019, 4:03pm

Hi Kiran, (@kirankk4u)

I have the similar requirement, however I need to calculate exact 3 month back start date from the current date and not month, is it possible to get that ?

For example if today is 03.12.2019 and I am using the above code for start and end date it is giving me
START_DATE = 01.09.2019
END_DATE = 30.11.2019 (replaced 1 with 3 in the END_DATE calculation)
instead I want it to be
START_DATE = 02.09.2019
END_DATE =02. 12.2019

Hope it is clear. Thanks for your help in advance.

BOBJFan (BOB member since 2011-09-24)