Case statement in views - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Case statement in views

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-10 : 09:44:06

thomas writes "I can create a SUM(CASE WHEN....) statement in SQL but when I copy this SQL to make a view it won't take. Is there another alternative?

SQL is:
SELECT [Month], [Year], EpicDeptID, DocID,
CAST(CAST([Month] AS varchar(2)) + '/01/' + CAST([Year] AS varchar(4)) AS datetime) AS Survey_date,
SUM(CASE WHEN RTCARE = '1' THEN 1 WHEN RTCARE = '2' THEN 1 WHEN RTCARE = '3' THEN
1 WHEN RTCARE = '4' THEN 1 WHEN RTCARE = '5' THEN 1 WHEN RTCARE = '6' THEN 1 WHEN
RTCARE = '7' THEN 1 WHEN RTCARE = '8' THEN 1 WHEN RTCARE = '9' THEN 1 WHEN RTCARE
= '10' THEN 1 else 0 END)as Overall_Total_Responses,
SUM(CASE WHEN RTCARE = '8' THEN 1 WHEN RTCARE = '9' THEN 1 WHEN RTCARE
= '10' THEN 1 else 0 END)as Overall_Excellent_resp,
SUM(CASE WHEN RTCARE = '7' THEN 1 WHEN RTCARE = '6' THEN 1 WHEN RTCARE
= '5' THEN 1 else 0 END)as Overall_Good_resp,
SUM(CASE WHEN RTCARE = '4' THEN 1 WHEN RTCARE = '3' THEN 1 WHEN RTCARE
= '2' THEN 1 WHEN RTCARE = '1' THEN 1 else 0 END)as Overall_Poor_resp
FROM dbo.DMS_QDM
group by [Year],[Month],epicdeptid,docid

Any suggestions?

Tom Nather"

Q
Yak Posting Veteran

76 Posts

Posted - 2006-08-10 : 10:22:26

What error do you get?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-10 : 10:22:57

The SQL seems fine...but try this..

SELECT [Month], [Year], EpicDeptID, DocID,
CAST(CAST([Month] AS varchar(2)) + '/01/' + CAST([Year] AS varchar(4)) AS datetime) AS Survey_date, 
SUM(CASE WHEN RTCARE in ('1', '2', '3', '4', '5', '6','7','8','9', '10') THEN 1 else 0 END)as Overall_Total_Responses,
SUM(CASE WHEN RTCARE in ('8', '9','10') THEN 1 else 0 END)as Overall_Excellent_resp,
SUM(CASE WHEN RTCARE in ('7','6','5') THEN 1 else 0 END)as Overall_Good_resp,
SUM(CASE WHEN RTCARE in ('4','3','2','1') THEN 1 else 0 END)as Overall_Poor_resp
FROM dbo.DMS_QDM
group by [Year],[Month],epicdeptid,docid

BTW, what do you mean by "it won't take"? Is there any error returned?
What error?

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources