This is a datediff question but involving grouping

I have a crystal report that has dates in it and I need to know the time difference as the status changes from In Progress to Completed.  Don't care about any other statuses, but they are currently built into the report so disregard them.

I am attaching the report.

For each account, as the status (line 2,5) changes from In Progress to Complete, I need to show the time difference in hours and minutes across from the account #.  When there are more than one In Progress times, I only want to use the first In Progress time, none of the others.

I also want to disregard any accounts that are do not have both an In Progress and Complete time.

This is over my head.

Please help.
HSP-Cdsts-hx.rpt
Becky EdwardsEpic Clarity DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I know how to solve this in t-SQL, but not in Crystal reports as such.
in t-sql would also give some different solutions (syntax), depending on the volume (number of rows) to process... and the complexity of the sql.

best would be if the crystal reports takes a stored procedure as input ...
please clarify
Mike McCrackenSenior ConsultantCommented:
This will take 3 formulas

Formula1 - Report header and group 1 header
Name - xDeclareVariables
WhilePRintingRecords;
Global BooleanVar InProgressSet;
Global DateTimeVar InProgressStart;
InProgressSet := False;
""

Open in new window


Formula 2 - Group 2 header
Name - xSetInProgressStart
WhilePRintingRecords;
Global BooleanVar InProgressSet;
Global DateTimeVar InProgressStart;

If {HSP_ACCT_CDSTS_HX.CDSTS_HX_STS_C} = 2 AND Not InProgressSet then
(
    InProgressSet := True;
    InProgressStart := {HSP_ACCT_CDSTS_HX.CDSTS_HX_INST}
);
''

Open in new window


Formula 3 - GRoup1 header
Name - xCalcDuration
EvaluateAfter({@xSetInProgressStart});
Global BooleanVar InProgressSet;
Global DateTimeVar InProgressStart;
Local StringVar strOut := "";
Local NumberVar nbrDurationMinutes;
Local NumberVar nbrHours;
Local NumberVar nbrMinutes;

If {HSP_ACCT_CDSTS_HX.CDSTS_HX_STS_C} = 4 AND InProgressSet then 
(
    nbrDurationMinutes := DateDiff('n',InProgressStart, {HSP_ACCT_CDSTS_HX.CDSTS_HX_INST});
    nbrHours := nbrDurationMinutes \ 60;
    nbrMinutes := nbrDurationMinutes mod 60;
    strOut := CStr(nbrHours,"00") & ":" & CStr(nbrMinutes,"00");
);
strOut

Open in new window


The attached report has been modified.  It shows the intermediate steps.  To hide them simply uncomment the last line of each formula

mlmcc
HSP-Cdsts-hx.rpt

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Wow.  This works perfectly.  I don't know if I will ever understand how you did it, but I thank you so much.  You are truly blessed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.