How do I pull all OBX-5s where OBX-3.1=’SS003′

by

Question:

I am working on a syndromic surveillance feed for the CDC and hope you can help me with an HL7 SQL question. The messages contain multiple OBX lines. In those lines, I need to pull: 

data in OBX-5
from the segment where OBX-3.1 = ‘SS003’

The order and number of OBX segments varies, so I can’t always specify a specific OBX segment.
Is there a way to do this in HL7 Spy? I’m including a set of OBX segments below as an example.

OBX|1|NM|11289-6^BODY TEMPERATURE:TEMP:ENCTRFIRST:PATIENT:QN^LN||97.4|degF^FARENHEIT^UCUM|||||F
OBX|2|NM|59408-5^OXYGEN SATURATION:MFR:PT:BLDA:QN:PULSE OXIMETRY^LN||97|%^PERCENT^UCUM|||||F
OBX|3|CWE|8661-1^CHIEF COMPLAINT:FIND:PT:PATIENT:NOM:REPORTED^LN||||||||F
OBX|4|CWE|8661-1^CHIEF COMPLAINT:FIND:PT:PATIENT:NOM:REPORTED^LN||||||||F
OBX|5|NM|21612-7^AGE TIME PATIENT REPORTED^LN||60|a^YEAR^UCUM|||||F
OBX|6|CWE|SS003^FACILITY / VISIT TYPE^PHINQUESTION||225XN1300X||||||F
OBX|7|TX|44833-2^DIAGNOSIS.PRELIMINARY:IMP:PT:PATIENT:NOM:^LN||per ems pt with increased confusion. pt also presents with ulcers to bilateral feet||||||F
OBX|8|AD|SS002^TREATING FACILITY LOCATION^PHINQUESTION||701 10TH STREET SE||||||F

Answer:

The best way to accomplish this is to write a small amount of Custom Code. In the example below, all OBX-5 values where OBX-3.1 = ‘SS003’ will be placed in a file called obx5s.csv. Once all the messages have been processed the file will automatically be opened in excel, if it is installed on the computer.

private StreamWriter _writer;
private string _fileName = Path.Combine(Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop),"ob5s.csv");

public override void OnStart()
{
  _writer = new StreamWriter(_fileName,false);
}

// Called once or many times based on RunTypeRequested
public override void Run()
{
  // Get an HL7 Message in parsed format
  HL7Message message = GetParsedMessage();
  foreach(var obx in message.OBXs)
  {
    if(obx[3,1,1] == "SS003")
    _writer.WriteLine("{0}", obx[5]);
   }
}
public override void OnFinish()
{
   if(_writer!=null)
   {
     _writer.Close();
     _writer = null;
     System.Diagnostics.Process.Start(_fileName);
   }
}