Report Design: Date Calculation

Date Calculation

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2032
Printed Date: 06 May 2024 at 5:55pm

Topic: Date Calculation

Posted By: Kathleen
Subject: Date Calculation
Date Posted: 11 Jan 2008 at 11:32am

Hello, Newbie again....

I am creating a report where I have to calculate whether or not a support function is being done every week. The function does not have to be done on the same day every week and that's what causing my problems.

I've tried to use the DateDiff feature, but cannot get anything to calculate correctly.

For instance, depth of pool water. Inspections of the depth are to be done on a weekly basis. Date is entered into Casper as DateCk. I need to make sure that DateCk is being done weekly.

How do I create a calculation to do this?

Replies:

Posted By: Lugh
Date Posted: 14 Jan 2008 at 5:39am

First, let's clarify what you mean by "weekly." Do you mean that no two inspections can be more than 7 days apart? Or, simply that there must be at least one inspection during every calendar week? Because, these require two very different solutions.

The first one is actually the easier one. Create a formula for each record that looks like:

If DateDiff("d",Previous({MyReport.DateCk}),{MyReport.DateCk}) > 7 Then
    "Late Inspection"
Else
    "Within Parameters"

Obviously, you should probably change the actual output. Making this something like a condition to change the Date to red when it's late might be good.

The second one is trickier. That's because there is no built-in function in Crystal to find a missing week. There are a few options.

One is to do something like the above, but test it based on the week rather than the number of days between. It might look like:

If DatePart("ww",{MyReport.DateCk}) - DatePart("ww",Previous({MyReport.Date)) > 1 Then
    "Late Inspection"
Else
    "Within Parameters"

However, all this does is flag the late inspections. It doesn't necessarily tell you the missing dates. (Also note that it fails on the first week of the year, but it's fairly trivial to add an extra bit to account for that.)

Another option is to add a table to your database, which includes the date of each Sunday for the year. Or, probably, a more generic table of sequential numbers, which you use to calculate the date for each Sunday of the year. Then, create a join between this table and your inspection table, and make a note of any Sunday that doesn't have a corresponding inspection in the next 7 days.