Select ( CASE ) question - 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)

Select ( CASE ) question

Author

Topic

hitmanharkness
Starting Member

1 Post

Posted - 2004-09-28 : 18:54:49

I've got a question about a CASE statement nested in an SELECT statement.
Below is the whole query. When A.Year1 or another year is null the total_diff returns null.

SQL STATEMENT:
SELECT A.*,
(A.Year1 - A.Year2) As Diff,
(A.Year1 - A.Year2) + (A.Year2- A.Year3) + (A.Year3 - A.Year4)
As Total_Diff
FROM
(

SELECT QR.Question,

(SELECT CONVERT(decimal(10,2), AVG(AnswerValue) ) AS AVG
FROM Answer AN, Survey SV, Question Q, School SC
WHERE AN.SurveyID = SV.SurveyID
AND AN.RespondentType = @RespondentType
AND Q.QuestionID = AN.QuestionID
AND Q.QuestionNO = QR.QuestionNO
AND SV.SchoolID = SC.SchoolID
AND SC.State = @State
AND (CAST(DATEPART(yyyy,SV.SurveyDate) AS Varchar)) = (CAST(DATEPART(yyyy,GETDATE())-3 AS Varchar))
) AS Year4,

(
SELECT CONVERT(decimal(10,2), AVG(AnswerValue) ) AS AVG
FROM Answer AN, Survey SV, Question Q, School SC
WHERE AN.SurveyID = SV.SurveyID
AND AN.RespondentType = @RespondentType
AND Q.QuestionID = AN.QuestionID
AND Q.QuestionNO = QR.QuestionNO
AND SV.SchoolID = SC.SchoolID
AND SC.State = @State
AND (CAST(DATEPART(yyyy,SV.SurveyDate) AS Varchar)) = (CAST(DATEPART(yyyy,GETDATE())-2 AS Varchar))
) AS Year3,

(
SELECT CONVERT(decimal(10,2), AVG(AnswerValue) ) AS AVG
FROM Answer AN, Survey SV, Question Q, School SC
WHERE AN.SurveyID = SV.SurveyID
AND AN.RespondentType = @RespondentType
AND Q.QuestionID = AN.QuestionID
AND Q.QuestionNO = QR.QuestionNO
AND SV.SchoolID = SC.SchoolID
AND SC.State = @State
AND (CAST(DATEPART(yyyy,SV.SurveyDate) AS Varchar)) = (CAST(DATEPART(yyyy,GETDATE())-1 AS Varchar))
) AS Year2,

(
SELECT CONVERT(decimal(10,2), AVG(AnswerValue) ) AS AVG
FROM Answer AN, Survey SV, Question Q, School SC
WHERE AN.SurveyID = SV.SurveyID
AND AN.RespondentType = @RespondentType
AND Q.QuestionID = AN.QuestionID
AND Q.QuestionNO = QR.QuestionNO
AND SV.SchoolID = SC.SchoolID
AND SC.State = @State
AND (CAST(DATEPART(yyyy,SV.SurveyDate) AS Varchar)) = (CAST(DATEPART(yyyy,GETDATE()) AS Varchar))
) AS Year1

FROM Question Q, QuestionRef QR
WHERE Q.QuestionNO = QR.QuestionNO AND TARGET = @RespondentType
) A
GO

how can I do this for the total_diff?

CASE WHEN
( A.Year1 = null
AND A.Year2 = null
AND A.Year3 = null
AND A.YEAR4 != null
)
THEN 0
ELSE WHEN
( A.Year1 = null
AND A.Year2 = null
AND A.Year3 != null
AND A.Year4 != null
)
THEN (A.Year3 - A.Year4)
ELSE WHEN
( A.Year1 = null
AND A.Year2 != null
AND A.Year3 != null
AND A.Year4 != null
)
THEN (A.Year2 - Year3) + (A.Year3 - A.Year4)
ELSE WHEN
( A.Year1 = !null
AND A.Year2 != null
AND A.Year3 != null
AND A.Year4 != null
)
THEN (A.Year1 - Year2) + (A.Year2 - Year3) + (A.Year3 - A.Year4)
ELSE 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-28 : 18:57:46

You need to use IS NULL and IS NOT NULL instead of = and !=.

A.Year1 IS NULL AND...

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources