|
show details
#shows.djname#
#shows.nicetime#
#shows.title##shows.genre#
#shows.description#
select dj_name, show.show_id, description, show_name, start_time, end_time, day as dow,
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 = #URL.show# AND show.show_id = #URL.show#
order by matchtime, day, archive
DJ site
#shows.web_address#
email
#shows.day#
Miss this Show?
MP3 Stream (128kbps, broadband)
Subscribe to Podcast (Help)
|
select * from
(select dj_name, show_name, show.show_id,
to_char(show_date,'Day MM/DD/YY') as clean_show_date, show_date, start_time,
to_char(schedule.start_time,'HH:Mi AM - ') || to_char(schedule.end_time,'HH:Mi AM') as nice_time,
substr(replace(comments,'ยด',''''),1,300) comments, length(comments) comment_length, DENSE_RANK() OVER
(ORDER BY show_date ASC) AS date_Rank, replace(show_name,'''','\''') as escaped_title, show_type.name show_type
from kdvs_library.show_occur, show, schedule, show_type
where show_occur.show_id = show.show_id
and show.show_id = schedule.show_id
and show_occur.show_type = show_type.show_type_id(+)
and comments is not null
and to_date(to_char(show_date,'MM/DD/YYYY')||' '||to_char(start_time,'HH24:MI'),'MM/DD/YYYY HH24:MI') > sysdate
and show.show_id = #URL.show#
order by show_date asc, start_time asc)
where rownum < 2
order by show_date asc, start_time asc
upcoming show on #show.clean_show_date#
select * from (
SELECT dj_name, show_name, show_occur.comments, show.show_id, playlist_id, currfromhome from_home, playlist.comments playlist_comments,
to_char(show_date,'Day MM/DD/YY') as clean_show_date,
to_char(schedule.start_time,'HH:Mi AM - ') || to_char(schedule.end_time,'HH:Mi AM') as nice_time,
nvl(playlist.label,label.label) label,
playlist.nowplaying,
playlist.request,
playlist.currfromhome,
playlist.curr,
decode(playlist.track,0,null,playlist.track) as track,
playlist.artist,
airbreak,
listorder,
kdvs_library.is_current(music_library.track, genre.months_in_currents, music_library.add_date, show_occur.show_date) is_current,
decode(playlist.song,'Song Not Specified',album_songs.song,playlist.song) as song,
decode(to_char(playlist.duration, 'mi:ss'),'00:00',null) as duration,
decode(playlist.album,'NA',null,playlist.album) as album, replace(show_name,'''','\''') as escaped_title,
playlist.library_id, DENSE_RANK() OVER
(ORDER BY show_date desc) AS date_rank
FROM kdvs_library.playlist, kdvs_library.music_library, kdvs_library.label,
kdvs_library.album_songs, kdvs_library.genre_block,
kdvs_library.genre, kdvs_library.show_occur, show, schedule
WHERE playlist.library_id = music_library.id(+)
and playlist.library_id = album_songs.album_id(+)
and playlist.track = album_songs.id(+)
and music_library.genre_block = genre_block.id(+)
and genre_block.genre_id = genre.id(+)
and music_library.label_id = label.id(+)
and playlist.show_occur_id = show_occur.show_occur_id
and show_occur.show_id = show.show_id
and show.show_id = schedule.show_id
and show.show_id = '#URL.show#'
and show_occur.show_occur_id = '#URL.show_occur_id#'
order by listorder, playlist_id
)
where date_rank = 1
order by nvl(listorder,'99999'), playlist_id
|