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','PTO_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_name,'PTO','PTO_Holiday') 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_name,'Holiday','PTO_Holiday')and Sum(td_hours) AS SumOfHours (this will give me an error)
ELSE task_name
Thank youYour "Sum(td_hours) as SumOfHours" must be moved out of the CASE statement; something like this maybe:
Code Snippet
select case
when SUBSTRING(task_name, 1, 3)= 'PTO'
then Replace(task_name,'PTO','PTO_Holiday')
when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'
then Replace(task_name,'Holiday','PTO_Holiday')
ELSE task_name
end,
Sum(td_hours) AS SumOfHours
No comments:
Post a Comment