Udtræksspecifikationer
Pejlinger

For pejlinger udtrækkes nyeste pejling for hvert indtag med tilhørende statistik.

SQL

SELECT
    ROW_NUMBER() OVER (ORDER BY 1)  id,
    -- Udfyld kun med anlæg hvis unikt 
    CASE WHEN COUNT(*) >1 THEN 'Knyttet til flere anlæg' ELSE MAX(anlaegsnavn) END anlaegs_navn,
    CASE WHEN COUNT(*) = 1 THEN MAX(anl2.anlaegsnavn) END overanlaegs_navn,
    CASE WHEN COUNT(*) = 1 THEN MAX(indtagsnr) END indtag_nr,
    pej.borid, 
    pej.indtagsid,
    CASE WHEN COUNT(*) = 1 THEN MAX(anlaegid) END anlaeg_id,
    CASE WHEN COUNT(*) = 1 THEN MAX(virksomhedstype) END virksomhedstype_kode,
    CASE WHEN COUNT(*) = 1 THEN MAX(vl.virksomhedstype1992) END virksomhedstype_tekst,
    CASE WHEN COUNT(*) = 1 THEN MAX(kommunenr2007) END kommune_nr,
    CASE WHEN COUNT(*) = 1 THEN MAX(k.kommunenavn) END kommune_navn,
    CASE WHEN COUNT(*) = 1 THEN MAX(ial.lang_tekst ) END indtag_anvendelse_tekst,
    -- TODO: Næste 4 cases skal rettes til mgau's akvitard beregning
    CASE                         
         WHEN MIN(bf.top) IS NULL AND MAX(bf.bund) IS NULL THEN MAX(boringsdybde)-2
         WHEN MIN(bf.top) IS NULL THEN MAX(bf.bund) - 2
         ELSE MIN(bf.top)
    END indtag_top_dybde, 
    CASE 
         WHEN MIN(bf.top) IS NULL AND MAX(bf.bund) IS NULL THEN 'Boringdybde minus 2 m anvendt'
         WHEN MIN(bf.top) IS NULL THEN  'Dybdebund minus 2 m. anvendt'
         ELSE  'Dybdetop anvendt'
    END indtag_top_dybde_forklaret, 
    CASE
         WHEN MAX(bf.bund) IS NULL AND MIN(bf.top) IS NULL THEN MAX(boringsdybde)
         WHEN MAX(bf.bund) IS NULL THEN MIN(bf.top) + 2
         ELSE MAX(bf.bund) 
    END indtag_bund_dybde,
    CASE
         WHEN MAX(bf.bund) IS NULL AND MIN(bf.top) IS NULL THEN 'Boringdybde anvendt'
         WHEN MAX(bf.bund) IS NULL THEN 'Dybdetop plus 2 m. anvendt'
         ELSE 'Dybdebund anvendt'
    END indtag_bund_dybde_forklaret,
    --(MONTHS_BETWEEN(dato_seneste, dato_foerste)/12)aar_pejleserie -- længden af pejletidsserie
    CASE 
        WHEN (MONTHS_BETWEEN(dato_seneste, dato_foerste)/12) > 20 AND frkvns_5_aar >= 4  THEN 6
        WHEN (MONTHS_BETWEEN(dato_seneste, dato_foerste)/12) > 20                        THEN 4
        WHEN (MONTHS_BETWEEN(dato_seneste, dato_foerste)/12) > 10 AND frkvns_5_aar >= 4  THEN 5
        WHEN (MONTHS_BETWEEN(dato_seneste, dato_foerste)/12) > 10                        THEN 3
        WHEN (MONTHS_BETWEEN(dato_seneste, dato_foerste)/12) > 3 AND frkvns_5_aar >= 4   THEN 2
                                                                                         ELSE 1
    END AS klassifikation,                  
    antal_pejlinger,
    dato_foerste,
    kote_seneste,
    dato_seneste,
    (vandspejl_seneste) vsp_mtu_seneste, -- vandspejl meter under terræn
    median_kote,
    max_kote,
    dato_max_kote,
    min_kote,
    dato_min_kote,
    ROUND(stddev_kote, 3)stddev_kote,   
    (max_kote - min_kote) diff_max_min,  
    synkron,  
    'http://data.geus.dk/JupiterWWW/pejlinger.jsp?'||'borid=' 
    	|| pej.borid AS url_tidsserie,
    CASE WHEN COUNT(*) = 1 THEN MAX(boringsdybde) END boringsdybde
FROM
    (
    -- Aggregering på pejlinger
    SELECT DISTINCT
        borid,
        indtagsid,
        FIRST_VALUE(vandstandkote) 
        	OVER ( PARTITION BY borid, indtagsid ORDER BY pejletidspunkt DESC ) kote_seneste,
        FIRST_VALUE(vandstandterraen) 
        	OVER ( PARTITION BY borid, indtagsid ORDER BY pejletidspunkt DESC ) vandspejl_seneste,
        FIRST_VALUE(pejletidspunkt) 
        	OVER ( PARTITION BY borid, indtagsid ORDER BY vandstandkote DESC ) dato_max_kote,
        FIRST_VALUE(pejletidspunkt) 
        	OVER ( PARTITION BY borid, indtagsid ORDER BY vandstandkote ) dato_min_kote,
        -- udfyldes hvis dette indtag har deltaget i synkronpejling (meget få forekomster)          
        MAX(pejlerundeid) 
        	OVER ( PARTITION BY borid, indtagsid ) synkron, 
        COUNT(vandstand) 
        	OVER ( PARTITION BY borid, indtagsid ) antal_pejlinger,
        MEDIAN(vandstandkote) 
        	OVER ( PARTITION BY borid, indtagsid ) median_kote,
        MAX(vandstandkote) 
        	OVER ( PARTITION BY borid, indtagsid ) max_kote,
        MIN(vandstandkote) 
        	OVER ( PARTITION BY borid, indtagsid ) min_kote,
        STDDEV(vandstandkote) 
        	OVER ( PARTITION BY borid, indtagsid ) stddev_kote,
        MIN(pejletidspunkt) 
        	OVER ( PARTITION BY borid, indtagsid ) dato_foerste,
        MAX(pejletidspunkt) 
        	OVER ( PARTITION BY borid, indtagsid ) dato_seneste,
        -- Antal pejlinger de sidste 5 år divideret med 5 
        0.2 * SUM( CASE WHEN pejletidspunkt > ADD_MONTHS(SYSDATE,-5*12) THEN 1 ELSE 0 END ) 
        	OVER ( PARTITION BY borid, indtagsid ) frkvns_5_aar  
    FROM                
        mis.pej$pejling pej 
     ) pej 
INNER JOIN
    mis.bor$indtag i ON i.borid = pej.borid AND i.indtagsid = pej.indtagsid
INNER JOIN              
    mis.bor$boring b ON b.borid = pej.borid 
LEFT OUTER JOIN
    mis.drv$indtag_anlaeg indanl ON indanl.borid = pej.borid AND indanl.indtagsid = pej.indtagsid
LEFT OUTER JOIN
    mis.drv$anlaeg anl ON anl.anlaegid = indanl.anlaegid
LEFT OUTER JOIN 
    mis.drv$anlaeg anl2 ON anl2.anlaegid = anl.overanlaeg
LEFT OUTER JOIN
    mis.bor$indtaganvend_liste ial ON ial.kode = indanl.indtagsanvendelse
LEFT OUTER JOIN
    mis.gen$kommune2007 k ON k.kommunenr = anl.kommunenr2007
LEFT OUTER JOIN
    mis.drv$virksomhedstype_liste vl ON vl.kode = anl.virksomhedstype
LEFT OUTER JOIN
    mis.bor$filter bf ON bf.borid = pej.borid AND bf.indtagsid = pej.indtagsid
WHERE 
    b.xutm32euref89 <> 0
GROUP BY 
    pej.borid, 
    pej.indtagsid,
    pej.kote_seneste,
    pej.vandspejl_seneste,
    pej.dato_max_kote,
    pej.dato_min_kote,          
    pej.synkron, 
    pej.antal_pejlinger,
    pej.median_kote,
    pej.max_kote,
    pej.min_kote,
    pej.stddev_kote,
    pej.dato_foerste,
    pej.dato_seneste,
    pej.frkvns_5_aar;