Reporting on data on a given month range

[Woodyuk]

Im trying to write a report, that picks up certain records in a given month.
The report also shows a year to date and for the week for these records, which ive got working fine.

Now I know I could type the dates in at the beginning of each month but I would like to automate it. Also these periods would work the same every year, for the number of weeks in them (except when it’s a leap year I think)

Now is there a really easy way to do this, or am I hoping for far too much?

My problem is my month dates run on weird dates (ie not for the whole of June), but always start on a Sunday and finish on a Saturday eg

30th April 2006 to 27th May 2006 = May
28th May 2006 to 1st July 2006 = June
2nd July 2006 to 29th July 2006 = July
Etc wont bore with all the dates

There is a pattern I get, and this is how many weeks are in the period:

4 Weeks = May
5 Weeks = June
4 Weeks = July
4 Weeks = August
5 Weeks = September
4 Weeks = October
4 Weeks = November
5 Weeks = December
4 Weeks = January
4 Weeks = February
5 Weeks = March
4 Weeks = April

Im Using Crystal 10

 

[synapsevampire]

You didn't state WHEN you are running the report, I'll assume that it's for the previous month, in either case the logic will work.

You can hardcode the logic, as in:

if month(currentdate) in cdate(year(currentdate),5,28) to cdate(year(currentdate),7,1) then
{table.date} in cdate(year(currentdate),4,30) to cdate(year(currentdate),5,27)
else
if ...

the only tricky part would be when you're in January and need to go back a year, in which case you'll have something like:

if month(currentdate) in cdate(year(currentdate),12,31) to cdate(year(currentdate),1,30) then
{table.date} in cdate(year(currentdate)-1,11,30) to cdate(year(currentdate),12,30)

Assumed dates above of course.

We could also write a formula to determine the Saturday and Sunday, however since there are only 12 periods this should prove easier.

-k

-k

 

[Woodyuk]

Sorry I forgot too say, well it will be run on a Saturday, and show figures entered for that whole week, then the current month period we are in then for the year to date. I will try this out, however my machine has died so need to fix that first

 

[synapsevampire]

Ooops, drop the MONTH() part, just use the date, I started one way and decided on another.

if currentdate in cdate(year(currentdate),5,28) to cdate(year(currentdate),7,1) then
{table.date} in cdate(year(currentdate),4,30) to cdate(year(currentdate),5,27)
else
if ...