September 19, 2011 at 3:46 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.
-- 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.
September 19, 2011 at 3:53 pm
ajenawsu (9/19/2011)
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.
....
I'm really just wondering what would be considered a best practice for this type of situation.
Why don't you just have your procedures table store a ProcedureCategoryID which will associate it to your text category in your 'ProcedureCategory'?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 19, 2011 at 3:56 pm
Altering the design of the procedures table isn't an option here since it is a 3rd party application. I also don't want duplicate rows in my results, i.e. even though an encounter might have two procedures it can only have one category.
By the way, I am using SQL 2008 R2. Not sure how I accidentally posted this in the SQL 7, 2000 forum! :ermm:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply