Date Range in FHS report

building reports desktopintelligence
1

Hi,
I’m creating Free hand SQL reports and trying to get the date range.
User inputs only one date (example : 07/01/2004, but the report should retreive the data from last 15 days. (example : 06/16/2004 to 07/01/2004)

I tried the below code.

Where date = (to_date(’@Prompt('2. Enter Date (mm/dd/yyyy) ‘, ‘D’, , Mono, Free)’, ‘mm/dd/yyyy’) -15)

It says : missing right parenthesis.
I believe there is a problem,
Any ideas please.
Thanks, Raj

ellis27 :us: (BOB member since 2004-06-22)

2

WHERE ( TRAIN.PERIODT.PEBEDT >= ADD_MONTHS(@variable(‘End Date (dd-Mmm-yyyy 00:00:00)’),-12) )
AND TRAIN.PERIODT.PEENDT <= @variable(‘End Date (dd-Mmm-yyyy 00:00:00)’)

I noticed this also, it seems to not like the @ I think. This code is for months rather than days but gives previous 12 month period.

ezab :uk: (BOB member since 2003-08-13)

3

Could you not use
date-15 < @Prompt('2. Enter Date (mm/dd/yyyy) ', ‘D’, , Mono, Free)

Not sure why you are trying to turn a date into a date :crazy_face:

Regards,
Mark

4

Thank you very much for the response. I think, I have not explained the issue clearly.

I have only one date prompt in my FHS report and the report should get the data for last one month.

Date= @Prompt('2. Enter Date (mm/dd/yyyy) ', ‘D’, , Mono, Free)

Example - User entered the date - 07/01/2003. The report should generate the data since June 1st to July 1st. I have tried the @variable function, but its retreiving the complete one year data.

Any suggestions appreciated.
Thanks, -Raj

ellis27 :us: (BOB member since 2004-06-22)

5

You were only 15 days different in your original question, but hey, it’s nearly weekend :lol:

OK, do you have a calendar table?
If so, you could use this to bring back start and end dates for the month.

Or, if not, you could try using something like Add_Months(CurrentMonth,-1) >= @Prompt(‘CurrentDate’…)

6

For Month to Date try (Oracle syntax):

WHERE
date BETWEEN TRUNC (to_date (@Prompt('2. Enter Date (mm/dd/yyyy)', 'A', , Mono, Free), 'mm/dd/yyyy'), 'mm') 
AND to_date ( @Prompt('2. Enter Date (mm/dd/yyyy)', 'A', , Mono, Free)', 'mm/dd/yyyy')

For Last 15 days try:

WHERE
date BETWEEN to_date (@Prompt('2. Enter Date (mm/dd/yyyy)', 'A', , Mono, Free), 'mm/dd/yyyy') -15
AND to_date ( @Prompt('2. Enter Date (mm/dd/yyyy)', 'A', , Mono, Free), 'mm/dd/yyyy')

This should get you more than started.

Andreas :de: (BOB member since 2002-06-20)

7

Thanks Team,
I will try the above options and will let you know. I apologize for the confusion 15 days and 1 month, becuase i have two requests.
Anyways, Have a great holiday weekend to everyone.
–Raj

ellis27 :us: (BOB member since 2004-06-22)