I have had a request for some filters as a user wants to schedule a report but am struggling to find something that fits, any help would be very much appreciated
Here goes:
Say I run a report today, 17/02/2011, I need the data to always come back from the Monday of the current week, so on Monday (14/02/2011) I need the dates 14/02/2009 - 14/02/2010, on Tuesday (15/02/2011) I need the dates 14/02/2009 - 15/02/2010 and so on.
However, if the report ran on a Sunday (21/02/2011) it would provide 21/02/2009 - 20/02/2010 as it would be providing a complete year which is the minimum I need.
So the number of days covered will depend on what day of the week you run the report.
On a Sunday you get 365 days, Monday and up to Saturday you would get 371 days.
Hope that makes sense it has been sending me bog-eyed
Thanks for your help in advance, it is much appreciated!
You didn’t say what database this is - makes a difference for people providing you with some sample code. In Oracle the add_months function would be a good starting point…take 24 months from your start date…
I can get the bit to work where I get the dates with this bit of code:
@Select(Core Dates\Recorded Date) BETWEEN add_months(Next_Day((sysdate-7), ‘MONDAY’), -24) AND
add_months((sysdate-7),-12)-(1/(606024))
which will increase as the days go up (although it starts at 15/02/2011 instead of 14/02/2011? I’m working on that) but am still stuggling with how to get the last bit:
You can have a case statement and based on the current day you can select your statement.
modify your current statement for selecting 365 days (On Sunday). and in the case statement based upon the current day select the statement1 or statement2.
(BOB member since 2002-08-15)
(BOB member since 2007-04-02)