Help with CASE Statement - 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 2005 Forums

Transact-SQL (2005)

Help with CASE Statement

Author

Topic

barryNHS
Starting Member

3 Posts

Posted - 2010-09-29 : 05:08:13

I am having problems with a CASE statement I am writing as part of a function:

CREATE FUNCTION [dbo].[GetRTC_EEPcompliance](@SubmissionID int) RETURNS float

AS

BEGIN

DECLARE @Result float
DECLARE @ActualEEPscore float
DECLARE @PossibleEEPscore float

DECLARE @numSAB float
DECLARE @numCDiff float
DECLARE @numHAIaudits float

-- Check the number of SAB infections
SELECT @numSAB = SABinfections FROM RTC_Submissions WHERE RTC_Submissions.RTCSubmissionID = @SubmissionID
IF @numSAB = 0
BEGIN
SELECT @ActualEEPscore = @ActualEEPscore + 3
END
SELECT @PossibleEEPscore = @PossibleEEPscore + 3

-- Check the number of C-Diff infections
SELECT @numCDiff = CDiffInfections FROM RTC_Submissions WHERE RTC_Submissions.RTCSubmissionID = @SubmissionID
IF @numCDiff = 0
BEGIN
SELECT @ActualEEPscore = @ActualEEPscore + 3
END
SELECT @PossibleEEPscore = @PossibleEEPscore + 3

-- Check the number of Environmental Infection Control Audits
SELECT @numHAIaudits = HAImonthlyAudits FROM RTC_Submissions WHERE RTC_Submissions.RTCSubmissionID = @SubmissionID
IF @numHAIaudits IS NOT NULL
BEGIN
SELECT @ActualEEPscore = CASE @numHAIaudits
WHEN @numHAIaudits > 95 THEN @ActualEEPscore + 4
WHEN @numHAIaudits = 95 THEN @ActualEEPscore + 3
WHEN @numHAIaudits < 95 AND @numHAIaudits >= 90 THEN @ActualEEPscore + 2
ELSE @ActualEEPscore + 1
END
SELECT @PossibleEEPscore = @PossibleEEPscore + 4
END

RETURN @Result

END

I am getting a syntax error on the operator symbols in my CASE statement. Can anyone help?

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 05:15:30

Change your case statement to this.


SELECT @ActualEEPscore =CASE 
WHEN @numHAIaudits > 95 THEN @ActualEEPscore + 4
WHEN @numHAIaudits = 95 THEN @ActualEEPscore + 3
WHEN @numHAIaudits < 95 AND @numHAIaudits >= 90 THEN @ActualEEPscore + 2
ELSE @ActualEEPscore + 1 end

PBUH

barryNHS
Starting Member

3 Posts

Posted - 2010-09-29 : 05:18:16

That's sorted it nicely.

Many thanks for such a fast response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources