Formula to return dates if the week 1

[straud13]

I am trying to write a formula that accepts a date parameter and returns the days of that week sunday to monday. For example today is Wednesday the 25, that would be my parameter. the result would return 7 dates starting with the 23rd to the 29th. But i have no idea where to start.

 

[synapsevampire]

This depends on whether your data source has all those dates, and if you intend to extract data for those dates.

A query simply returns what is in the database, so to fabricate dates you have options.

The best solution long term is to create a periods table which contains all dates and use a Left Outer from that table to your datasource.

I have numerous posts here on this topic, inclusive of scripts to create a periods table in SQL Server.

To simply get the 7 days with no data, you could use {?dateparm}+6 to determine what the dates are and loop through the dates and output accordingly.
Please flesh out what it is you need other than the 7 days, and include Crystal version, database used, example data and expected output, no doubt several people wil supply different approaches, but yuo need to supply requirements.

-k

 

[bdreed35]

This formula will return a comma delimited string of dates. You really didn't specify how you wanted the output so this is what I did.

WhilePrintingRecords;
numbervar j := -(DayOfWeek({?date})) + 1;
numberVar i;
stringVar myDates;

For i := j to (j + 6)
do
(
    myDates := myDates + ", " + ToText({?date} + i,"MM/dd/yyyy")
);
Right(myDates, Length(myDates)-2)

This could be altered to populate an array that you could use if needed.

~Brian

 

[straud13]

Crystal Version 9
SQL Server 2000 database.

Basically i have an appointment table with all appointments scheduled. I input a date parameter and I want the report to return all appointments for that day (No problem), and all appointments for that week, so if today is wednesday then all appointmenst for the previous sunday, monday, tuesday and then thursday, friday and saturday.

Table has field called app_Date then App_Details.

 

[bdreed35]

I think you are looking for something like this in your record selection criteria:

{table.date} IN ({?date} + (-(DayOfWeek({?date}))+1)) TO 
                ({?date} + (-(DayOfWeek({?date}))+6))

This will get passed to the database so your report will still be efficient.

~Brian

 

[straud13]

Thank bdreed35 and everyone else that worked like a charm. Now if you wouldn't mind explaining the logic of that formula I would be very happy.

 

[bdreed35]

Using IN...TO allows you to specify an inclusive range for your date field. Since you want everything based upon you date parameter, you need to set the first day of the week and the last day.

You accomplish this by first determing which day of the week the date parameter falls on.

the DayOfWeek function returns a number from 1 to 7.
1 = Sunday
2 = Monday
...
...
7 = Saturday

Say the date parameter selected was today (11/25/2003). If you ran the date through the DayOfWeek function it would return 3, for Wednesday.
In order to find the first day of the week (1) we would need to subtract 2 days from the date parameter. If we take the negative value of the result of the DayOfWeek(), we get -3. IF we just added this to the date parameter, we would end up with the last day of the previous week. When we add the 1 to the -3 we get -2 and add this to our date parameter. This will always work no matter what day of the week your parameter falls on.
To get the last day of the week, we follow the same concept but add 6 because the day of the week equals the first day plus 6.
Sorry if this is a little long winded but I think it gets the logic across.

~Brian

 

[straud13]

Thanks Brian. You Rock!

can i do the same thing for the entire month?

 

[bdreed35]

Sure but you would have to do it a little differently.

I would create 2 SQL Expressions.
This one for month:

{%Month}

{fn MONTH(table.`date`)}

And this one for Year:

{%Year}

{fn YEAR(table.`date`)}

In your record seleciton formula use this:

{%month} = Month({?date})
AND
{%Year} = Year({?date})
[code]
By using the SQL Expressions, your selection criteria will get passed in the where clause of SQL statement, to the database, making your report more efficient.


~Brian