This is the standard every vendor should strive to!

Its not often that I gush over customer service.  I will express appreciation and fill out surveys when I’m happy, and leave it at that.

We have been using the iPeople Connect suite as our data repository for just over a year now.   Having our Meditech data available in SQL Server has made it possible to implement solutions that would’ve required expensive vendor assistance in the past.

The iPeople team has always provided stellar support and we’ve been very happy with them.  Today though, they blew us away with their dedication to customer service by reaching out proactively on an issue.

I am working on a project that uses data pulled from nursing interventions.  The project is not yet live and until an hour ago,  I hadn’t had a chance to work on it for the past week.

I noticed that there was no information crossing over from Meditech.   It did’t take me long to discover the reason.  I had screwed up the start date of the download query.

iPeople ECHO allows us to customize download queries in order to ensure the we can get only what we need.   I decided to take advantage of that,  but in doing so I had neglected to add a line of code.

I started the download,  and was immediately distracted by another, unrelated task.

As I was finishing that up my phone rang.   It was Drew Sher from iPeople.

I was a bit shocked as there were no major issues with our system.  I knew this because I was looking at it at the time.

Drew had noticed the error in the download I was working on and called me to help me get it working the way I wanted.

Seriously,  how awesome is that?!

It would be like looking out at your driveway and seeing your dealer’s mechanic pull up to fix something because your car called them!

This is a big kudo’s to iPeople and their excellent staff!



MEDITECH and iPeople Echo: Providing near real time patient census data

Anyone whose worked with the Meditech Magic Hospital Information System knows that sometimes it can be a challenge to pull useful and timely statistical data from it.

Late last year, the Huron-Perth Healthcare Alliance implemented the iPeople Connect product which includes Echo an application that allows us to build our own SQL Server based data-repository,  specifying what data we want from our Meditech system,  and how frequently to update it.

What this does is allows us to provide our client base with a broad range of reports and reporting tools instead of the text based reports generated by Meditech’s NPR report writer.

One of the very first requests we received was to provide near real-time patient census data.

The project, dubbed Atlas,  asked for the following data elements:

  • Location
  • Bed Count
  • Patient Count
  • Empty Beds
  • Occupancy Rate
  • Isolation Patients
  • Pending DC dates prior to current date
  • Pending DC dates on current day
  • ALC patient counts

Anyone who has ever faced pulling data from Meditech knows that it be difficult figuring out where the data is stored.

With the help of iPeople Scout,  an application that allows us to search the Meditech Dictionaries,  and view joins,  we found that the best Meditech source table was ADM.ADMStatsInLocation.   This table gave us the bed census data for each location, as of the midnight run on any given date.

While all this seems straight forward,  there was a complication in how to define a bed.

I know what you’re thinking,  “a bed is something that you sleep in, or on…and its where hospitals keep patients!”.    You’d be correct,  except that our organization may have several entries for the same physical bed in the Meditech dictionary.    The reason for this is that some beds may be either acute,  or chronic.  It all depends on what type of patient gets admitted.

My preference is that data elements be identified programmatically.    After spending several hours running test queries,  trying to ensure that our virtual bed count matched our physical bed count I realized that this was impossible.   The reason is that those pesky humans might get in there and create a bed that wouldn’t fit the existing naming convention.

I opted instead to use an associative array within the PHP script that contained the official bed counts for each facility and location.

The next, and most difficult challenge to solve, was how to identify ALC patients.

For those unfamiliar with the term,  ALC means (Alternative Level of Care).   This term refers to patients occupying acute beds,  who are ready for discharge,  but do not have a place to go.   They still require care (long term facility,  chronic or palliative etc),  but there are no beds for them to go to.

When I first reviewed the specifications,  I figured this would be a flag,  perhaps a custom query somewhere within ADM.   I was somewhat mortified to learn that the only way to identify an ALC patient was through ALC orders.

This was complicated by the fact that a patient who has been in hospital for some time,  could be designated ALC several times through their stay.

Within the organization we have several ALC order types in our OE.ORD dictionary.

  • Identify:  indicates that the patient is an ALC patient
  • Change:  changes the order
  • Discontinue:  discontinues the order,  however the patient remains in that bed, and
  • Discharge:  the patient is discharged

It took several days to work this out,  and it was only with the assistance of our Nursing Informatics team,  as well as input from Bed Management that I managed to figure it out at all.

In order to identify,  and add a patient to the ALC tally,   while compiling the information,   I query OE.ORD for each in patient,   looking for any ALC orders for their current visit.

I pull the data sorted in descending order by order date and time.

This is the logic I use:

If the query gets no results,  then patient is not now,  nor has ever been an ALC patient.   I then set the ALC flag within the XML node for this patient to “no”.

If there are results,  I check to see if the last result is a discontinue or discharge.   If it is,  this indicates that the patient was an ALC patient.   I set the ALC attribute to “expired” for these patients.

The only option left at this point is that the patient is an alc patient.   I increment the appropriate counters,  and set the ALC flag to “yes”,  which is used for display and functionality as the user can pull up a patient’s entire ALC order history in this case.

Other data types,  such as Isolation and Pending Discharge are determined by checking custom query fields which are exported into the SQL Server database.

All this information is formatted into an XML document by a PHP script.   In order to save time on processing,  the script is run every 10 minutes by a cron job,  and the results written to a file on the web server.

When a user accesses the Atlas home page,   it is this file that is accessed,  saving the user a long wait each time they go looking for data.

There is functionality that checks the age of the data,  and,  if it is older than 10 minutes,  the user be flagged, and notified to call IT to report a problem.

The front end is a simple web page,  with JQuery/javascript programming that will take the XML document and dynamically create tables using Document Object Model traversal methods.

The user has the ability, via checkboxes, to limit which sites within the organization they want to see.

Upon page load,  each site is collapsed.   The user can view or hide site specific locations by clicking on the desired site.

The screenshot below shows the Stratford General Hospital locations expanded.


Other functionality includes the ability to view a list of patients in each location.   The list only uses account numbers,  reducing the risk of a privacy breach.

Within the individual patient list,  the user can view a history of ALC orders for patients that have them.

They can also view a list of patients for whom there is a pending discharge.

Managers and other stakeholders receive a static copy of this report via email every morning at 7am.

The iPeople DR,   combined with the ability to use modern programming languages has allowed us to improve patient flow, and service delivery to our patients.

Dealing with Meditech Dates (including Clinical formats) in SQL (Meditech, iPeople Echo, SQL Server)

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

CONVERT([data-type(date)],database field,format)

The format 112 indicates  YYYYMMDD

Here is a reference on format codes. 

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)

D10027 9285 1 802279697 RS4X000115 NULL NULL

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)

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)

--b.NAME as name,
a.EXAMACCOUNT as account,
a.EXAMNUMBER as number,
REPLACE(b.ZIP,' ','') as zip, as lat,
c.long as lon,
CONVERT(varchar(12),DATEADD(DAY,CONVERT(int,a.EXAMDATES),'3/1/1980'),106) as dates
IPEOPLEDATA.IPEOPLE_DR.Rad.RadiologyExamInformation as a
geolocation as c ON REPLACE(b.ZIP,' ','') = REPLACE(c.code,' ','')
radexamcodes as d ON d.exmnemonic = a.EXAMMNEMONIC
a.EXAMDATES >= @from
a.EXAMDATES <= @end
AND = 1

A sample result is below with altered data

account exam number zip lat lon dates
XXXXXX MAMSCBIL xxxxx LNLNLN 83.3691 -64.8376 04-Mar-15

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.