MEDITECH Reports

Populating Extracts with a Sequential Record (MEDITECH DR)

Tweet

 

The Allscripts spec calls for a list of Surgeons in each patient's ENCOUNTER_PHYSICIAN record set. In Meditech, the data can be found using a sequence identifier and records will be available for any surgeries performed. In the example below, our patient only had 3 surgeries; but the specification requires information for 10.

Our data source for the project is: Meditech DR.  10 separate SQL statements can be used to extract the data to specification, returning a Surgeon Number and Provider Mnemonic where available.  Coding 10 separate statements stacks up like this.

INSERT INTO #AbsProv
SELECT ABSV.VisitID
,ABSSURG.ProviderID
'Surgeon ' + CONVERT(varchar(30), ABSSURG.[ProviderSeqID])
FROM #AbstractVisits AS ABSV WITH (INDEX=[#CEIX_AbstractVisits])
  LEFT OUTER JOIN AbsOperationSurgeons AS ABSSURG WITH (NOLOCK) 
ON ABSV.VisitID=ABSSURG.VisitID AND ABSSURG.[ProviderSeqID] = '1'
GO 
-- skipping statements 2 through 9
INSERT INTO #AbsProv
SELECT ABSV.VisitID
,ABSSURG.ProviderID
'Surgeon ' + CONVERT(varchar(30), ABSSURG.[ProviderSeqID])
FROM #AbstractVisits AS ABSV WITH (INDEX=[#CEIX_AbstractVisits])
  LEFT OUTER JOIN AbsOperationSurgeons AS ABSSURG WITH (NOLOCK) 
ON ABSV.VisitID=ABSSURG.VisitID AND ABSSURG.[ProviderSeqID] = '10'
GO 
What a mess!  For those used to loops in NPR, this can be remedied with a loop.  You can use a cursor to execute a single SQL statement 10 times, once for each Surgeon 1 - 10.

DECLARE @SurgeonCount INT = CONVERT(INTEGER,0)
WHILE @SurgeonCount < 10
BEGIN
SET @SurgeonCount = @SurgeonCount + 1
INSERT  INTO #AbsProv
        SELECT  ABSV.VisitID
               ,ABSSURG.ProviderID
               ,'Surgeon ' + CONVERT(VARCHAR(30),@SurgeonCount)
        FROM    #AbstractVisits AS ABSV WITH (INDEX=[#CEIX_AbstractVisits])
        LEFT OUTER JOIN AbsOperationSurgeons AS ABSSURG WITH (NOLOCK) ON ABSV.VisitID = ABSSURG.VisitID AND ABSSURG.[ProviderSeqID] = @SurgeonCount
        CONTINUE
END
GO

Our last statement implements the query logic once instead of 10 times.  Less code means you can implement changes faster as they are requested.  And still go home on time.

Want an alert when the next post is published? Click the phone and Subscribe to the MEDITECH Reports Blog!