SQL in MS Query with Case Statements (Oracle SQL Server)

Hi can anyone check my SQL to see if its ok.

I had trouble gettin it to work with MS Query, and I have been forced to remove my parameters (apparently you can't have variable parameters with queries that cannot displayed in the design views... e.g. Case statements)

At the moment I do get data back, however the query pulls back all data as though they were the ELSE case...  '03MED' when I have checked and there is a fairly even distribution between all cases.

Any help would be greatly appreciated.

SQL:

SELECT datename(month,disch_dttm)+'-'+datename(year,disch_dttm) AS 'Month',      

'Division' = case
when 'left(main_ident,3)' in('100','104','110','120','130','143','190')  then '01SURG'  
when 'left(main_ident,3)' in('142','170','171','180','420','421','501','502')  
then '02W&C'    
when 'left(main_ident,3)' in('101')
then '04NONCONTRACT'
else '03MED'
end,  
Count(*) AS ID  

FROM LOR_IPM.dbo.PROF_CARER_EPISODES PROF_CARER_EPISODES, LOR_IPM.dbo.PROVIDER_SPELLS PROVIDER_SPELLS, LOR_IPM.dbo.SPECIALTIES SPECIALTIES  WHERE PROVIDER_SPELLS.PRVSP_REFNO = PROF_CARER_EPISODES.PRVSP_REFNO AND SPECIALTIES.SPECT_REFNO = PROF_CARER_EPISODES.SPECT_REFNO AND SPECIALTIES.SPECT_REFNO = PROVIDER_SPELLS.SPECT_REFNO AND ((PROVIDER_SPELLS.ARCHV_FLAG='n') AND (PROVIDER_SPELLS.PRVSN_END_FLAG='n') AND (PROVIDER_SPELLS.DISCH_DTTM>={ts '2008-04-01 00:00:00'} And PROVIDER_SPELLS.DISCH_DTTM<={ts '2008-08-31 00:00:00'}) AND (PROF_CARER_EPISODES.ARCHV_FLAG='n'))  

GROUP BY datename(month,disch_dttm)+'-'+datename(year,disch_dttm)

ORDER BY datename(month,disch_dttm)+'-'+datename(year,disch_dttm)

Results:

Month                      Division         ID
April-2008      03MED      4782
August-2008      03MED      4153
July-2008                      03MED      4746
June-2008      03MED      4440
May-2008                      03MED      4560

ThankyouStart Free Trial