Need most recent data for a field 2

[steve053]

Crystal Reports 10
MS SQL 2000

I have a field which is a numeric representation of a date PAT_ENC_DATE_REAL which is in the table PAT_ENC_CURR_MEDS. (The integer portion of the number specifies the date of the encounter. The digits after the decimal point indicate multiple visits on one day.)

If a patient has a visit, this table {PAT_ENC_CURR_MEDS} may or may not be updated. Also, a patient may have multiple visits on the same day, and there is a posibility that the table may be updated during both visits.

Is there a way to pull only the most recent record into my record selection using a formula? I want the most recent PAT_ENC_CURR_MEDS information, even if it is not associated with the specific visit date that I am pulling into my report.

Thanks for your help.

 

[Turkbear]

Hi,
What determines that a record is the most recent..Is the Date part updated? If so, then creating a formula that extracts the number part and using that formula in the Group Selection as something like:
{@DateNumberPart} = Max(@DateNumberPart)

You could also create a command object that uses a function as a part of the select statement's where clause, something like ( I do not know SqlServer's syntax):

Select record from PAT_ENC_CURR_MEDS where
DatepartOfPAT_ENC_DATE_REAL = (Select Max(DatepartOfPAT_ENC_DATE_REAL) from PAT_ENC_CURR_MEDS )





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Post what's in the field, not a description, it could be interpreted different ways.

Sounds like a legacy system or you have a bad dba. I would create a View on the database to expose that field as meaningful data, using 2 fields, a date and the visit count.

And yes, you can return the most recent, group by whatever it is you want the max for (patient, or?) use the group of the record selection:

{table.date} = maximum({table.date},{table.patient})

-k

 

[steve053]

Thanks Turkbear and Synapsevampire for your suggestions.

Post what's in the field

example data:
38123
38123.1

In this example, 38123.1 is the most recent data for this patient

Sounds like a legacy system or you have a bad dba

No, not a legacy system. There is a date field attached to each record in the table.

This not a normalized database, and the records are being pulled from a propriatary application.

{PAT_ENC_DATE_REAL} is a number field.

When our system is back up I'll try testing

{table.PAT_ENC_DATE_REAL} = maximum({table.PAT_ENC_DATE_REAL},{table.patient})

in the Crystal Group Slection.

I have one question if I use this. Do I have to group the data by {table.patient} in the design area of Crystal for this to work? I would guess not, but I don't have a lot of experience.

Thanks again.

 

[lbass]

You should use your date field instead of your date-in-number-format field in the above formulas. If the datenumber field is all you have to work with, then you probably need to convert it to a date first. I can't really tell what date your example number represents. Can you provide a couple of examples and indicate how you are formatting it (MM dd yyyy) for clarity?

-LB