Case Statement Problem

**azimmer** · Sep 24 '07, 06:57 PM

Originally posted by goal2007

I am trying to convert this to sql syntax

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PT O_Holiday',

IIf(Mid([task_name],1,7)='Holiday' ,'PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

Here is what i am trying to do:

select case

when SUBSTRING(task_ name, 1, 3)= 'PTO'

then Replace(task_na me,'PTO','PTO_H oliday') and Sum(td_hours) AS SumOfHours (this will give me an error)

when SUBSTRING(task_ name, 1, 7)= 'Holiday' and SUBSTRING(task_ name, 1, 3)= 'PTO'

then Replace(task_na me,'Holiday','P TO_Holiday')and Sum(td_hours) AS SumOfHours (this will give me an error)

ELSE task_name

Thank you

I suggest you do it along the lines of the following example:

Code:

SELECT
   CASE
      WHEN SUBSTRING(task_name, 1, 3)= 'PTO' THEN 'PTO_Holiday'
      WHEN SUBSTRING(task_name, 1, 7)= 'Holiday' THEN 'PTO_Holiday'
      ELSE task_name
   END as timeType,
   Sum(tdHours) AS SumOfHours
FROM ...

Case Statement Problem

Case Statement Problem

Comment