For those regular followers of this blog who don’t realize it, I work in healthcare.
While I usually don’t post industry specific things here, I’m going to start as one of the purposes of this blog is to keep track of things I learn, that I’ll probably forget as I move onto the next project.
Those of you are are familiar with Meditech know that there are many instances where dates are stored in a “Clinical Date” format. That is, the number of days since 3/1/1980.
We recently started using iPeople Echo, which we purchased as part of their Connect suite, to copy data from Meditech’s proprietary Dictionaries (the Meditech term for tables) into SQL server.
When querying the data, most of the dates were in the Meditech standard for Canadian sites (YYYYMMDD).
When writing a sql query that involves dates, its often necessary to convert the Meditech date to a SQL datetime datatype.
The CONVERT function does this, the trick is knowing the correct format.
Here’s an example where it converts the ADMITDATE to a SQL date type.
The parameters are
The format 112 indicates YYYYMMDD
The Meditech clinical date format is trickier to deal with as it represents the number of days passed since 3/1/1980.
If you’re writing a query that requires you to handle clinical dates, its best to use a Stored Procedure so that you can do the conversion once, and use the results in your actual query. I’ll post a live example below, but for now, here’s the base query.
CAST (DATEDIFF(day,'3/1/1980',CONVERT(date,@startdate,112)) as int)
In order to use the result of this statement in a WHERE clause pulling from iPeople DR, it needs to be converted to a number.
As you’ll see in this partial example row from our Radiology Exam Information table, the field ‘EXAMDATES’ is a number. (and, for some reason, a plural)
When looking for a range of dates, user’s prefer to be able to enter a date range that makes sense to them, its up to as, as programmers to accommodate this. (and to make our own lives easier while testing queries)
If you use the above code fragment to convert the examdate field above your code would look like this
The result would be 02 Aug 2005, or using the 112 format 20050802
I mentioned stored procedures earlier and will post an example, but it will require some explanation.
The following stored procedure was written for a project that gives users geographic information (primarily driving distance to each of our facilities) for patients who presented for certain types of radiology exams.
The information is going to be used to locate new equipment and tweak service delivery.
Seeing as I need to query the radiology exam table used as an example above, I wanted an easy way to set date ranges without doing a lot of work in the PHP script that calls this stored procedure.
The stored procedure runs on another SQL Server instance, with the iPeople Data server added as a linked object. You’ll see in the query how I reference those tables.
I do this so I can write stored procedures while not affected the actual iPeople database, and, so I can use custom tables to contain select data (so I don’t have to change the query if exam codes change), as well as store geographic data such as lat and long for each postal code.
You’ll also see, that I use the Meditech date format YYYYMMDD as the procedure parameters (@startdate,@enddate) and then convert them to the clinical date format (@from and @end) prior to the select running.
The @from and @end variables are used in the WHERE select to limit the results to the desired date range.
ALTER PROCEDURE [dbo].[radpatientsforgis] @startdate varchar(8), @enddate varchar(8) AS DECLARE @from int DECLARE @end int SET @from = CAST (DATEDIFF(day,'3/1/1980',CONVERT(date,@startdate,112)) as int) SET @end = CAST(DATEDIFF(day,'3/1/1980',CONVERT(date,@enddate,112)) as int) SELECT --b.NAME as name, a.EXAMACCOUNT as account, a.EXAMMNEMONIC as exam, a.EXAMNUMBER as number, REPLACE(b.ZIP,' ','') as zip, c.lat as lat, c.long as lon, CONVERT(varchar(12),DATEADD(DAY,CONVERT(int,a.EXAMDATES),'3/1/1980'),106) as dates FROM IPEOPLEDATA.IPEOPLE_DR.Rad.RadiologyExamInformation as a INNER JOIN IPEOPLEDATA.IPEOPLE_DR.Rad.RadiologyPatient as b ON a.EXAMACCOUNT = b.ID INNER JOIN geolocation as c ON REPLACE(b.ZIP,' ','') = REPLACE(c.code,' ','') INNER JOIN radexamcodes as d ON d.exmnemonic = a.EXAMMNEMONIC WHERE a.EXAMDATES &gt;= @from AND a.EXAMDATES &lt;= @end AND b.ZIP IS NOT NULL AND d.active = 1
A sample result is below with altered data
Note: I did not receive any consideration from iPeople for this post, or the link. I have no issues with letting people know about products and applications that make my life easier.