Identifying original entry date against multiple records

[RBTB9]

Hi,

I am trying to identify when a patient(PAT_MRN_ID) first was assigned to a Provider(PROV_NAME) by date(EFF_DATE). Most patients have changed providers over and over, so I want to know who the first provider was assigned to a specific patient based on the date(EFF_DATE).
I have tried using 'First' or "min" in the EFF_DATE field, but I keep getting all records.



DATA SAMPLE:

PAT_NAME PAT_ID PCP PROV_NAME EFF_DATE TERM_DATE
TEST, CUSTOMER 111111 1234 JONES, DAVID 28-AUG-02 03-DEC-07
TEST, CUSTOMER 111111 5678 DOE, JANE 04-DEC-07 18-MAR-09
TEST, CUSTOMER 111111 10 NO PCP 19-MAR-09 15-SEP-10
TEST, CUSTOMER 111111 5678 DOE, JANE 16-SEP-10 22-JUN-11

I want to be able to just get the record with the oldest EFF_DATE.

[pbaldy]

This should get you started:

http://www.baldyweb.com/LastValue.htm

[RBTB9]

Thanks.
Not much experience in writing SQL.
Can this be done with an expression and/or criteria?

[pbaldy]

Possibly, but it would likely be more complicated and less efficient. You can create it in design view, using the Totals icon.

[RBTB9]

Sorry for the follow-up question (learning this as I go).

How would I write this in Design View?

[pbaldy]

Add those 2 fields to a new query, click on the Totals icon and change the Group By to Min on the appropriate field.

[RBTB9]

I have tried using the Min in the Group By (as well as 'First'), however I keep getting the same set of data.

[pbaldy]

Did you have other fields included? Can you post a sample db here?

[RBTB9]

Here is my attempt at SQL.
SELECT dbo_patient.pat_name, dbo_pat_pcp.pat_mrn_id=123456, dbo_pat_pcp.pcp_prov_id, dbo_clarity_ser.prov_name, Min([eff_date])from[dbo_pat_pcp], dbo_pat_pcp.term_date, dbo_date_dimension.month_Name, dbo_date_dimension.Year

Getting error that pathway could not be found

[pbaldy]

Try

SELECT pat_mrn_id, Min([eff_date]) As MinDate
FROM dbo_pat_pcp
GROUP BY pat_mrn_id

[RBTB9]

I tried that and I am now getting an ODBC error. Query that I had previously built still working.
Question: How would I add in the other fields for the report once I get the first part working?

[pbaldy]

You would join that query in another query, as shown on the link.