Using the Minimum function in a Group Selection 2

[glthornton]

Hi everyone,
Setup Info:
Version: CR XI
Platform: Win XP Pro
Connection Type: ODBC (RDO) to a SQL 2003 database

I've run into a snag when trying to use the "Minimum" function in a Group Selection formula. I am trying to extract records from a database based upon the first date that a patient was admitted. I thought that by using the mimimum function for as a group formula would work, but it doesn't. Let me give you an example of what I'm trying to do:

Sample Database Data:

MRN   Acct#    Name               Admit_Date
123   F1332    Test,Patient 1     01/05/2003
123   S3233    Test,Patient 1     08/01/2006
234   F9090    Test,Patient 2     08/03/2006
234   F6732    Test,Patient 2     09/15/2006
345   F7633    Test,Patient 3     08/17/2006
456   S6453    Test,Patient 4     09/01/2004

And my report should only contain the data in which the patient first admitted (in this case August 2006), therefore, everything is based on the Admit_Date (Grouped by MRN). So I am only looking for the following data to be returned:

MRN   Acct#    Name               Admit_Date
234   F9090    Test,Patient 2     08/03/2006
345   F7633    Test,Patient 3     08/17/2006

Am I using the correct approach by using the "Minimum" function? Does anyone have any suggestions as a better way to perform this type of extract?

Any and all suggestions would greatly be appreciated.

Thank you,
glthornton

 

[synapsevampire]

Rather than describe what you tried, post the formula.

Why wouldn't you have output for MRN 123??? Yousay that you want the first date for each MRN, so either you neglected to include MRN 123 in the expected output, or you aren't explaining the requirements correctly.

You can group by the MRN, then sort by the dates, and palce the fields in the Group Header and suppress the details and group footer to accomplish the first date for each MRN.

But that isn't consistent with your examples so you need to define what it is that you really want if it differs from my suggestion.

You could also use the formula:

minimum({table.date},{table.mrn})

in the group header or the group footer.

-k

 

[glthornton]

Hey Syn,

The reason why the patient with MRN=123 is not included in my results is because that patient's first admit date was 01/05/2003 and I'm only looking for patient's whose first admit date is for August 2006.

Thanks,
glthornton

 

[glthornton]

This is what I currently have in my Group Selection formula:

{od_ar_demographics.ADMIT_DATE}=minimum({od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER})

glthornton

 

[glthornton]

Syn,

You are absolutely on the right track in getting the data to display. But my question is how can only select the patients within a specified date range, if I were to use any type of record selection. I do not get the correct results that I expect because the record selection throws off the group selection priority.

Thanks,
glthornton

 

[lbass]

For this to work properly you need to use your date field in the group selection also, not in the record selection:

{od_ar_demographics.ADMIT_DATE}=minimum({od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER}) and
minimum({od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER}) in date(2006,8,1) to
date(2006,8,31)

-LB

 

[synapsevampire]

Or eliminate the group selection as I previously suggested, place the fields in the group header and suppress the details and group footer and use the record selection criteria for the date period only, it returns the same data anyway.

-k

 

[lbass]

SV-That would work as long as you used group selection for the date, not record selection--since you need to capture the minimum admission date in the database, not just the minimum selected by the report.

-LB

 

[glthornton]

LB,
I tried your suggested solution. But the problem is that the query is concentrating on the dates in the query and not just the minimal date that is retrieved by the first formula "{od_ar_demographics.ADMIT_DATE}=minimum({od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER})", which is really the one I want to focus on. I'm going to find a way to post some screenshots of my data so everyone can see what my settings and results are looking like. I wonder if using this report as a subreport and pass the date up from the subreport to the main report and then do a date comparison. I'll keep you posted.

Thank you,
glthornton

 

[lbass]

Please reread my solution. First, you should have no date selection in selection formula->record. Then my suggested formula says to return only the row containing the first admission date AND that that date must fall within specified date period. Isn't that what you were looking for?

-LB

 

[glthornton]

Hi LB,
You are correct in that is what I'm looking for, but please take a look at the raw data that I'm working with:

http://www.hcnsvt.org/Hold/Patient_DT_List.jpg

You can see that each MRN has multiple visit dates. And what I'm trying to do is only return back the very first date that the patient was admitted.

Here is my condition statement:

http://www.hcnsvt.org/Hold/Patient_First_Visit_DT_Cond.jpg

And when I use that condition, this is what my results are looking like:

http://www.hcnsvt.org/Hold/Patient_First_Visit_DT.jpg

So based upon those dates that you see in the Patient_First_Visit_DT.jpg picture, I only want to return the patients whos dates are within the last month.

I hope with the photos and description, you'll have a better idea of what I'm looking for.

Thanks,
glthornton

 

[FrequentUser]

glthornton,

I had the same problem a month ago, and finally the solution is exactly what LBass has stated.

you must first pull in all patients, with all admission dates, then filter to dipslay only the first admission date (using minimum function in Group Selection), then filter to display only those with first admission dates in the last Month (admission date = date parameter or date formula criteria, also in Group Selection)

Group Selection formulas are calculated in Pass #2 after Record Selections, so you must place your date selection criteria after the minimum admission date criteria in Group Selection

you could try:
{od_ar_demographics.ADMIT_DATE}=minimum({od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER}) and
{od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER} >= date(2006,8,1) and
{od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER} <= date(2006,8,31)


Crystal Reports XI, MSSQL 2000, ODBC

 

[lbass]

You didn't use the complete group selection formula that I suggested:

{od_ar_demographics.ADMIT_DATE}=minimum({od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER}) and
minimum({od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER}) in date(2006,8,1) to
date(2006,8,31)

The last clause limits the initial admission dates to a specific period.

-LB

 

[glthornton]

Hey guys you were completely correct. And I apologize for digging a deeper hole with this solution than I had to. One of the big things that I have a tendancy to do is look at the number of records it retrieves instead of looking at the entire results. I kept noticing that my record count of 120,849 stayed the same. I think I need to take everyone's advice and stop looking at this particular number and rely on any group or record count fields that I put in. I want to personally thank you for bearing with my stupidity on this one. Just an FYI, I went back to using the formula that I originally used which is:

{od_ar_demographics.ADMIT_DATE}=minimum({od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER}) and
minimum({od_ar_demographics.ADMIT_DATE},{od_ar_demographics.MR_NUMBER}) in LastFullMonth

I will be automating this process so I can merely "Set it. And Forget It".

Thanks again everyone.
glthornton

 

[lbass]

When you use group selection, the non-group selected records still contribute to inserted summaries. If you are subtotaling at the group level you are displaying, your numbers will be accurate, but if you are summarizing across groups, you need to use running totals instead of inserted summaries. You don't have to specify any special evaluation--the running totals will evaluate only the displayed records (this differentiates group selection from suppression where you would have to use evaluation criteria that are the reverse of the suppression criteria).

-LB