T-sql 2012 case statement problem

Transact-SQL
1

In a t-sql 2012, I have the following case statement:

CASE
WHEN (CalendarSchool.schoolLevelOPS IN ('All', 'Elem') AND ScheduleStructure.name <> 'High/Middle School'
AND LessonPlanActivity.[weight] IS NULL OR LessonPlanActivity.[weight] <> 1)
OR
(CalendarSchool.schoolLevelOPS IN ('All', 'High') AND ScheduleStructure.name <> 'Elementary'
AND (LessonPlanActivity.[weight] IS NULL OR (LessonPlanActivity.[weight] <> 1.000 AND LessonPlanActivity.[weight] <> 2.000 AND LessonPlanActivity.[weight] <> 3.000 AND LessonPlanActivity.[weight] <> 4.000)))
THEN 1 --multiplier
ELSE 0

The field called weight defined as (decimal(6,3),null)

Basically when the field called weight does not = the values of 1, 2, 3, or 4 Then the value from the case statement should get a result of 1.

The problem is every time a value of 2.000 is located it is selected while it should not be selected. Thus would you show me the sql on how to fix this problem?

2

Something like:

case
   when CalendarSchool.schoolLevelOP in ('All','Elem')
    and ((ScheduleStructure.name<>'High/Middle School'
    and   isnull(LessonPlanActivity.[weight],0)<>1
         )
     or  (ScheduleStructure.name<>'Elementary'
    and   isnull(LessonPlanActivity.[weight],0) not in (1,2,3,4)
        ))
   then 1
   else 0
end
1 Like
3

I think you just need extra parentheses around the NULL and value conditions:

CASE
WHEN (CalendarSchool.schoolLevelOPS IN ('All', 'Elem') AND ScheduleStructure.name <> 'High/Middle School'
AND (LessonPlanActivity.[weight] IS NULL OR LessonPlanActivity.[weight] <> 1 ))
OR
(CalendarSchool.schoolLevelOPS IN ('All', 'High') AND ScheduleStructure.name <> 'Elementary'
AND ((LessonPlanActivity.[weight] IS NULL OR (LessonPlanActivity.[weight] <> 1.000 AND LessonPlanActivity.[weight] <> 2.000 AND LessonPlanActivity.[weight] <> 3.000 AND LessonPlanActivity.[weight] <> 4.000))))
THEN 1 --multiplier
ELSE 0