Date Diff between two records

[Creakinator]

I'm brand new to CR and need to do a turn around time report. CR11 with a SQL database.

Here's the pertinant data: Table name is x_approval
CASE# User_name Approval_Type Assigned_Date Esig_date
11-01855-N-01 GEORGE ANALYST 3/24/2011 7:23:06 AM 4/11/2011 10:03:33 AM
11-01855-N-01 MARY TECH_REV 4/11/2011 1:08:17 PM 4/11/2011 1:10:07 PM

I've created a CR report that shows this
Template (as a group (This field is from another table - cases))
Case# (as a group)
User_Name Approval_Type Assigned_Date Esig_date (as my details fields)
so that I have
Unit Name
11-01855-N-01
GEORGE ANALYST 3/24/2011 7:23:06 AM 4/11/2011 10:03:33 AM
MARY TECH_REV 4/11/2011 1:08:17 PM 4/11/2011 1:10:07 PM

I need to be able to find the date difference between the Analyst's Assigned_date and the Tech_Rev Esig_date, then I need to show the average turn around time for the Template group.

I'm so new to CR and I'm a bit lost right now.

Thanks for your help.

Christy

 

[Creakinator]

This is what I just tried following what was in

http://tek-tips.com/viewthread.cfm?qid=1523073:

I made two formulas:
@fmAnalystAssignDate which is: if {X_APPROVAL.APPROVAL_TYPE} = "ANALYST" then {X_APPROVAL.ASSIGNED_DATE}

@fmTechEsignDate which is: if {X_APPROVAL.APPROVAL_TYPE} = "TECH_REV" then {X_APPROVAL.ESIG_DATE}
I put the formulas in the details area of the report and I guess I can suppress them so they don't show?

Then I did another formula:
@fmDateDiff which is: DateDiff ("d",{@fmAnalystAssignDate}, {@fmTechEsignDate})
and put it in the group for the Case#, but I'm getting zeros for the answer to this formula.

Am I on the right track? What am I missing?

Thanks.

Christy

 

[lbass]

Assuming you have a group on case, your formula should be:

DateDiff ("d",maximum({@fmAnalystAssignDate},{table.case#}), maximum({@fmTechEsignDate},{table.case#}))

-LB

 

[Creakinator]

Thank you! That worked. And thanks for the quick response. I'll probably be back with more questions as I have lots of reports to design and the best way for me to learn is to get in and do it.

Christy

 

[Creakinator]

One more question on this - how do I do an average on this the @fmDateDifffield? I tried to make a formula, but the @fmDateDifffield still didn't show.

Thanks.

Christy

 

[lbass]

Average at what level? For the entire report?

-LB

 

[Creakinator]

Oops. Sorry. At the Template group level. I need to show the average turn around cases per template (which corresponds to a section at my work).

I have two groups: the top one is the Template, the next one is the case#. I put the @fmDateDifffield in the group footer for the Case# group level and supressed the details of the report.


Thanks.

Christy

 

[lbass]

Create these formulas:

//{@reset} to be placed in the template group header and suppressed:
whileprintingrecords;
numbervar sumdiff;
numbervar cnt;
if not inrepeatedgroupheader then (
sumdiff := 0;
cnt := 0
);

//{@accum} to be placed in the case# group footer and suppressed:
whileprintingrecords;
numbervar sumdiff := sumdiff + DateDiff ("d",maximum({@fmAnalystAssignDate},{table.case#}), maximum({@fmTechEsignDate},{table.case#}));
numbervar cnt := cnt + 1;

//{@display} to be placed in the template group footer:
whileprintingrecords;
numbervar sumdiff;
numbervar cnt;
if cnt <> 0 then
sumdiff/cnt

-LB

 

[Creakinator]

Sorry it took me a while to get back to you to give my thanks for your help - this was a holiday weekend. Your formulas worked for me.

Christy