Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems
Recently while working a regulatory report I found myself faced with needing to identify the first (or earliest) of a set of dates. Each date came from a different possible order related to the patient visit, and I needed to chunk through 10 different JOIN-ed tables and get the one with the first / earliest date.
I didn’t think I could use MIN() because that aggregate function takes only one parameter – and I had 10 to deal with. I considered writing a big ole giant CASE statement to compare each of them.
Then I woke up from that bad dream and decided to Google for a solution because – heavens above! – someone else must have encountered the same mess and solved it.
Lo! And behold! They had done so. Boy, was I relieved. Since the solution was not obvious – and even in that Stack Overflow post it’s not obvious (you have to read through the possible answers and realize the Score 91 one is what you need) -- I’m going to relay it here:
In my example, I have:
Ten (10) JOIN’s to the Orders table for the visits I am interested in. Each JOIN gets me the first instance of 10 different Order mnemonics. Each JOIN is aliased as VP01 – VP10.
I get my calculated field as a sub-select like this:
I do need to know how many values I am comparing to get the smallest one. But if I know that, and I’ve got discrete fields for each of those values I can use the VALUES table constructor:
To create a virtual table where each value in parentheses creates a row, with one field in it, holding the OrderDateTime:
The values in each row don’t have a field name – they’re placed by position – which is why you have to get them out with the AS Dates( x ) clause at the end. In this example x is the field name I’ve assigned to the data element in the first position on each row.
That makes a SQL table – and I can SELECT MIN (x) from that!
Which gets me the first / earliest date in the set of 10 fields.
Note! If you only had to get the first date from a subset of data in a single table then you could do an OUTER APPLY for that single table, sort it in the ORDER you want, and then do TOP 1 from that OUTER APPLY set.
But this approach will work for dates or numbers in as many different tables in your SELECT structure as you have!
Thank you Craig! I’d link to Jamie Thomson’s original blog post, but it no longer exists.
Extra Credit
MAX() works too, right? Yes it does.
And…
As ever, if you need help with Meditech DR index creation, reporting, extract or analysis please feel free to give your Iatric Sales Representative a call!
Our Report Writing team can help with Data Repository reporting, NPR report writing, data extract or analysis, and much more. Simply reach out to your Iatric Systems Account Executive or email info@iatric.com to discuss how we can help support your team!
