[mythtv-users] Big Beautiful Scheduler Query

[Jay R. Ashworth]

As it happens, there *is* an online SQL pretty printer, at
www.sqlinform.com.

Herewith, then the BBSQ, courtesy of crs23 and the boyz:

SELECT DISTINCT channel.chanid                                               ,
        channel.sourceid                                                     ,
        program.starttime                                                    ,
        program.en dtime                                                     ,
        program.title                                                        ,
        program.subtitle                                                     ,
        program.description                                                  ,
        channel.channum                                                      ,
        channel.callsign                                                     ,
        channel.name                                                         ,
        oldrecorded.endtime IS NOT NULL AS oldrecduplicate                   ,
        program.category                                                     ,
        record.recpriority                                                   ,
        record.dupin                                                         ,
        recorded.endtime IS NOT NULL AS recduplicate                         ,
        oldfind.findid   IS NOT NULL AS findduplicate                        ,
        record.type                                                          ,
        record.recordid                                                      ,
        program.starttime - INTERVAL record.startoffset minute AS recstartts ,
        program.endtime   + INTERVAL record.endoffset minute   AS recendts   ,
        program.previouslyshown                                              ,
        record.recgroup                                                      ,
        record.dupmethod                                                     ,
        channel.commfree                                                     ,
        capturecard.cardid                                                   ,
        cardinput.cardinputid                                                ,
        UPPER(cardinput.shareable) = 'Y' AS shareable                        ,
        program.seriesid                                                     ,
        program.programid                                                    ,
        program.category_type                                                ,
        program.airdate                                                      ,
        program.stars                                                        ,
        program.originalairdate                                              ,
        record.inactive                                                      ,
        record.parentid                                                      ,
        (CASE record.type WHEN 6 THEN record.findid
                          WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute))
                          WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday
                          WHEN 7 THEN record. findid ELSE 0
        END)                                               ,
        record.playgroup                           ,
        oldrecstatus.recstatus                     ,
        oldrecstatus. reactivate                   ,
        channel.recpriority + cardinput.recpriority,
        record.prefinput                           ,
        program.hdtv                               ,
        program.closecaptioned                     ,
        program.first                              ,
        program.last                               ,
        program.stereo FROM recordmatch
INNER JOIN record
ON (recordmatch.recordid = record.recordid)
INNER JOIN program
ON (recordmatch.chanid        = program.chanid
    AND recordmatch.starttime = program.starttime
    AND recordmatch.manualid  = program.manualid)
INNER JOIN channel
ON (channel.chanid = program.chanid)
INNER JOIN cardinput
ON (channel.sourceid = cardinput.sourceid)
INNER JOIN capturecard
ON (capturecard.cardid = cardinput.cardid)
LEFT JOIN oldrecorded as oldrecstatus
ON ( oldrecstatus.station      = channel.callsign
    AND oldrecstatus.starttime = program.starttime
    AND oldrecstatus.title     = program.title )
LEFT JOIN oldrecorded
ON ( record.dupmethod          > 1
    AND oldrecorded.duplicate <> 0
    AND program.title          = oldrecorded.title
    AND ( (program.programid  <> ''
    AND program.generic        = 0
    AND progra m.programid     = oldrecorded.programid)
     OR (oldrecorded.findid   <> 0 AN D oldrecorded.findid =
        (
                CASE record.type
                        WHEN 6
                        THEN record.findid
                        WHEN 9
                        THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute))
                        WHEN 10
                        THEN floor((to _days(date_sub(program.starttime, interval time_format(record.fi ndtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record .findday
                        WHEN 7
                        THEN record.findid
                        ELSE 0
                END
        )
        )
     OR ( program.generic           = 0
    AND (program.programid          = ''
     OR oldrecorded. programid      = '')
    AND (((record.dupmethod & 0x02) = 0)
     OR (program. subtitle         <> ''
    AND program.subtitle            = oldrecorded.subtitle))
    AND (((record.dupmethod & 0x04) = 0)
     OR (program.description       <> ''
    AND program.description         = oldrecorded.description)) ) ) )
LEFT JOIN recorded
ON ( record.dupmethod         > 1
    AND recorded.duplicate   <> 0
    AND program.title         = recorded.title
    AND recorded.recgroup    <> 'LiveTV'
    AND ( (program.programid <> ''
    AND program.generic       = 0
    AND program.programid = recorded.programid)
     OR (recorded.findid     <> 0
    AND recorded.findid       =
        (
                CASE record.type
                        WHEN 6
                        THEN record.findid
                        WHEN 9
                        THEN to_days(date_sub(program.starttime, interval t ime_format(record.findtime, '%H:%i') hour_minute))
                        WHEN 10
                        THEN floor((to_ days(date_sub(program.starttime, interval time_format(record.fin dtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record. findday
                        WHEN 7
                        THEN record.findid
                        ELSE 0
                END
        )
        )
     OR ( program.generic           = 0
    AND (program.programid          = ''
     OR recorded.prog ramid         = '')
    AND (((record.dupmethod & 0x02) = 0)
     OR (program.subt itle         <> ''
    AND program.subtitle            = recorded.subtitle))
    AND (((record.dupmethod & 0x04) = 0)
     OR (program.description       <> ''
    AND program.description         = recorded.description)) ) ) )
LEFT JOIN old find
ON (oldfind.recordid   = recordmatch.recordid
    AND oldfind.findid = (CASE record.type
	WHEN 6 THEN record.findid
	WHEN 9 THEN to_days(date_sub( program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute))
	WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday
	WHEN 7 THEN record.findid ELSE 0
END) )

{ big finish! }

ORDER BY record.recordid DESC;


Bruce Markey?  I lick the dust off your boots.

:-)

Cheers,
-- jra
-- 
Jay R. Ashworth                   Baylink                      jra at baylink.com
Designer                     The Things I Think                       RFC 2100
Ashworth & Associates     http://baylink.pitas.com                     '87 e24
St Petersburg FL USA      http://photo.imageinc.us             +1 727 647 1274