select dj_name, show.show_id, show_name,
case day
when 'sun' then 0
when 'mon' then 1
when 'tue' then 2
when 'wed' then 3
when 'thu' then 4
when 'fri' then 5
when 'sat' then 6
end as day
, to_char(start_time,'hh:mi AM -') || to_char(end_time,'hh:mi AM') as nicetime,
decode(genre,'Public Affairs', 'public', 'music') as type,
to_number(to_char(start_time,'hh24') ||
(case
when to_char(start_time,'mi') = '30' then '.5'
end)) as matchtime,
(to_number(decode(to_char(end_time,'hh24'),'00','24',to_char(end_time,'hh24')))-
to_number(to_char(start_time,'hh24'))
+
case
when to_char(start_time,'mi') = '30' and to_char(end_time,'mi') = '00' then -1
else 0
end
- case when to_char(end_time,'hh24') = '00' and to_char(end_time,'mi') = '30' then 24 else 0 end)
*2 +
(case
when to_char(start_time,'mi') = '30' and to_char(end_time,'mi') = '00' then 1
when to_char(start_time,'mi') = '00' and to_char(end_time,'mi') = '30' then 1
else 0
end)
as rowspan,
replace(show_name,'''','\''') as escaped_title
from schedule, show where schedule.show_id = show.show_id
order by matchtime, day, archive