Thanks Drew....if the statuses are different I want to return the most recent Rx (based on DoseStartDate).  If all the statuses are the same I want to return all the Rxs

Thanks!  And that would return all values if the statuses are the same?

Sorry....yes to all of your questions. All of the data looks just like the example

And sorry if I wasn't clear....the field is called 'Strengthu' the examples I gave are the values. So, would I put Strengthu in the formula?

Is this a tool in SQL Server?

Thanks everyone. I found a calendar that has fiscal year that I can work from.

Lowell, how does T2 know to look at last year's data for the same days?

pietlinden, I'm building this via sql in sql server.

That worked....thanks!

That's what I figured....but on one of my records it's still showing the Expected for the 0 OCC code...hmmmm

I got it! Here is what I did:

select top 1 with ties

column name,

column...

Crap, I thought it worked (technically it did)....but, I didn't take into account multiple patient ids.....meaning, I need the latest status (based on date) for each patient ID. Right...

That worked Sean....thanks!

Missed that part! Sorry. I'll give it a try

But, wouldn't that just put it order? I only want the status with the most current date to display. The goal of the report is to show the...