November 12, 2015 at 4:44 pm
Hello All,
This has been a challenge. Any help is very much appreciated.
I have to find the UNIT/PERDAY value of a prescription sig code.
Example 'Take 1 tablet twice daily' would equal 2 per day. However I only really need the INT result so 2 is the answer. I started to do this with nested replaces to get all INT values first, however this would be a very long chunk of code that may be very hard to maintain. I thought I would turn to the magic on this forum for some ideas on how to complete this.
If you look at a set a pharmacy instructions its basically two pieces I'm looking for MAX(UNITS) * MAX(FREQUENCY) i.e. MAX(1-2 tabs) = 2 , MAX(Every 4-6 hours) =6
2 Units * 6 Frequency = 12
I have a small table with some examples. I realize that nothing is perfect and something will fall through the cracks in a situation like this.
The Instructions column is where I need the calculations. I have included two columns with the desired result and my explanation of why.
Here is a small legend of Pharmacy Sig codes
QD = 1
QID = 4
QHS =1
BID =2
TID =3
CREATE TABLE #Pharmacy (RXID INT IDENTITY(1,1), INSTRUCTIONS VARCHAR (100), NeededResult INT, Explanation VARCHAR(200))
INSERT #Pharmacy VALUES ('1 PO BID', 2, ' 1 is the unit, BID is twice daily so 1 * 2 =2')
,('1 PO TID', 3, ' 1 is the unit, TID is three times daily so 1 * 3 =3')
, ('1 PO QD', 1, ' 1 is the unit, QD is once daily so 1 * 1 =1')
, ('1-2 PO QD', 2, ' 2 is the unit because it is max value, QD is once daily so 2 * 1 =2')
, ('1-2 PO BID', 4, ' 2 is the unit because it is max value, BID is twice daily so 2 * 2 =4')
, ('One to two tabs PO BID', 4, ' 2 is the unit because it is max value, BID is twice daily so 2 * 2 =4')
,('one tab PO TID', 3, ' 1 is the unit, TID is three times daily so 1 * 3 =3')
,('Take one tablet twice a day', 2, ' 1 is the unit, twice daily so 1 * 2 =2')
,('Take one tablet every 4-6 hours', 6, ' 1 is the unit, every 4-6 hours max is 6 so 1 * 6 =6')
SELECT * FROM #Pharmacy
DROP TABLE #Pharmacy
***SQL born on date Spring 2013:-)
November 12, 2015 at 5:38 pm
Use DelimitedSplit8K and toss all the non-numeric data? Then multiply?
November 12, 2015 at 6:02 pm
I did this and it works, however I would like to see if anyone has a better idea or method.
I created a mapping table then did wildcard joins. I'm sure performance will take a hit.:pinch:
CREATE TABLE #Pharmacy (RXID INT IDENTITY(1,1), INSTRUCTIONS VARCHAR (100), NeededResult INT, Explanation VARCHAR(200))
INSERT #Pharmacy VALUES ('1 PO BID', 2, ' 1 is the unit, BID is twice daily so 1 * 2 =2')
,('1 PO TID', 3, ' 1 is the unit, TID is three times daily so 1 * 3 =3')
, ('1 PO QD', 1, ' 1 is the unit, QD is once daily so 1 * 1 =1')
, ('1-2 PO QD', 2, ' 2 is the unit because it is max value, QD is once daily so 2 * 1 =2')
, ('1-2 PO BID', 4, ' 2 is the unit because it is max value, BID is twice daily so 2 * 2 =4')
, ('One to two tabs PO BID', 4, ' 2 is the unit because it is max value, BID is twice daily so 2 * 2 =4')
,('one tab PO TID', 3, ' 1 is the unit, TID is three times daily so 1 * 3 =3')
,('Take one tablet twice a day', 2, ' 1 is the unit, twice daily so 1 * 2 =2')
,('Take one tablet every 4-6 hours', 6, ' 1 is the unit, every 4-6 hours max is 6 so 1 * 6 =6')
;
CREATE TABLE #CODE (Code VARCHAR(10), Result INT, Part VARCHAR(10))
INSERT #CODE
VALUES ('BID',2,'Freq')
,('TID',3,'Freq')
,('QD',1,'Freq')
,('QHS',1,'Freq')
,('One to two', 2,'Unit')
,('1-2',2,'Unit')
,('one',1,'Unit')
,('One to two',2,'Unit')
,('every 4-6',6,'Freq')
,('1',1,'Unit')
,('twice',2,'Freq')
SELECT p.RXID,p.INSTRUCTIONS, p.NeededResult, p.Explanation, CalculatedResult = MAX(freq.result * unit.Result)
FROM #Pharmacyp
INNER JOIN #CODE FREQ ON p.INSTRUCTIONS LIKE '%' + freq.code + '%'AND freq.Part ='Freq'
INNER JOIN #CODE UNIT ON p.INSTRUCTIONS LIKE '%' + unit.code + '%'AND unit.Part ='Unit'
GROUP BY RXID,p.INSTRUCTIONS, p.NeededResult, p.Explanation
DROP TABLE #Pharmacy
DROP TABLE #CODE
***SQL born on date Spring 2013:-)
November 13, 2015 at 2:40 pm
You're probably going to want to make a permanent table out of this, if performance doesn't suffer so badly that you have a problem on that front. And then, given your INNER JOIN construction, you'll discover you might miss some records as newer instructions make their way into the system, but you'll have no NULL values to clue you in to the fact that it's taking place. You could either pre-check the values, or just allow NULLS, and run a nightly batch to identify rows that would not appear, which means changing those INNER JOINs to LEFT OUTER JOINs.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 13, 2015 at 2:59 pm
Yes, good insight. I ended up creating a Permanent table with many, many more mappings in it. I also switched to LEFT JOIN so I will catch things missed in the mappings. Performance is not as bad as I thought it would be. I ran 700,000 rows in a copy of a production db and it came back in 23 seconds. So I can't complain too much since at most I will only be using it on a few thousand rows at a time. 😎
Interestingly I tried two ways. One with the wildcard join and CHARINDEX join. I thought the CHARINDEX would be faster but it actually took twice as long.
***SQL born on date Spring 2013:-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply