Selecting earliest date

[Former Member]

I have a MSSQL view that I have pulled into Crystal Reports.

The table looks something like this:

PatId SpecDt_1 SpecDt_2 SpecDt_3 SpecDt_4 SpecDt_5

PSN10812085TN01 1/9/2008 1/9/2008 1/9/2008 1/21/2008 1/5/2008

PSN10815697TN01 1/14/2008 1/7/2008 2/2/2008 1/14/2008 2/11/2008

PSN10815041TN01 1/15/2008 3/1/2008 2/22/2008 3/1/2008 1/14/2008

PSN10817602TN01 1/10/2008 1/10/2008 2/1/2008 1/15/2008 1/16/2008

PSN10820762TN01 1/24/2008 1/25/2008 1/23/2008 1/1/2008 2/8/2008

For each patient (PatId), I am trying to select the earliest event date (SpecDt_#). As you can see there are 5 event dates for each patient. I have tried using the minimum function, but it will only look at 2 dates for comparison. Any suggestions would be greatly appreciated.

I have had to learn CR on my own, so if you respond please take that into consideration.

[Former Member · Accepted solution]

Hi Amanda,

To get the records related to the latest date or old date you need to insert a group on patient ID and then go to the menu option report-->record sort expert and add the date field and use descending order. Now place all the fields in group footer instead od details section so that it will show the records with max date for each group. If you want to see the min date then in the record sort expert use descending order.

Regards,

Raghavendra

[Former Member · Accepted solution]

I tried doing what you said, but I don't know that it is doing what I am needing it to do.

The table has one patient per row. They may have up to 5 different specimen collection dates in that one row. I have a formula field that I have created called SpecDt. The value that needs to go in that field is the earliest of the 5 specimen collection dates. There should be a date for every patient.

So for the example above, the value for SpecDt should be as follows:

PatId----

---

SpecDt

PSN10812085TN01 1/5/2008

PSN10815697TN01 1/7/2008

PSN10815041TN01 1/14/2008

PSN10817602TN01 1/10/2008

PSN10820762TN01 1/1/2008

If I have misunderstood what you suggested, just let me know.

Amanda

Edited by: Amanda Ingram on Sep 30, 2008 5:34 PM

[Former Member · Accepted solution]

Try this formula that returns earliest date for each patID

dateTimeVar array arr;
redim preserve arr[4];
arr:=makearray({SpecDt_1},{SpecDt_2},{SpecDt_3},{SpecDt_4},{SpecDt_5});
Minimum(arr)

Regards,

Raghavendra

[Former Member · Accepted solution]

I had to tweak the code just a bit because it was acting funny with regards to missing values for some of the fields. Other than that, though, it worked like a charm.

Thanks,

Amanda