Solved

This is a datediff question but involving grouping

Posted on 2014-01-02
3
552 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 101

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

717 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