Case Statement Question

Hi All,

Can anybody tell why the underlined when statement doesn't return any results? It should return something like this: Sun - Sat 10:30PM

select substring(sjt.name,1,charindex(':',sjt.name)-1),
case js.freq_type when '8' then
case js.freq_interval
when '1' then 'Sun'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
when '64' then 'Sat'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7) end
when '4' then
case js.freq_subday_type when '4' then 'Sun - Sat'+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)+'-'+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_end_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)+' '+'every'+' '+cast(js.freq_subday_interval as varchar(2))+' '+'min'
when '4' then 'Sun - Sat'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7) end end
from
(select name, job_id, active_start_time, freq_type, freq_interval, freq_subday_type, freq_subday_interval, active_end_time
from sysjobschedules
where name like 'ALGL%' or name like 'WC%') as js
join
(select name, job_id
from sysjobs
where name like 'ALGL%' or name like 'WC%') as sjt
on sjt.job_id = js.job_id
order by sjt.name

This is the output of this query:
---------- ------------
ALGL-VCS Data Process Sun 10:30PM
ALGL-VCS Data Process Sun - Sat 6:30AM- 8:00PM every 5 min
ALGL-VCS Data Process NULL (underlined when stmt)
ALGL-VCS Data Process NULL (underlined when stmt)
ALGL-VCS Maintenance Process Sat 10:50PM
WC-VCS Data Process Sun 10:30PM
WC-VCS Data Process Sun - Sat 6:30AM- 8:00PM every 5 min
WC-VCS Data Process NULL (underlined when stmt)
WC-VCS Data Process NULL (underlined when stmt)
WC-VCS Maintenance Process Sat 10:50PM
WC-VCS Maintenance Process NULL (underlined when stmt)

Thanks.Is it just me, or does it appear that you have two WHEN '4' entries in your case statement?

Perhaps you aren't seeing the results of the second one because you are seeing the results of the first one?

Not trying to be a smartass, but unless I missed an END somewhere, seems like you have duplicate WHEN's in there.

BTW...my head hurts from reading that code...yikes!|||Perhaps formatting the statement a bit will point out what I think is an issue to deal with:select substring(sjt.name,1,charindex(':',sjt.name)-1),
case js.freq_type
when '8' then case js.freq_interval
when '1' then 'Sun'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
when '64' then 'Sat'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
end
when '4' then case js.freq_subday_type
when '4' then 'Sun - Sat'+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)+'-'+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_end_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)+' '+'every'+' '+cast(js.freq_subday_interval as varchar(2))+' '+'min'
when '4' then 'Sun - Sat'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
end
end
from
(select name, job_id, active_start_time, freq_type, freq_interval, freq_subday_type, freq_subday_interval, active_end_time
from sysjobschedules
where name like 'ALGL%' or name like 'WC%') as js
join
(select name, job_id
from sysjobs
where name like 'ALGL%' or name like 'WC%') as sjt
on sjt.job_id = js.job_id
order by sjt.name|||Instant SQL Formatter:
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl|||Thanks. I fixxed it. It works.