[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

This is a datediff question but involving grouping

Posted on 2014-01-02
3
Medium Priority
?
557 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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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