Solved

This is a datediff question but involving grouping

Posted on 2014-01-02
3
547 Views
Last Modified: 2014-01-03
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
0
Comment
Question by:Becky Edwards
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39751920
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
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39752025
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
0
 

Author Closing Comment

by:Becky Edwards
ID: 39754186
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.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question