Conversion from MS Access SQL Code...nested if statements

INSERT INTO EligSummary ( PlanVariation)
SELECT DISTINCT

IIf(Left([BPI],1)=2 Or Left([BPI],1)=3,

'1. MED EE',

'2. MED DEP' AS PlanVariation)

The above is the code i would use in access to Assign either the value

'MED EE' or 'MED DEP' to the PlanVariation field.

I am new to SQL Server - how would I accomplish this in SQL Server 2000?

If I use SQL

INSERT INTO EligSummary (PlanVariation)
SELECT Planvariation =

CASE left([BPI],1)
WHEN 1 THEN 'EE NON MED'
WHEN 2 THEN 'MED DEP'

..it requires me to GROUP by on BPI which would cause it to enter 15 different rows for each BPI code as opposed to 2 for EE NON MED and MED DEP......

I would appreciate any help you could give me with this!

CASE has a second syntax, which will give you what you want

INSERT INTO EligSummary ( PlanVariation)
SELECT DISTINCT
CASE WHEN (left([BPI], 1) = '2' OR left([BPI], 1) = '3' THEN '1. MED EE'
ELSE '2. MED DEP' END AS PlanVariation

|||

Thanks! That did the trick..i appreciate the help.