3 Replies Latest reply on May 20, 2012 11:05 PM by Dimitri.B

# Show the first ever date for a patient, filtered

So I have a pharmacy.

Transactions in our table look like this:

Patient Name, Rx Number, Fill Date, Blah Blah Blah

Patient Name, Rx Number, Fill Date, Blah Blah Blah

Patient Name, Rx Number, Fill Date, Blah Blah Blah

Patient Name, Rx Number, Fill Date, Blah Blah Blah

I would like to see a list of patients who filled their first ever prescription with us this past week.

I've got a list of patients and their first fill date.

How do i filter this down successfully?

• ###### 1. Re: Show the first ever date for a patient, filtered

Can you confirm that you have two data sources: transaction list and list of customers with first fill date?

If yes, data blending may work. Alternatively, table calculations might do the trick.

Attaching desensitized data sample would help.

• ###### 2. Re: Show the first ever date for a patient, filtered

Ok here's the table layout:

Patient

ID, Last Name, First Name, DOB, Address, Created Date, Last Access, etc etc

Rx

Patient ID, Rx Number, Refill Number, Date Filled, Drug ID, etc etc

I join these two (along with two or three other tables) to get

Patient, Rx Number, Date Filled, etc etc etc.

Selecting Patient for rows and min(rx.filldate) gives me a table of all the FIRST EVER fill dates for ALL patients.

• ###### 3. Re: Show the first ever date for a patient, filtered

You can try using calculated field with a formula like this:

`//determine if ordered this week `

`DATETRUNC('week',NOW()) <= [Order Date] //start of current week `

`AND [Order Date] < DATEADD('day',7,DATETRUNC('week',NOW())) //end of current week`

replace [Order Date] with your date of first fill and use this calculated field as your filter, keeping 'True'.