available-hours-month.sql

available-hours-month.sql

SELECT
    fbs.service_id,
    fbs.year,
    CASE
        WHEN MONTH(fbs.published_date) = '1' THEN 'Jan'
        WHEN MONTH(fbs.published_date) = '2' THEN 'Feb'
        WHEN MONTH(fbs.published_date) = '3' THEN 'Mar'
        WHEN MONTH(fbs.published_date) = '4' THEN 'Apr'
        WHEN MONTH(fbs.published_date) = '5' THEN 'May'
        WHEN MONTH(fbs.published_date) = '6' THEN 'Jun'
        WHEN MONTH(fbs.published_date) = '7' THEN 'Jul'
        WHEN MONTH(fbs.published_date) = '8' THEN 'Aug'
        WHEN MONTH(fbs.published_date) = '9' THEN 'Sep'
        WHEN MONTH(fbs.published_date) = '10' THEN 'Oct'
        WHEN MONTH(fbs.published_date) = '11' THEN 'Nov'
        WHEN MONTH(fbs.published_date) = '12' THEN 'Dec'
    END AS 'month',
    FLOOR((SUM(fbs.version_duration) / 60) / 60) AS 'aggregate_version_durations',
    FLOOR((SUM(fbs.first_broadcast_duration) / 60) / 60) AS 'aggregate_first_broadcast_durations'
FROM
    (SELECT DISTINCT
        v.pid AS 'version_pid',
            b.service_id,
            YEAR(b.published_time) AS 'year',
            MIN(DATE(b.published_time)) AS 'published_date',
            v.duration AS 'version_duration',
            b.pid AS 'broadcast_pid',
            b.published_duration AS 'first_broadcast_duration'
    FROM
        version v
    JOIN version_version_type vvt ON vvt.pid = v.pid
        AND vvt.version_type_id = 'Original'
    JOIN broadcast b ON b.version_pid = v.pid
    JOIN service s ON s.service_id = b.service_id
        AND s.type IN ('National Radio' , 'Local Radio', 'Regional Radio')
    WHERE
        YEAR(b.published_time) = '2017'
    GROUP BY v.pid) AS fbs
GROUP BY fbs.service_id , MONTH(fbs.published_date)