Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Extracting last Date in a particular field Post Reply Post New Topic
Author Message
Terry Kasdorf
Newbie
Newbie


Joined: 19 May 2011
Location: United States
Online Status: Offline
Posts: 4
Quote Terry Kasdorf Replybullet Topic: Extracting last Date in a particular field
     Posted: 19 May 2011 at 7:19am

We are changing the way we measure an Incident. We were measuring from open to closed (using the OPENTIME and CLOSEDTIME fields) to our new way, "open" to "resolve" fields. The problem I'm trying to figure out is, the "resolve field" has not been used properly for the last 8 months (they are making changes to the proceedures as we speak), so when I pull the field ACTIVITYM1.TYPE is equal to "resolved" for a particular Incident number it's showing me each time that date and time stamp was used for each resolved stamp. Example:

IM21922  Resolved 5-1-11 3:25pm  JOHN JONES
                Resolved 5-2-11 3:15pm  MARY DAVID
                Resolved 5-3-11 5:25pm  TERRY MARSH
 
So this one ticket should of only have one resolved Date and time and user, it has three(3). So when I try and measure duration, it would measure all three entries(for the one ticket). What I need is a formula that will extract the last "resolved", date & time stamp, and User ID, and exclude the first two. So my duration measurement for IM21922 would be from OPENTIME (e.g.1-22-11) to (ACTIVITYM1.DATESTAMP)5-3-11 5:25pm. As apposed to getting back three duration measurements of about the same time frame. Can some one please help?
 
 
 
 
Thanks,
Tjk
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 May 2011 at 7:32am
if you have SQL you can used a view or stored proc to do this outside of crystal.
If you must do this in Crystal then you need to also think through what else you need the data for. IF you are doing additional calculations then you might want to use  Running toal or a Variable formula.
IF it is just for dispaly purposes then group on the PK for the incident (looks like your "IM21922" field) and use  formula doing a datediff using a maxvalue
datediff("interval desired here",opentime,maximum(datestamp,PKfield))
IP IP Logged
Terry Kasdorf
Newbie
Newbie


Joined: 19 May 2011
Location: United States
Online Status: Offline
Posts: 4
Quote Terry Kasdorf Replybullet Posted: 19 May 2011 at 7:56am
What I'm looking for is a formula that would extraxt the oldest date & time for ACTIVITYM1.DATESTAMP field for each Incident Ticket that has mulitple "resolve" entries. If you close out your part of a ticket and mark it "relsolved" (which would leave an activity stamp as well), and then send it to me to work on, and then I resolve and close out the ticket. Then I have two resolve stamps, I need to be able to exclude the multiple entries for each resolve on a ticket. So you think using the datediff formula's would get me what I wish? Also, on your example you show 'interval desired here", what is that referencing?
 
 
Thanks,
Tjk
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 May 2011 at 8:18am
interval desired here is by second, minute, hour, day, etc.
You never indicated what the difference you were looking for was.
 
My formula assumes that you are only brining in records that are resolved.
 
IP IP Logged
Terry Kasdorf
Newbie
Newbie


Joined: 19 May 2011
Location: United States
Online Status: Offline
Posts: 4
Quote Terry Kasdorf Replybullet Posted: 19 May 2011 at 9:06am
I apologize for my wording. Actually what I'm looking for is a way to extract the "resolve" date and time one time for each record. So if a record has multiple "resolve" date and time stamps because each user that touched the record used the "resolve" date and time. I need to be able to use the one that actually closed out the ticket, which would be the last one to use the resolve date and time stamp......
 
So my formula would go in and look at each incident ticket and if it has only one (1) "resolve" date and time stamp, then that record is fine, if it has incident tickets with multiple (2 or more) resolve stamps, then it would go in and take out and use the last resolve date and time stamp. So when I run my report each ticket would have one resolve date and time stamp. Plus my duration would be measure from the open date and time to that one (1) resolved date and time stamp.
 
As of right now if i generate a report for this past month. It is showing 1,749 Incident tickets and it's showing that their were 2,250 resolve entries. Which means there are 501 tickets that have more than one "resolve time & date" stamp. Which in turn is skewing my duration times, because it is measuring from open to resolve for each entry, even if one ticket has three resolve entries.
 
 
 
 
 
 


Edited by Terry Kasdorf - 19 May 2011 at 9:15am
Thanks,
Tjk
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 May 2011 at 9:15am

Do you have access to creating a view or stored procedure?
IP IP Logged
Terry Kasdorf
Newbie
Newbie


Joined: 19 May 2011
Location: United States
Online Status: Offline
Posts: 4
Quote Terry Kasdorf Replybullet Posted: 07 Jun 2011 at 6:07am
Actually I don't know if I do or not? I was hoping there was a quick fix such as a formula that would be able to do that function?
Thanks,
Tjk
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Jun 2011 at 7:25am
no real easy solution in crystal as you are trying to evaluate multiple records and exclude some of those records as based on grouping and comparisons. You could probably do it in a Command if you cannot use a stored proc or a view.
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.