How to write this query more concisely

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to write this query more concisely

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-29 : 10:18:13

Lee writes "How do I make this query more concise?

/*Description: Generate % responded Yes for each one of the
survey questions(column names)

Goal: I will display the real time result of this query on a website using asp.net */

CREATE TABLE chartcalc
(
Dept CHAR(15),
Aller_yes DECIMAL(5,2),
Aller_notsure DECIMAL(5,2),
Aller_deno DECIMAL(5,2),
Adv_yes DECIMAL(5,2),
Adv_notsure DECIMAL(5,2),
Adv_deno DECIMAL(5,2),
HP_yes DECIMAL(5,2),
HP_no DECIMAL(5,2),
HP_na DECIMAL(5,2),
HP_deno DECIMAL(5,2),
Imm_all DECIMAL(5,2),
Imm_part DECIMAL(5,2),
Imm_none DECIMAL(5,2),
Imm_na DECIMAL(5,2),
Imm_deno DECIMAL(5,2),
Vit_all DECIMAL(5,2),
Vit_part DECIMAL(5,2),
Vit_none DECIMAL(5,2),
Vit_na DECIMAL(5,2),
Vit_deno DECIMAL(5,2),
Dia_yes DECIMAL(5,2),
Dia_no DECIMAL(5,2),
Dia_na DECIMAL(5,2),
Dia_deno DECIMAL(5,2),
);

INSERT INTO chartcalc (Dept, Aller_yes, Aller_deno, Adv_yes, Adv_deno, HP_yes, HP_deno)
SELECT Dept,
COUNT(CASE WHEN Aller='Yes' THEN 1.0 ELSE null END)"AllerYes",
COUNT(CASE WHEN Aller='Yes' or Aller='Not Sure' THEN 1.0 ELSE null End)"AllerDenominator",
COUNT(CASE WHEN Adv_Rea='Yes' THEN 1.0 ELSE null END)"AdvYes",
COUNT(CASE WHEN Adv_Rea='Yes' or Adv_Rea='Not Sure' THEN 1.0 ELSE null END)"AdvDenominator",
COUNT(CASE WHEN HP='Yes' THEN 1.0 ELSE null END)"AdvYes",
COUNT(CASE WHEN HP='Yes' or HP='No' THEN 1.0 ELSE null END)"HPDenominator"
FROM chart_audit
GROUP BY Dept

CREATE TABLE chartaudit_result
(
Dept CHAR(15),
Aller_p_yes DECIMAL(5,2),
Adv_p_yes DECIMAL(5,2),
HP_p_yes DECIMAL(5,2),
);

INSERT INTO chartaudit_result
SELECT Dept,Avg(Aller_yes/Aller_deno)*100,Avg(Adv_yes/Adv_deno)*100, Avg(HP_yes/HP_deno)*100
FROM chartcalc
GROUP BY Dept"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-29 : 17:52:11

You need to post your table and test data for the chart_audit too.

SQLCM
Starting Member

13 Posts

Posted - 2007-02-01 : 13:39:50

snSQL: Here is the test data, if this is too messy, can I email it to you?

rowid Pt_Fir Pt_Last Chart_Num DOS Phys_NameDr_Num Dept Aller Adv_Rea HP
4 john zzjohn 34567 1/15/2007 40261 NULL General Surgery Yes Yes Yes
9 phil zzphil 45687 1/17/2007 41471 NULL Allergy Not sure Yes Yes
11 jason zzjason 5678 1/16/2007 41471 NULL Endocrinology Not sure Yes Yes
14 steve zzsteve 34567 1/25/2007 41471 NULL General Surgery Not sure Yes Yes
17 lydia zzlydia 52356 1/23/2007 41471 NULL IM West Valley Yes Yes Yes
18 robert zzrobert 75865 1/23/2007 41471 NULL IM West Valley Yes Yes Yes
19 vivian zzvivian 4523 1/23/2007 41471 NULL OBGYN Mt View Yes Yes Yes
20 vivien zzvivien 45236 1/15/2007 41471 NULL OBGYN Mt View Not sure Yes Yes
21 steve zzsteve 3546 1/17/2007 41471 NULL Allergy Yes Yes Yes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources