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
