(I got help for this from searching postings on StackOverflow, which I
highly recommend when looking for programming answers. Also, if you don't
have something already, I highly recommend the free MySQL Workbench to
explore and work with database queries such as this.)
The simplest way to get the _001 cut and its play count is to add this
additional JOIN to the query:
LEFT JOIN CUTS ON Audio_SRT.CART_NUMBER = CUTS.CART_NUMBER AND
RIGHT(CUTS.CUT_NAME, 3) = '001'
This is a pretty brittle design, because it depends on the formatting of
that database column's value, which could theoretically change in a future
Rivendell.
Also, this doesn't work if the _001 cut has been deleted from the cart,
even if there is another cut that is now "first" in the cart.
Here's the query I ended up. View it in a fixed-width font to make it more
readable.
SELECT
cart.NUMBER as 'Cart number',
cuts.CUT_NAME as 'Cut name',
played.EVENT_DATETIME as 'Played on',
cuts.PLAY_COUNTER as 'Total plays',
TIME_FORMAT(SEC_TO_TIME(cart.FORCED_LENGTH / 1000), "%i:%s") as
'Duration: cart',
TIME_FORMAT(SEC_TO_TIME(played.LENGTH / 1000), "%i:%s") as
'Duration: played',
cart.TITLE as 'Song title',
cart.ARTIST as 'Artist',
cart.COMPOSER as 'Composer',
cart.LABEL as 'Label',
cart.ALBUM as 'Album title'
FROM rivendell.dick_k_srt AS played
LEFT JOIN cart ON played.CART_NUMBER = cart.NUMBER
LEFT JOIN cuts ON played.CART_NUMBER = cuts.CART_NUMBER AND
RIGHT(cuts.CUT_NAME, 3) = '001'
WHERE
cart.GROUP_NAME IN ('MUSIC', 'XMAS')
AND
played.EVENT_DATETIME BETWEEN
DATE_FORMAT( (NOW() - INTERVAL 1 MONTH), '%Y-%m-01
00:00:00') AND
DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
ORDER BY cart.NUMBER ASC, played.EVENT_DATETIME DESC
;
Mike
On Tue, May 22, 2018 at 9:15 PM Mircea Paun <[email protected]> wrote:
> First one.
> "Cut 001" or _001.
>
> Sent from my mobile device
> mIRCea
>
> On 23 May 2018, at 02:35, Mike Carroll <[email protected]> wrote:
>
> What have you already tried? Give us a hint so we don't waste time
> duplicating your work.
>
> There can be many cuts in a cart. Which one do you want?
>
>
> On Tue, May 22, 2018 at 9:41 AM Mircea Paun <[email protected]> wrote:
>
>> Hi,
>> From previous posts and my additions I have reached this form of mysql
>> query:
>> -------------
>> SELECT Audio_SRT.EVENT_DATETIME,\
>> CART.TITLE,\
>> CART.ARTIST,\
>> CART.COMPOSER,\
>> TIME_FORMAT(SEC_TO_TIME(Audio_SRT.LENGTH/1000),"%i:%s"),\
>> TIME_FORMAT(SEC_TO_TIME(CART.FORCED_LENGTH/1000),"%i:%s"),\
>> CART.LABEL,CART.ALBUM FROM `Audio_SRT`
>> LEFT JOIN CART ON Audio_SRT.CART_NUMBER = CART.NUMBER
>> WHERE (Audio_SRT.EVENT_DATETIME BETWEEN DATE_FORMAT(NOW() - INTERVAL 1
>> MONTH, '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1
>> MONTH), '%Y-%m-%d 23:59:59'))
>> AND (CART.GROUP_NAME in ('MUSIC','XMAS'))
>> INTO OUTFILE '/tmp/Playout.csv'
>> FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n';
>> ---------------
>> Does anyone help me to include PLAY_COUNTER field, from another "CUTS"
>> TABLE, in this query output, right after ARTIST ?
>> Thanks!
>>
>> *mIRCea*
>>
>> _______________________________________________
>> Rivendell-dev mailing list
>> [email protected]
>> http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev
>>
>
_______________________________________________
Rivendell-dev mailing list
[email protected]
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev
