Now I've finally had the time to try this out. Works like a charm, thank you very much!
Kind regards, Morten radiomax.dk 2013/4/11 Robert Orr <[email protected]> > Here's mine that works for SOCAN: > SELECT AllHours_SRT.EVENT_DATETIME, CART.TITLE, CART.ARTIST, > CART.COMPOSER, AllHours_SRT.LENGTH, CART.LABEL, CART.ALBUM FROM > `AllHours_SRT` LEFT JOIN CART ON AllHours_SRT.CART_NUMBER = CART.NUMBER > WHERE (AllHours_SRT.EVENT_DATETIME BETWEEN "2013-02-08 06:40:00" AND > "2013-02-08 06:59:59") AND (CART.GROUP_NAME <> "TEMP") AND (CART.GROUP_NAME > <> "TEST") AND (CART.GROUP_NAME <> "ads") AND (AllHours_SRT.CART_NUMBER <> > "26000") AND (AllHours_SRT.CART_NUMBER <> "4000") AND (CART.GROUP_NAME <> > "ID") AND (CART.GROUP_NAME <> "currevents") into outfile "/tmp/this.txt"; > I needed to do the left join part because one of the tables contained some > of the data I needed and another one contained the rest. > Have a great day, > Robert > > > On Thu, Apr 11, 2013 at 1:44 AM, Morten Krarup Nielsen <[email protected] > > wrote: > >> Thank you very much, Michael. I will try it out :-) >> >> Kind regards, >> >> Morten >> radiomax.dk >> >> >> 2013/4/9 MICHAEL SMITH <[email protected]> >> >>> >>> I can't take any of the credit for this, but... >>> >>> Log into the mysql prompt (or write a script and pipe this to mysql): >>> >>> SELECT COUNT(*) AS Plays, CART_NUMBER, TITLE, ARTIST FROM >>> yourstationservicename_SRT WHERE STATION_NAME="playoutcomputername" && >>> ((CART_NUMBER >=0 AND CART_NUMBER <=75000) OR (CART_NUMBER >=100000 AND >>> CART_NUMBER <=300000)) && EVENT_DATETIME > "2013-03-22" && >>> EVENT_DATETIME < "2013-03-28" GROUP BY CART_NUMBER ORDER BY Plays DESC >>> LIMIT 50; >>> >>> A quick explanation >>> yourstationservicename = the name of the service that your station runs >>> in Rivendell >>> Station_name= the name of the computer which plays out using rdairplay. >>> You may have to expand this if you have lots of machines operating the on >>> air play >>> Cart number: these are ranges which count towards the chart. Our >>> jingles/beds etc... reside between 75000 and 100000 so we don't count play >>> of those. >>> The dates are the chart date range >>> We call up 50 items (Desc limit) as we have a top 40 and no doubt >>> someone will have loaded in a bed/jingle as a music item! At least it helps >>> us spot them. >>> >>> To make this work in a script: >>> >>> mysql -u yourmysqlusername -pyourmysqlpassword -h mysqlserveripaddress >>> databasename >>> >>> e.g. mysql -u rduser -ppassword -h 192.168.1.100 Rivendell < >>> /home/rduser/scriptasabove.txt >>> >>> >>> ------------------------------ >>> *From:* Morten Krarup Nielsen <[email protected]> >>> *To:* User discussion about the Rivendell Radio Automation System < >>> [email protected]> >>> *Sent:* Tuesday, 9 April 2013, 13:30 >>> *Subject:* [RDD] Generating Airplay-chart >>> >>> Hi. >>> >>> We want to generate an weekly Airplay Chart to publish to our listeners >>> and the record companies. >>> I guess we're not the first station that want to make this Airplay >>> chart, so how do we do this, without me learning PHP and SQL? :-) >>> >>> Thank you! >>> >>> Kind regards, >>> >>> Morten >>> >>> _______________________________________________ >>> Rivendell-dev mailing list >>> [email protected] >>> http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev >>> >>> >>> >>> _______________________________________________ >>> Rivendell-dev mailing list >>> [email protected] >>> http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev >>> >>> >> >> _______________________________________________ >> Rivendell-dev mailing list >> [email protected] >> http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev >> >> > > _______________________________________________ > Rivendell-dev mailing list > [email protected] > http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev > >
_______________________________________________ Rivendell-dev mailing list [email protected] http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev
