update num_of column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update num_of column

Author

Topic

brucezepplin
Starting Member

15 Posts

Posted - 2011-09-19 : 05:13:21

Hi guys, I'll attempt to explain the thread a title a bit better. I am trying to identify the effects of using certain prescribed drugs when taken together. So I have 5 different drugs, let's say drug 1,2...etc and I need a way to group together all people who have only taken 1 (random) drug, all people who have taken 2 (random) drugs etc etc up to 5 (random) drugs. So explicit combinations of certain drugs do not matter just yet. I have a database for all patients, and I have separate tables for all people using a certain drug that are in the general database, so I have something like:


INSERT INTO DRUGDB (PERSON, DRUG1, DRUG2....)
SELECT DISTINCT A.PERSON FROM

(SELECT DISTINCT PERSON FROM PBASE) A

LEFT JOIN

(SELECT DISTINCT PERSON FROM DRUG1) B
ON A.PERSON = B.PERSON

LEFT JOIN 

SELECT DISTINCT PERSON FROM DRUG2) C
ON A.PERSON = C.PERSON
.
.
.
.
.
.
--and so on

My desired output is something like:

Person------------Drug1----------------Drug2-----------num_drugs
1-----------------YES------------------YES-------------2--------
2-----------------NO-------------------YES-------------1--------
3-----------------NO--------------------NO-------------0--------

Anyone know how I can get that num_drugs column right using an update statement? or even if there is another another solution without using an update statement would be good. thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 05:33:18

[code]INSERT INTO DRUGDB (PERSON, DRUG1, DRUG2....)
SELECT PERSON,DRUG1, DRUG2,
SUM(CASE WHEN Drug1='YES' THEN 1 ELSE 0 END)+SUM(CASE WHEN Drug2='YES' THEN 1 ELSE 0 END) AS num_drugs
FROM DRUGDB [/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

brucezepplin
Starting Member

15 Posts

Posted - 2011-09-19 : 05:44:30

this worked fantastically! thanks very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 05:45:47

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-23 : 06:34:02

How about this?

INSERT INTO DRUGDB (PERSON, DRUG1, DRUG2....)
SELECT PERSON,DRUG1, DRUG2,
SUM(CASE WHEN (Drug2,Drug1) in 'YES' THEN 1 ELSE 0 END) AS num_drugs
FROM DRUGDB

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources