How to create a FUNCTION to determine a date in the past....

[Sam Ram]

I need to write a function for use in reports that will take a specified date and determine the starting date of the month that is x number of months in the past.

Any suggestions?

[NaviGator]

Could you please clarify your request with more details or an example ? i don't think this will be a problem once we know what is exactly required ..

[Sam Ram]

The report as it is now has the following prompts which the user has to enter.  If the user does not enter exactly a period of 12 months, which is what should be entered, the report may include more or less than 12 months of data.  This is contrary to the businss reules for ths report.



         





      
The report query has the following detail filter:

               [Posting Date]     between ?BeginDate? and ?EndDate?


I need to use only an ending date (which might be defaluted to the current date) and then change the filter so as to retrive data for 12 months,
•   Beginning with the 1st day of the month 12 months prior to the ending-date month
•   Thru the last day of the ending-date month

If this logic could be encapsulated in a Function, then the logic become “portabale” for use in other reports of this type.

[Sam Ram]

OOPS, sent the previous reply prematurely...

The report as it is now has the following prompts which the user has to enter.  If the user does not enter exactly a period of 12 months, which is what should be entered, the report may include more or less than 12 months of data.  This is contrary to the business rules for the report.
             
                    Report Begin Date
                    Report End Date
      
The report query has the following detail filter:

                    [Posting Date]     between ?BeginDate? and ?EndDate?

I need to use only an ending date (which might be defaulted to the current date) and then change the filter so as to retrieve data for 12 months,

•   Beginning with the 1st day of the month 12 months prior to the ending-date month
•   Thru the last day of the ending-date month

If this logic could be encapsulated in a Function, then the logic becomes portable for use in other reports of this type.

And if the "number of months back" could be a variable, that would be even better.

If it would be easier, here is my email address:   srramirez@cvty.com

TIA   :)

[omgyme]

Sam,

Try something along these lines.

[POSTING_DATE] between _first_of_month(TO_DATE(TO_NUMBER(current_date) - 365)) and _last_of_month(current_date)

or you could try substituting your parameter name for the current_date.

[POSTING_DATE] between _first_of_month(TO_DATE(TO_NUMBER(?EndDate?) - 365)) and _last_of_month(?EndDate?)

This worked for me in Report Studio 8.3

Best of luck!