OraFAQ Forum: SQL & PL/SQL » SQL query statement in Report builder 6.0

Home » SQL & PL/SQL » SQL & PL/SQL » SQL query statement in Report builder 6.0

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL query statement in Report builder 6.0 [message #10717]

Wed, 11 February 2004 15:30

fatimah
Messages: 9
Registered: February 2004

Junior Member

Hi..

I want to know how to obtain a monthly report by using the SQL Query statement? E.g. I want to retrieve the Monthly Collection report for last month (January)

�

Report message to a moderator

Re: SQL query statement in Report builder 6.0 [message #10720 is a reply to message #10717]

Wed, 11 February 2004 23:19

Maaher
Messages: 7065
Registered: December 2001

Senior Member

And what do you mean by 'monthly report'? If you want to know what new records have been inserted into your_table the past 30 days, you need a timestamp/date column that contains the creation date.

Assuming that your_table contains a column 'creation_date', you could create a report based upon the following query:

Select whatever_you_need
  From your_table
 Where creation_date > TRUNC(SYSDATE)-31

MHE

Report message to a moderator

Re: SQL query statement in Report builder 6.0 [message #10726 is a reply to message #10720]

Thu, 12 February 2004 06:18

Rehman
Messages: 19
Registered: February 2000

Junior Member

Hi,

what I have understood is....

Assume that you are executing ur report on 12th of Feb'04 then the report should default it to previous month. i.e. starting from 01st of Jan - 30th of Jan irrespective of whether you executed on 12th/01st/27th of Feb.

Ex:-

In ur SQL data model, use a lexical parameter.
eg : Select *
From

Where <Date_Col>
Between First_Day(Add_Months(Sysdate,-1))
-- Make sure you define First_Day function which accepts a
-- date value as a parameter and converts to the
-- first_date of previous month.

And Last_Day(Add_Months(Sysdate,-1));
------------------------------------------------------

You cant use the condition as

sysdate - 31....

Trust this will solve ur query.

Rgrds

Report message to a moderator

Previous Topic:	case
Next Topic:	OWA_UTIL.CALENDARPRINT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 16:43:57 CDT 2024