?
Solved

Convert field value in select statement

Posted on 2012-03-15
5
Medium Priority
?
193 Views
Last Modified: 2012-12-02
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
0
Comment
Question by:markloessi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 2000 total points
ID: 37725774
select dose
, case dose when 1 then '40 MG = 1 ECTAB'
    ELSE dose
  end as changed_dose
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 37726150
what is your current query? we may suggest something better depending on your current solution...
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 37726154
what about updating your table

update mytable set dose='40 MG = 1 ECTAB' where dose=1

then your query should be fine...
0
 
LVL 32

Expert Comment

by:awking00
ID: 37730344
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.
0
 

Author Comment

by:markloessi
ID: 37735775
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

Open in new window

Trans qty is not based on dose per se, at least it is not calculated from the value present here as dose, which is really a human readable form of dose. The TRANS_QTY is a billing field calcualated based on fields in the drug record which align with another table used to calculate a charge, so although dose and trans qty are related there are intermediary formulas that form the connection.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question