Print Page | Close Window

Minimum Date Filter - Select Criteria

Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2192
Printed Date: 28 Apr 2024 at 8:58pm


Topic: Minimum Date Filter - Select Criteria
Posted By: romert
Subject: Minimum Date Filter - Select Criteria
Date 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


Replies:
Posted By: Lugh
Date 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})

Posted By: rahulwalawalkar
Date 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


Print Page | Close Window