Selecting the Minimum value for a date and time field

[Former Member]

I have selected a field that can often times have three different dates and times associated with it. What I am trying to do is select the earliest or first date and time out of all three and omit the other two dates and times from showing up on the report. Is there any way I can select just the first time and not the other two? Thanks for any help with this!

[Former Member · Accepted solution]

Try this formula in the Selection Expert...

{TableName.DateField} = Minimum({TableName.DateField})

HTH,

Jason

[Former Member · Accepted solution]

Jason,

I tried that and feel that I am getting closer but when I put in the formula you gave me, I got an error indicating that it needs to be evaluated later.

Here is what I have:

{TableName.DateField} in DateTime (2011,05,0,0) to DateTime (2011, 05,0,0) and

{TableName.NameField} = "Name" and

{TableName.CodeField} in ["10","15"] and

{TableName.DateField} = Minimum({TableName.DateField})

[Former Member · Accepted solution]

This is because the Minimum function cannot be evaluated until the "WhilePrintingRecods" pass of the report. I was thinking that CR would simply not pass this to the server and wait to evaluate it... Guess not.

Pull it out of the Selection Expert and instead, use it as a suppression formula for the Details section of the report.

IF {TableName.DateField} = Minimum({TableName.DateField}) THEN FALSE ELSE TRUE

The rows will still be pulled from the database but they will be suppressed and not visible on the report.

Jason

[Former Member · Accepted solution]

Jason,

First, thank you for working with me on this. However, I am just a beginner at Crystal and I am not familiar with the details section and creating a suppressed formula. Would you mind letting me know where that is?

Thanks again,

IPFWRecords

[Former Member · Accepted solution]

No problem... Here's what you do...

1) Switch to "Design" tab of the report if you're currently looking at preview of the report.

2) Look at the left side of the report. You'll see a gray area with labels for each of the report sections (Report Header, Page Header, Details... ect).

3) Right click in the area that says Details and choose "Section Expert". This will open the Section Expert window.

4) Look at the left window and verify that Details is highlighted in gray. (select it if it's not...)

5) On the right you'll see "Suppress (No Drill-Down)" ... Click the formula button next to it.

6) This will open the Formula Workshop and you will place your suppression formula there.

Key to remember about suppression formulas...

1) They must be boolean expressions. That is, something is suppressed or it isn't.

2) Anything that evaluates to "TRUE" gets suppressed. Anything the evaluates to "False" is left alone.

-- This can be a little counterintuitive because it's like working with a double negative...

{TableName.Name} = "Bob"

// Bob gets suppressed. Everyone else is visible

IF {TableName.DateField} = Minimum({TableName.DateField}) THEN FALSE ELSE TRUE

// Only dates that are equal to the minimum date are visible. All others are suppressed

Jason

[Former Member · Accepted solution]

Jason,

I did that and it appeared to have suppressed almost everything. I did have a 3 pages of the report. Now I have one entry on the report (1 page). Help?? LOL

IPFW Records

[Former Member · Accepted solution]

Remove the suppression formula and verify the dates in that field. It's a good idea to have a mental note of what records you want to be visible and which ones you want gone.

Without actually being able to see the data myself, it's tough to know where the breakdown is occurring.

[Former Member · Accepted solution]

In short, I work for a police department. I have two fields in one table. One field contains the Signal Code and the other contains the date and time. What I am trying to do is get the minimum date/time for a specific code. For instance, there can be multiple codes that have dates and times associated with it. I am selecting a specific code (that could have 3-4 different times) and I am wanting the minimum of that. Not the total minimum from the whole day. Sorry about the ocnfusion:)

[Former Member · Accepted solution]

Here's the easy way to do that...

1) Group by the code field.

2) Sort the report by the date/time (Ascending order)

3) The code field will automatically be shown in the Group 1 header... (GroupName1)

4) No do as Sastry suggested and place the date field in the group header next to the groupname.

5) Get back into the Section Expert and completely suppress the details section... (Get rid of the formula and place a check in the suppress check box.

You should be good to go

Jason

[Former Member · Accepted solution]

Hi, Jason!

It's me again. I keep researching the issue that I am having and can't seem to locate the answer. I know that you have been a big help thus far. I believe my problem is that I'm not explaining my issue very well. LOL! Suppose I have a field that has 3 different dates and times associated with it like response time. I am trying to only show the "initial" response time for each incident. Often times, I can have three different units arriving at the same time. I just want the report to show the date and time for when the first unit arrived.

When doing the minimum formula, I only get the minimum date and time for the WHOLE day, not for each incident.

Hope this explanation helps:)

IPFW Records

[Former Member · Accepted solution]

No problem. You're getting close. You just need to create an incident group on the report.

Without groups your report should resemble this:

ColHead1	ColHead2	ColHead3	ColHead4
---------	---------	---------	---------
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value
Col1Value	Col2Value	Col3Value	Col4Value

Adding a group will have it looking more like this...

IncidentGroup 1
	ColHead1	ColHead2	ColHead3	ColHead4
	---------	---------	---------	---------
	Col1Value	Col2Value	Col3Value	Col4Value
	Col1Value	Col2Value	Col3Value	Col4Value
	Col1Value	Col2Value	Col3Value	Col4Value
	Col1Value	Col2Value	Col3Value	Col4Value

IncidentGroup 2
	ColHead1	ColHead2	ColHead3	ColHead4
	---------	---------	---------	---------
	Col1Value	Col2Value	Col3Value	Col4Value
	Col1Value	Col2Value	Col3Value	Col4Value
	Col1Value	Col2Value	Col3Value	Col4Value
	Col1Value	Col2Value	Col3Value	Col4Value

IncidentGroup 3
	ColHead1	ColHead2	ColHead3	ColHead4
	---------	---------	---------	---------
	Col1Value	Col2Value	Col3Value	Col4Value
	Col1Value	Col2Value	Col3Value	Col4Value
	Col1Value	Col2Value	Col3Value	Col4Value
	Col1Value	Col2Value	Col3Value	Col4Value

Now assume in this instance that each "IncidentGroup" is it's incident code and the details below are the individual responses.

Also assume that the response details are sorted in order by date/time in ascending order.

Based on that you're saying, you only want the "first" detail record for each group...

To get that, simply move the field from the details section to the "IncidentGroup" header. (Not the page header or report header)

-- Note: moving the fields to the group footer will give you the last record in the group...

Now, to get rid of the records that you don't want to see, suppress the details section. You'll have something that looks like this...

IncidentGroup 1 1stIG1Col1Value	1stIG1Col2Value	1stIG1Col3Value	1stIG1Col4Value	

IncidentGroup 2 1stIG2Col1Value	1stIG2Col2Value	1stIG2Col3Value	1stIG2Col4Value

IncidentGroup 3 1stIG3Col1Value	1stIG3Col2Value	1stIG3Col3Value	1stIG3Col4Value

Reread my last post and follow all 5 steps. They still apply to your described scenario.

Jason

[Former Member]

I do not know if this will be the solution you need, but have you tried using a SQL Expression in crystal?

I do not have crystal in front of me at the moment, but, if memory serves me, it would be something like:

{%SQLMinPerUnit}

(

select min("DateField")

from a TableName

where "TableName"."UnitID" = a."UnitID"

)

If i have the syntax correct, this would provide the minimum date per unitID.

You could then use this along with Jason's suggestion to group and suppress, by suppressing any detail section within the UnitID group that does not equal the SQL Expression date.

[Former Member]

Hi

Create a group on selected field, and place your date time field on your group header. This will pickup only the first time. Now suppress your detail to show only first record.

Thanks,

Sastry