September 19, 2011 at 4:18 pm
Hi all,
I have a table that holds patient encounters in it, and another table that holds all of the patient procedures that were done (each encounter can have one or more procedures). I need to write a query that will assign a category to each encounter based on what procedures (if any) were done. I'm looking for a recommendation on a general approach that's going to be optimal for about 3.5 million encounters (but scalable, naturally).
-- Sample Encounter Table Data
SELECT 1 AS EncID UNION ALL
SELECT 2 AS EncID UNION ALL
SELECT 3 AS EncID UNION ALL
SELECT 4 AS EncID
-- Sample Procedure Table Data
SELECT 1 AS EncID, 'OB212' AS ProcID UNION ALL
SELECT 1 AS EncID, '81002' AS ProcID UNION ALL
SELECT 3 AS EncID, 'D0120' AS ProcID UNION ALL
SELECT 3 AS EncID, 'D0272' AS ProcID UNION ALL
SELECT 4 AS EncID, '99212' AS ProcID UNION ALL
SELECT 4 AS EncID, '36415' AS ProcID
-- 81002, D0272, and 36415 are incidental and would not
-- factor into determining the category
--Sample Results that I'm hoping for
SELECT 1 AS EncID, 'OB Visit' AS 'Category' UNION ALL
SELECT 2 AS EncID, 'Other' AS 'Category' UNION ALL
SELECT 3 AS EncID, 'Dental Visit' AS 'Category' UNION ALL
SELECT 4 AS EncID, 'Medical Visit' AS 'Category'
I know I could do this with a cursor, but I keep reading about how evil they are 🙂 I've also thought I could probably write a stored procedure or a function that accepts the EncID and returns a category based on what procedures it finds. I'd also be open to using SSIS to do a cursor type operation on this beast. I'm really just wondering what would be considered a best practice for this type of situation.
Altering the design of the procedures table is not an option since this is a 3rd party application.
Sorry for the duplicate post, I don't know how I accidentally put this in the SQL 7, 2000 forum the first time!
September 19, 2011 at 4:24 pm
ajenawsu (9/19/2011)
SELECT 1 AS EncID, 'OB Visit' AS 'Category' UNION ALLSELECT 2 AS EncID, 'Other' AS 'Category' UNION ALL
SELECT 3 AS EncID, 'Dental Visit' AS 'Category' UNION ALL
SELECT 4 AS EncID, 'Medical Visit' AS 'Category'
On what basis are you defining these "Categories"? what Procedures fall under which category? I think this is one of the missing pieces in this puzzle.
September 19, 2011 at 4:28 pm
... and if there are procedures from more than one category in an encounter, how to determine which category to include the encounter in? Is there a priority to classifying encounters into categories, or will an encounter never include procedures from more than one category? and how is that rule enforced?
September 19, 2011 at 4:39 pm
Okay... now I know that data belongs in your tables -- not in your code. In a perfect world. But there's not really a mechanism for categorizing the encounters in the source database, and my ability to construct something there is limited. So I think they might have to be categorized on the fly RBAR, for example:
encounters with...
... procedure codes OB212, OB213, OB214 are OB
... procedure codes 99212, 99213, 99214 are medical
... procedure codes D0120, D0130 are dental
... and so on?
September 19, 2011 at 4:42 pm
paul_ramster (9/19/2011)
... and if there are procedures from more than one category in an encounter, how to determine which category to include the encounter in? Is there a priority to classifying encounters into categories, or will an encounter never include procedures from more than one category? and how is that rule enforced?
Very good question, another thing I'm not sure about. There really should never be procedures from more than one category but in order to deal with garbage data I will probably have to set a priority to classifying them.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply