actsoft

asked on

# need sql CASE statement to replace excel formula

I have a table with field names "0616" "0716" "0816" etc for 24 months. These fields contain numbers.

I also have a field that gets todays date and translates it to "0616" or "0716" etc.

I need a CASE statement to replace this excel formula.

=IF(TDMMYY="0816",(N2+O2),IF(TDMMYY="0916",(N2+O2+P2),IF(TDMMYY="1016",(N2+O2+P2+Q2))))

I think it would start

CASE

WHEN 'TDMMYY' = '0816' THEN ...

what would the expression be to add the numbers from the fields named "0716" + "0816" + "0916"

please see attached sheet for screen shots that may make this clearer.

As always, Thanks you for your assistance.

case.docx

I also have a field that gets todays date and translates it to "0616" or "0716" etc.

I need a CASE statement to replace this excel formula.

=IF(TDMMYY="0816",(N2+O2),

I think it would start

CASE

WHEN 'TDMMYY' = '0816' THEN ...

what would the expression be to add the numbers from the fields named "0716" + "0816" + "0916"

please see attached sheet for screen shots that may make this clearer.

As always, Thanks you for your assistance.

case.docx

= CASE(ddmmyy) WHEN '0816' THEN n2+o2 WHEN '0916' THEN N2+O2+P2 WHEN '1016' THEN N2+O2+P2+Q2 ELSE 0 END

ASKER

in the table the field names are not "n2" and "o2" they are "0716" and "0816".

so will

WHEN TTMMYY = '0816' THEN 0716+0816

add the amount in those fields together to get the total?

so will

WHEN TTMMYY = '0816' THEN 0716+0816

add the amount in those fields together to get the total?

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

CASE TDMMYY

WHEN '0816' THEN [0716] + [0816]

WHEN '0916' THEN [0716] + [0816] + [0916]

WHEN '1016' THEN ...

END AS TYSTD

WHEN '0816' THEN [0716] + [0816]

WHEN '0916' THEN [0716] + [0816] + [0916]

WHEN '1016' THEN ...

END AS TYSTD

ASKER

That worked with the addition of the full table and field name. thanks