Multiple condition calculated column

I’m trying to figure out what’s the best approach for this:

If C="WORD":

     A = 51 if B is between 0 and 2000
     A = 102 if B is between 2001 and 8000
     A = 204 if B is higher than 8000
     AND if D is ticked (checkbox) the A value turns into half`

I need this column to be “persisted”, so that I can use it on reports.

For another column, I’ve used this and works:

ALTER TABLE dbo.mytable 
    ADD C AS (A + B) / CASE D WHEN 1 THEN 4 ELSE 2 END;

Started with the this other code but it’s not working and it still doesn’t have the condition for the checkbox.

ALTER TABLE dbo.tablename
    ADD FieldA AS (CASE 
                      WHEN FieldC = "Word"  
                         THEN
                            CASE 
                               WHEN FieldB > 0 AND FieldB < =2000 
                                  THEN 102
                               WHEN FieldB > 2000 AND FieldB <= 8000 
                                  THEN 204
                               WHEN FieldB > 8000 
                                  THEN 306
                               ELSE NULL         
                            END,

How can I proceed?

Answer

In the short term, Tab is right about your typo.

In the bigger picture, you can save yourself some CPU time by taking advantage of the fact that CASE conditions are evaluated in order, so if you go backwards you only need to evaluate the last parameter. Also, by using the POWER trick, you’ll dodge the division by 0 error without having to introduce another CASE expression.

Alternative included if FieldD is a bit.

The values for FieldA vary between your problem statement and your code. I used the problem statement. Alter if needed.

ALTER TABLE dbo.tablename
ADD FieldA AS 
  (
    CASE 
      WHEN FieldC = "Word" THEN
        CASE 
          WHEN FieldB > 8000 
            THEN 204
          WHEN FieldB > 2000
            THEN 102
          WHEN FieldB >= 0 
            THEN 51
          ELSE NULL
        END
    END
    / POWER(2,FieldD) --<--- Or, possibly, POWER(2,CAST(FieldD AS tinyint))
   )