-- Entrance example using the Keeling Curve data originally from: 
--   http://scrippsco2.ucsd.edu/data/in_situ_co2/monthly_mlo.csv
-- 
-- Create the sample data for this example by downloading and running:
--   http://dbentrance.com/demo/create_keeling.sql
-- 
-- This example uses two extra columns to hold values and labels
-- for the chart.  The 'CO2' column is filled in with data values
-- from the original Keeling dataset, then its missing data markers
-- are replaced with NULLs.  The 'labels'column is filled in with
-- labels for the x axis.  
--
-- The full data set can be downloaded from the URL above using the 
-- Entrance Import tool. Check "Skip header" and select "Comma delimited" 
-- type on the Entrance import dialog with the web page address in the 
-- URL text box. You can also make a copy of the full page text by 
-- importing it as "Simple Text" type.  
--
-- Visit http://dbentrance.com for more information about Entrance.

-- use test;


-- make a copy of the CO2 data
update KeelingCurve
  set CO2 = original_CO2;

-- replace "no data" flags with NULL values
update KeelingCurve
  set CO2 = NULL
  where CO2 = -99.99;   

-- pick out x labels
update KeelingCurve
  set labels = NULL;

update KeelingCurve
--  set labels = substring(year, 3, 2)
  set labels = year
  where month = 01
    and cast(year as unsigned) % 5 = 0;

-- plot the data
plot linechart
    all xlabels, scarletred line, color override, skip, skip
  with
    title y "CO2 per mole (ppm)"
    scale 300 400 25
    dotted gridlines
    title "The Keeling Curve"
    legend
    note left "Atmospheric CO2 concentrations (ppm) derived from in situ air measurements"
    note left "at Mauna Loa, Observatory, Hawaii"
    note left "Source:  http://scrippsco2.ucsd.edu/data/in_situ_co2/monthly_mlo.csv"
    note ""
select labels, CO2, color, year, month 
    from KeelingCurve;