DateDiff

[botchka]

I have this in CR2008:

Group1: Complaint Number
Group2: Status Date
Group3: Status
Details: Suppressed

I have Group 2 Name in the Group 3 header and the group 2 header is suppressed.

What I need is:

Complaint Number: 122
(status) (status date) (number of days in status)
Complaint Opened: 05/12/2008 22
Investigation Opened: 06/03/2008 36
Investigator Reassigned: 07/09/2008 519
Investigation Complete: 12/10/2009 25
Admin Review Complete: 01/04/2010 25

What I have showing up is:

Complaint Number: 122
(status) (status date) (number of days in status)
Complaint Opened: 05/12/2008 0
Investigation Opened: 06/03/2008 22
Investigator Reassigned: 07/09/2008 36
Investigation Complete: 12/10/2009 519
Admin Review Complete: 01/04/2010 25

The formula I'm using for datediff is:

whileprintingrecords;

DateTimeVar BeginDate := (if (previous({complaint number}) <> {complaint number}) then {status date} else previous({status date}));
DateTimeVar EndDate := {status date};
NumberVar total_days := DateDiff("D", BeginDate, EndDate);

Basically what they need is the number of days that the complaint is in each status...so the datediff line needs to move up one row.

Does that make sense?

 

[lbass]

Try placing all fields in the detail section (leaving the groups as they are), and then show a sample of the detail results.

On what basis would you expect to see 22 for the first record when both the start and end date would be the same?

-LB

 

[botchka]

I will post details in a bit.

I would expect to see 22 for the first record because that's how many days it would have had the 'complaint opened' status:

6/3/2008 - 5/12/2008 = 22 days

 

[botchka]

moved the datediff formula to details and this is what I'm getting now:

Complaint Opened: 05/12/2008
2
2
2
Investigation Opened: 06/03/2008
2
2
2
Investigator Reassigned: 07/09/2008
2
2
2
Investigation Complete: 12/10/2009
2
2
2
Admin Review Complete: 01/04/2010
2
2
2

 

[lbass]

Can you please just show the complaint number, the status and the date field in the detail section with no group headers? Why are you grouping on date?

It looks like you really want to compare the current date with the NEXT date, not the previous date, by the way.

-LB

 

[botchka]

122 Complaint Opened 05/12/2008
122 Complaint Opened 05/12/2008
122 Complaint Opened 05/12/2008
122 Investigation Opened 06/03/2008
122 Investigation Opened 06/03/2008
122 Investigation Opened 06/03/2008
122 Investigator Reassigned 07/09/2008
122 Investigator Reassigned 07/09/2008
122 Investigator Reassigned 07/09/2008
122 Investigation Complete 12/10/2009
122 Investigation Complete 12/10/2009
122 Investigation Complete 12/10/2009
122 Administrative Review Complete 01/04/2010
122 Administrative Review Complete 01/04/2010
122 Administrative Review Complete 01/04/2010

 

[lbass]

You really want to compare the date with the next date, and so I think you should be using the Group Footer section for the date group, NOT the group header. Then change your formula to the following (assuming that the last date in the complaint group is compared to the currentdate) and place it in the suppressed detail section:

whileprintingrecords;
DateTimeVar BeginDate;
DateTimeVar EndDate;
if onfirstrecord or
{table.status} <> previous({table.status}) then
BeginDate := {status date};
if onlastrecord or
{table.complaint} <> next({table.complaint}) then
EndDate := currentdate else
if {table.status} <> next({table.status}) then
EndDate := next({status date});

In the date group footer section, use this formula:

Whileprintingrecords;
NumberVar total_days;
total_days := DateDiff("d", BeginDate, EndDate);

-LB

 

[botchka]

Thanks very much LB. I'll give this a go some time tomorrow and report back...much appreciated!

 

[botchka]

Well this doesn't appear to be working correctly because I'm getting some weird output and I'm having to correct some of the formula.

In your second formula:
Whileprintingrecords;
NumberVar total_days;
total_days := DateDiff("d", BeginDate, EndDate);

when I tried to save, I just get an error because it doesn't recognize "BeginDate" as a datetime value. So I added:

Whileprintingrecords;
NumberVar total_days;
DateTimeVar BeginDate;
DateTimeVar EndDate;
total_days := DateDiff("d", BeginDate, EndDate);

In addition to that, it's returning weird date counts.

122 Complaint Opened 5/12/2008 22
122 Investigation Opened 6/3/2008 36
122 Investigator Reassigned 7/9/2008 529
122 Investigation Complete 12/10/2009 25
122 Administrative Review Complete 1/4/2010 38

 

[botchka]

Ah..nevermind. It's too early. It appears to be working correctly.

 

[lbass]

Sorry about forgetting to build in the datetime variables in the display formula.

-LB