BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Fetching backward data for a month OR a year

Goto page 1, 2  Next
 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
msr4
Principal Member
Principal Member



Joined: 08 Mar 2011

Posts: 193



PostPosted: Mon Feb 27, 2012 9:37 am 
Post subject: Fetching backward data for a month OR a year

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.

Code:

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.
Code:

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


But here comes the trouble, icon_question.gif
1) I want to fetch a year's worth of data
2) 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.

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


Appreciate any help.
Thanks!
Back to top
kirankk4u
Senior Member
Senior Member



Joined: 16 Oct 2009
ASUG Icon
Posts: 71
Location: USA



PostPosted: Mon Feb 27, 2012 10:32 am 
Post subject: Re: Fetching backward data for a month OR a year

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
Back to top
msr4
Principal Member
Principal Member



Joined: 08 Mar 2011

Posts: 193



PostPosted: Mon Feb 27, 2012 10:47 am 
Post subject: Re: Fetching backward data for a month OR a year

Clear theoritically
Practically crazy.gif

I created 3 variable's like said, but how do i add the functions to it.
Back to top
msr4
Principal Member
Principal Member



Joined: 08 Mar 2011

Posts: 193



PostPosted: Mon Feb 27, 2012 10:57 am 
Post subject: Re: Fetching backward data for a month OR a year

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

Code:
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 icon_question.gif
Back to top
kirankk4u
Senior Member
Senior Member



Joined: 16 Oct 2009
ASUG Icon
Posts: 71
Location: USA



PostPosted: Mon Feb 27, 2012 11:01 am 
Post subject: Re: Fetching backward data for a month OR a year

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
Back to top
msr4
Principal Member
Principal Member



Joined: 08 Mar 2011

Posts: 193



PostPosted: Mon Feb 27, 2012 11:11 am 
Post subject: Re: Fetching backward data for a month OR a year

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?
Back to top
davc4
Senior Member
Senior Member



Joined: 03 Jul 2009

Posts: 88
Location: Aberdeen, Scotland


flag
PostPosted: Mon Feb 27, 2012 11:12 am 
Post subject: Re: Fetching backward data for a month OR a year

Code:

$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.
Back to top
msr4
Principal Member
Principal Member



Joined: 08 Mar 2011

Posts: 193



PostPosted: Mon Feb 27, 2012 11:56 am 
Post subject: Re: Fetching backward data for a month OR a year

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?
Back to top
kirankk4u
Senior Member
Senior Member



Joined: 16 Oct 2009
ASUG Icon
Posts: 71
Location: USA



PostPosted: Mon Feb 27, 2012 12:03 pm 
Post subject: Re: Fetching backward data for a month OR a year

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
Back to top
msr4
Principal Member
Principal Member



Joined: 08 Mar 2011

Posts: 193



PostPosted: Mon Feb 27, 2012 12:08 pm 
Post subject: Re: Fetching backward data for a month OR a year

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

icon_question.gif
Back to top
msr4
Principal Member
Principal Member



Joined: 08 Mar 2011

Posts: 193



PostPosted: Mon Feb 27, 2012 12:18 pm 
Post subject: Re: Fetching backward data for a month OR a year

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.

Code:

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?
Back to top
msr4
Principal Member
Principal Member



Joined: 08 Mar 2011

Posts: 193



PostPosted: Mon Feb 27, 2012 12:25 pm 
Post subject: Re: Fetching backward data for a month OR a year

I added this in my WHERE clause.
Code:

$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.
Back to top
msr4
Principal Member
Principal Member



Joined: 08 Mar 2011

Posts: 193



PostPosted: Mon Feb 27, 2012 12:32 pm 
Post subject: Re: Fetching backward data for a month OR a year

davc4

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

$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:
Code:

[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 banghead.gif
Back to top
kirankk4u
Senior Member
Senior Member



Joined: 16 Oct 2009
ASUG Icon
Posts: 71
Location: USA



PostPosted: Mon Feb 27, 2012 12:37 pm 
Post subject: Re: Fetching backward data for a month OR a year

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
Back to top
msr4
Principal Member
Principal Member



Joined: 08 Mar 2011

Posts: 193



PostPosted: Mon Feb 27, 2012 12:49 pm 
Post subject: Re: Fetching backward data for a month OR a year

thumbsup.gif cookie.gif

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.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0321 seconds using 18 queries. (SQL 0.0035 Parse 0.0010 Other 0.0276)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo