Listers,
I need your advice on the following issue:- The user should be prompted to enter year and month. The report should display the revenue for the past 12 months from the month year entered.
Example:- If user enters Jan 2000, data for dec 1999, nov1999, oct 1999 should be diplayed.
What should my condition look like?
Listserv Archives (BOB member since 2002-06-25)
I would use something like this for the following databases(I did test this):
In Oracle:
BETWEEN (TO_DATE(‘01-’ || @variable(‘Please enter the month to end on(MON)’) ||’-’ || @variable(‘Please enter the year to end on(YYYY)’),‘DD-Mon-YYYY’) - 365)AND TO_DATE(‘01-’ || @variable(‘Please enter the month to end on(MON)’) ||’-’ || @variable(‘Please enter the year to end on(YYYY)’),‘DD-Mon-YYYY’)
Greg
Greensboro (336) 286-7217
Home (704) 882-6238
2B||!2B
Listserv Archives (BOB member since 2002-06-25)
The condition may be :
TIME.SLS_DATE <=to_date(@variable(‘Month_M’),‘MM/YYYY’) AND
TIME.SLS_DATE >=add_months(to_date(@variable(‘Month_M’),‘MM/YYYY’),-12)
Listserv Archives (BOB member since 2002-06-25)
Another one for ORACLE:
User is prompted for end month “MM/YYYY” and “number of months”:
table.date_field BETWEEN
to_date(@prompt(‘Enter date (MM/YYYY)’,‘A’,), ‘MM/YYYY’) - @prompt(‘Number of months?’,‘N’,) + 1
AND
to_date(@prompt(‘Enter date (MM/YYYY)’,‘A’,), ‘MM/YYYY’)
Attention has to be paid if the date field also contains a time part…
hope this helps
Walter
Steve Krandel wrote:
Listers,
I need your advice on the following issue:- The user should be prompted to enter year and month. The report should display the revenue for the past 12 months from the month year entered.
Example:- If user enters Jan 2000, data for dec 1999, nov1999, oct 1999 should be diplayed.
What should my condition look like?
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’ Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’
–
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
Listserv Archives (BOB member since 2002-06-25)
O.K. Here is how I have done it. My problem was that I wanted an intelligent SQL statement to specify the month and the years required to bring back 12 months worth of data. We use an aggregated date dimension table that has a year and a month column. By selecting the correct combination you can isolate the correct date dimension key to join back to the fact table. This means that selecting the previous 12 months of data often requires that you specify something like:
Year = 2000 and Month = 1
or Year = 1999 and Months between 2 and 12 What I have done is this:
a) I have a parameter table that keeps track of what the current month and year is. This is updated automatically each night. I query this table to return the current year and month. This is because our calendar months and fiscal months do not correspond with each other and I need the fiscal year and month.
b) I have setup a prompt to ask the user how many months to go back in time. The query that I am providing allows you to go back up to 13 months. c) The structure of the query is below (this could easily be a filter if you start from the where clause):
Select X from DATETABLE
WHERE DATETABLE.year = (SELECT currentyear from PARAMETERTABLE) AND DATETABLE.month BETWEEN
(select case when convert(int,@Variable(‘MonthsToGoBack’)) > (SELECT currentmonth from PARAMETERTABLE) then 1
else ((SELECT currentmonth from
PARAMETERTABLE)-convert(int,@Variable(‘MonthsToGoBack’)))+1 end)
AND (SELECT currentmonth from PARAMETERTABLE)OR (DateDimAggPeriod.FiscalYear =
(select case when convert(int,@Variable(‘MonthsToGoBack’))>(SELECT currentyear from PARAMETERTABLE)
then (SELECT currentyear from PARAMETERTABLE)-1 else (SELECT currentyear from PARAMETERTABLE) end) AND DateDimAggPeriod.FiscalPeriod BETWEEN
(select case when convert(int,@Variable(‘MonthsToGoBack’))>(SELECT currentmonth from PARAMETERTABLE)
then 13-(convert(int,@Variable(‘MonthsToGoBack’))-(SELECT currentmonth from PARAMETERTABLE))
else ((SELECT currentmonth from
PARAMETERTABLE)-convert(int,@Variable(‘MonthsToGoBack’)))+1 end)
AND (select case when convert(int,@Variable(‘MonthsToGoBack’))>(SELECT currentmonth from PARAMETERTABLE)
then 12 else (SELECT currentmonth from PARAMETERTABLE) end))
Good Luck!
Simon Miller
Listserv Archives (BOB member since 2002-06-25)
In a message dated 00-01-25 17:38:31 EST, you write:
I need your advice on the following issue:- The user should be prompted to enter year and month. The report should display the revenue for the past 12 months from the month year entered.
Steve:
What database are you using?
If Oracle, you could build a prompt that looks something like this:
months_between(trunc(some_database_date,‘MM’), to_date(@prompt(‘Enter year YYYY’,‘N’,) || @prompt(‘Enter Month MM’,‘N’,) || ‘01’ ,‘YYYYMMDD’) between -12 and 0
The months_between calculation returns the number of months between two dates. The trunc(some_database_date,‘MM’) returns the first day of the month that includes “some_database_date”. The prompt for year and month is concatenated into a string and then converted into a date for use in the months_between function.
If the database date is this month, then the months_between returns zero.
If the database date is last month, then the months_between returns -1.
If the database date is next month, then the months_between returns +1.
By using a range from -12 to 0 you should be able to return the prior 12 months.
Note: by using -12 to 0, the prompted month is included. If you want strictly the prior 12 months use -12 to -1. If you want the prior 11 months and the prompted month use -11 to 0 to get a true 12 month range.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)