asked on Convert field value in select statement
I have data which looks like this, I'm trying to get accurate counts (TRANS_QTY_SUM) based on dose and unfortunately some of the doses are stored as '1' (complicated reason for this). In reality the items which are '1' are really the same as the items with a dose of '40 MG = 1 ECTAB'. Is there a way in the select statetment to convert the 1 to '40 MG = 1 ECTAB' or is there something more complex I'll need to do before executing this query to convert those doses. If so, then how do I do that, aka the if this store this.
![Convert Dose in Select statement]()
Microsoft SQL Server 2008SQL
Last Comment
markloessi
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
what is your current query? we may suggest something better depending on your current solution...
what about updating your table
update mytable set dose='40 MG = 1 ECTAB' where dose=1
then your query should be fine...
update mytable set dose='40 MG = 1 ECTAB' where dose=1
then your query should be fine...
How is trans_qty_sum based on dose, given that dose is obviously a character field? There must be some sort of manipulation to the dose values to accomplish that and, perhaps, that's where you might deal with the '1' value instead of the '40 MG = 1 ECTAB' value.
ASKER
I ended up using the case when idea with a little twist in that i needed to accommodate a number of scenario's beside the 1 ECTAB example. I did this by reconstructing the 'dose' from the fields in another table where needed.
SELECT CASE WHEN C.DOSE = '1' THEN P.STRENGTH + ' ' + P.STRENGTH_UNIT + ' = ' + + P.PACKAGE_SIZE + ' ' + P.PACKAGE_UNIT
ELSE DOSE END AS CHANGED_DOSE,
P.GENERIC_NAME, P.BRAND_NAME, P.THER_CODE_1,
(P.STRENGTH + P.STRENGTH_UNIT + ', ' + P.VOLUME + P.VOLUME_UNIT + ', ' + P.PACKAGE_SIZE + ' ' + P.PACKAGE_UNIT) AS 'CONC - PACK INFO',
C.*
FROM CURES C
JOIN PDM P
ON C.DRUG_CODE = P.DRUG_CODE
WHERE P.THER_CODE_1 = '56:28.36'
AND C.TRANS_DATE >= GETDATE()-(180)
ORDER BY C.TRANS_DATE