Water abstraction for agriculture indexed 2010=100

PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX sdmx-measure: <http://purl.org/linked-data/sdmx/2009/measure#> 
PREFIX sdmx-dimension: <http://purl.org/linked-data/sdmx/2009/dimension#>
PREFIX sdmx-attribute: <http://purl.org/linked-data/sdmx/2009/attribute#>
PREFIX property: <http://rdfdata.eionet.europa.eu/eurostat/property#> 
PREFIX unit: <http://dd.eionet.europa.eu/vocabulary/eurostat/unit/>
PREFIX indic_ag: <http://dd.eionet.europa.eu/vocabulary/eurostat/indic_ag/>
PREFIX itm_newa: <http://dd.eionet.europa.eu/vocabulary/eurostat/itm_newa/>
PREFIX indic_ef: <http://dd.eionet.europa.eu/vocabulary/eurostat/indic_ef/>
PREFIX irrig: <http://dd.eionet.europa.eu/vocabulary/eurostat/irrig/> 
PREFIX agrarea: <http://dd.eionet.europa.eu/vocabulary/eurostat/agrarea/>
PREFIX geo: <http://dd.eionet.europa.eu/vocabulary/eurostat/geo/> 
PREFIX wat_proc: <http://dd.eionet.europa.eu/vocabulary/eurostat/wat_proc/>
PREFIX wat_src: <http://dd.eionet.europa.eu/vocabulary/eurostat/wat_src/>
PREFIX countries: <http://dd.eionet.europa.eu/vocabulary/common/countries/>
PREFIX swbewa2017: <http://semantic.eea.europa.eu/project/indicators/gap_filling_data/WREI004/2017/swbewa2017.tsv#>
PREFIX fadn2017: <http://semantic.eea.europa.eu/project/indicators/gap_filling_data/WREI004/2017/fadn2017.tsv#>
PREFIX env_wat_abs_abs_nace_A011_A013: <http://semantic.eea.europa.eu/project/indicators/gap_filling_data/WREI004/2017/env_wat_abs_abs_nace_A011_A013.tsv#>

SELECT
?date as ?timePeriod
?geo as ?refArea
year(?date) as ?date
?ugeo
#?T_MT
#?T_MT_idx
'water abstraction for irrigation' as ?label
round(?T_MT/?T_MT_idx *1000)/10.0 as ?index

{{

SELECT DISTINCT
?date
?geo
?ugeo

(sum(xsd:double(?env_wat_abs_abs_nace_A011_A013)) + sum(coalesce(?env_wat_use,0))) / ( sum(?B_6_2_2_HA/2.0) + sum(?B_4_7_7_2_8_2_HA/2.0) ) *1000000 as ?T_MT

(sum(xsd:double(?env_wat_abs_abs_nace_A011_A013_idx)) + sum(coalesce(?env_wat_use_idx,0))) /( sum(?B_6_2_2_HA_idx/2.0) + sum(?B_4_7_7_2_8_2_HA_idx/2.0) ) *1000000 as ?T_MT_idx

WHERE {{ 
  {
    GRAPH <http://semantic.eea.europa.eu/project/indicators/gap_filling_data/WREI004/2017/env_wat_abs_abs_nace_A011_A013.tsv>  
    {  
      _:env_wat_abs_abs_nace_A011_A013 env_wat_abs_abs_nace_A011_A013:date ?date .
      _:env_wat_abs_abs_nace_A011_A013 env_wat_abs_abs_nace_A011_A013:geo ?geo .
      _:env_wat_abs_abs_nace_A011_A013 env_wat_abs_abs_nace_A011_A013:obsValue ?env_wat_abs_abs_nace_A011_A013 .

      _:env_wat_abs_abs_nace_A011_A013_idx env_wat_abs_abs_nace_A011_A013:date ?date_idx .
      _:env_wat_abs_abs_nace_A011_A013_idx env_wat_abs_abs_nace_A011_A013:geo ?geo .
      _:env_wat_abs_abs_nace_A011_A013_idx env_wat_abs_abs_nace_A011_A013:obsValue ?env_wat_abs_abs_nace_A011_A013_idx .
    }
  } UNION {
    GRAPH <http://rdfdata.eionet.europa.eu/eurostat/data/env_wat_use.rdf.gz>  { 
      _:env_wat_use sdmx-dimension:timePeriod ?date . 
      _:env_wat_use sdmx-dimension:refArea ?geo . 
      _:env_wat_use property:wat_proc wat_proc:ABS_AGR_IR . 
      _:env_wat_use sdmx-attribute:unitMeasure unit:MIO_M3 . 
      _:env_wat_use property:wat_src ?wat_src .
      FILTER (?wat_src in (wat_src:RUW, wat_src:DSW, wat_src:NFW))
      _:env_wat_use sdmx-measure:obsValue ?env_wat_use .

      _:env_wat_use_idx sdmx-dimension:timePeriod ?date_idx . 
      _:env_wat_use_idx sdmx-dimension:refArea ?geo . 
      _:env_wat_use_idx property:wat_proc wat_proc:ABS_AGR_IR . 
      _:env_wat_use_idx sdmx-attribute:unitMeasure unit:MIO_M3 . 
      _:env_wat_use_idx property:wat_src ?wat_src_idx .
      FILTER (?wat_src_idx in (wat_src:RUW, wat_src:DSW, wat_src:NFW))
      _:env_wat_use_idx sdmx-measure:obsValue ?env_wat_use_idx .
    }
  } UNION {
    GRAPH <http://rdfdata.eionet.europa.eu/eurostat/data/ef_poirrig.rdf.gz>  
    {  
      _:ef_poirrig sdmx-dimension:timePeriod ?date .
      _:ef_poirrig sdmx-dimension:refArea ?geo .
      _:ef_poirrig property:irrig irrig:TOTAL .
      { 
        _:ef_poirrig property:indic_ef indic_ef:B_6_2_2_HA .
        _:ef_poirrig sdmx-measure:obsValue ?B_6_2_2_HA .
      } UNION {
        _:ef_poirrig property:indic_ef indic_ef:B_4_7_7_2_8_2_HA .
        _:ef_poirrig sdmx-measure:obsValue ?B_4_7_7_2_8_2_HA .
      }

      _:ef_poirrig_idx sdmx-dimension:timePeriod ?date_idx .
      _:ef_poirrig_idx sdmx-dimension:refArea ?geo .
      _:ef_poirrig_idx property:irrig irrig:TOTAL .
      { 
        _:ef_poirrig_idx property:indic_ef indic_ef:B_6_2_2_HA .
        _:ef_poirrig_idx sdmx-measure:obsValue ?B_6_2_2_HA_idx .
      } UNION {
        _:ef_poirrig_idx property:indic_ef indic_ef:B_4_7_7_2_8_2_HA .
        _:ef_poirrig_idx sdmx-measure:obsValue ?B_4_7_7_2_8_2_HA_idx .
      }
    }
  }

  ?geo skos:notation ?geoNotation  .
  ?geo skos:exactMatch ?eeauri .
  ?eeauri skos:inScheme countries: .
  ?eeauri rdfs:label ?ugeo .
  FILTER (strlen(?geoNotation) = 2)
  #FILTER (?geo = geo:DE)
  FILTER (year(?date) in (2005, 2007, 2010, 2013))
  FILTER (year(?date_idx) in (2010))
 }}
GROUP BY ?ugeo year(?date) ?geo
}}
HAVING ?T_MT_idx > 0 && ?T_MT > 0
ORDER BY ?ugeo ?date