SQL Sample

sqlsample.sql

SELECT s.eh                                             AS hub,
       Month(Concat(s.yearmonth, '-01'))                AS m,
       Sum(IF(Year(Concat(s.yearmonth, '-01')) = 2006, Round(
               1000 * s.elec_price / s.gas_price), '')) AS '2006',
       Sum(IF(Year(Concat(s.yearmonth, '-01')) = 2007, Round(
               1000 * s.elec_price / s.gas_price), '')) AS '2007',
       Sum(IF(Year(Concat(s.yearmonth, '-01')) = 2008, Round(
               1000 * s.elec_price / s.gas_price), '')) AS '2008',
       Sum(IF(Year(Concat(s.yearmonth, '-01')) = 2009, Round(
               1000 * s.elec_price / s.gas_price), '')) AS '2009',
       Sum(IF(Year(Concat(s.yearmonth, '-01')) = 2010, Round(
               1000 * s.elec_price / s.gas_price), '')) AS '2010',
       Sum(IF(Year(Concat(s.yearmonth, '-01')) = 2011, Round(
               1000 * s.elec_price / s.gas_price), '')) AS '2011',
       Sum(IF(Year(Concat(s.yearmonth, '-01')) = 2012, Round(
               1000 * s.elec_price / s.gas_price), '')) AS '2012',
       Sum(IF(Year(Concat(s.yearmonth, '-01')) = 2013, Round(
               1000 * s.elec_price / s.gas_price), '')) AS '2013'
FROM   (SELECT hub.hub_abbreviation                         AS eh,
               elec_spot.hub_id                             AS elec_hub_id,
               Date_format(elec_spot.`start_date`, '%Y-%m') AS yearmonth,
               Round(Avg(gas_spot.price), 2)                AS gas_price,
               Date_format(elec_spot.`start_date`, '%m')    AS mo,
               Round(Avg(elec_spot.price), 2)               AS elec_price
        FROM   hub
               INNER JOIN elec_spot
                       ON hub.hub_id = elec_spot.hub_id
               INNER JOIN gas_hubs
                       ON hub.gas_hub_id = gas_hubs.gas_hub_id
               INNER JOIN gas_spot
                       ON gas_hubs.gas_hub_id = gas_spot.gas_hub_id
                          AND gas_spot.`date` = elec_spot.`start_date`
        WHERE  hub.hub_id = 1
               AND Date_format(elec_spot.`start_date`, '%Y-%m') <
                   Date_format(Now(), '%Y-%m')
        GROUP  BY yearmonth
        HAVING yearmonth >= '2005-01'
        UNION ALL
        SELECT hub.hub_abbreviation                     AS eh,
               'id'                                     AS elec_hub_id,
               Date_format(hub_results.f_date, '%Y-%m') AS yearmonth,
               Round(Avg(gas_forecast.gas_price), 2)    AS gas_price,
               Date_format(hub_results.f_date, '%m')    AS mo,
               Round(Avg(hub_results.value), 2)         AS elec_price
        FROM   gas_hubs
               INNER JOIN gas_forecast
                       ON gas_hubs.gas_hub_id = gas_forecast.gas_hub_id
               INNER JOIN hub
                       ON hub.gas_hub_id = gas_hubs.gas_hub_id
               INNER JOIN hub_results
                       ON hub.hub_id = hub_results.hub_id
                          AND gas_forecast.f_date = hub_results.f_date
               INNER JOIN day_type
                       ON day_type.f_date = hub_results.f_date
        WHERE  gas_forecast.r_date = (SELECT Max(r_date)
                                      FROM   gas_forecast)
               AND hub_results.r_date = (SELECT Max(r_date)
                                         FROM   hub_results)
               AND hub_results.hub_id = 1
               AND hub_results.f_date >= Now()
               AND hub_results.cat_id = 16
               AND hub_results.tod_id = 1
               AND day_type.day_type = 1
        GROUP  BY yearmonth) AS s
GROUP  BY s.mo
ORDER  BY s.mo ASC