Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports .NET 2003 : Report Design
Message Icon Topic: Minimum Date Filter - Select Criteria Post Reply Post New Topic
Author Message
romert
Newbie
Newbie


Joined: 31 Jan 2008
Location: United States
Online Status: Offline
Posts: 1
Quote romert Replybullet Topic: Minimum Date Filter - Select Criteria
    Posted: 31 Jan 2008 at 9:03am

I am running a report where I need to pull the first instance of a record when there is a many to one relationship between two specific fields.  I was thinking the easiest way to do that is to pull the Min on the create date of the record, but I need it in the hi-level criteria so that my end result is only those records that match, and if there is more than one, then just the first instance of the other record.  Here is an example because I am not sure if this makes sense.

12461716 12461716 2/3/2005  12:01:00 PM
12462087 12462087 2/3/2005  12:23:00 PM
12462088 12462088 2/3/2005  12:23:00 PM
12462180 12462180 10/22/2006  9:29:33 AM
12462726 12462726 2/3/2005  12:56:00 PM
12462844 1205822 2/16/2007  1:18:05 PM
12462844 1205824 2/16/2007  1:18:33 PM
12462844 1243527 2/23/2007  9:12:28 AM
12462844 12462844 2/3/2005  1:01:00 PM
12462844 1285142 3/1/2007  9:25:05 AM
12462844 1305471 3/5/2007  8:34:59 AM

so for record 12462844 where there are multiple records in the second column for that SR ID, I want to only show 1205822 because it was the first one that was created.  Is that possible?  I am desperate here...
 
romert
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 01 Feb 2008 at 5:20am
There are a couple ways to do this.

The first way is to alter the SQL of your data source, to use a self-join to only return the records you want.  This is a pretty classic problem in SQL, and most books will give you the solution.  Or, I can give you a sample query if you give me a bit more information on your schema.

The other way is to simply suppress the details in Crystal you don't want.  You need to create a group on the SR ID.  Then, you can suppress all the detail records, sort by date, and show the fields in the group footer (which will display the information for the last record).  Or, you can put a condition on the details to suppress, that might look like:

{MyReport.SRDate} <> Maximum ({MyReport.SRDate},{MyReport.SRID})
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 01 Feb 2008 at 5:22am

Hi,

Use command object ,or create view using the SQL below I have tested in SQL SERVER
 
Replace your column names with the columns below
 
SELECT A.FIRSTCOL,A.SECONDCOL, MIN(A.DATES)
FROM MINTAB A
WHERE DATES =
(SELECT MIN(DATES)
 FROM MINTAB B
WHERE A.FIRSTCOL = B.FIRSTCOL)
GROUP BY A.FIRSTCOL,A.SECONDCOL
 
Output
 
12462844 1205822 2/16/2007  1:18:05 PM
12461716 12461716 2/3/2005  12:01:00 PM
12462087 12462087 2/3/2005  12:23:00 PM
12462088 12462088 2/3/2005  12:23:00 PM
12462180 12462180 10/22/2006  9:29:33 AM
12462726 12462726 2/3/2005  12:56:00 PM

Cheers

Rahul
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.