Add "accroissement" analysis

[romainbh]

Add "accroissement" analysis to get values by division, like in this attachment.

Accroissement_Et_Passage_Futaie.pdf

[kalbermattenm]

([volume inventorié lors du dernier inventaire (p. ex. 1254 m3 en 1999)] – [volume inventorié lors de l’avant-dernier inventaire (p.ex. 1193 m3 en 2019)] + [volume récolté entre les deux inventaires (p.ex. 480 m3 entre 1999 et 2018, pas 2019)]) / [période entre les deux inventaires (p.ex. 2019-1999 = 20 ans)] -> reste à diviser le résultat par la surface de la division forestière pour avoir une valeur en m3/ha.

[volume récolté entre les deux inventaires (p.ex. 480 m3 entre 1999 et 2018, pas 2019)] = volume coupé (année avant l'inventaire final, car celui-ci est fait au 1er janvier -> ne pas prendre en compte les coupes de cette même année)

Diviser le le résultat par le nombre d'année reflétant l'intervalle entre les interventaires (période)

Titre: INVENTAIRES - Accroissement par hectare par année

[kalbermattenm]

SELECT INV_DIV_ID, max(INV_ANNEE) as INV_ANNEE
FROM (
    select INV_DIV_ID, INV_ANNEE
    from dbo.Inventaires
    EXCEPT
    select INV_DIV_ID, max(INV_ANNEE) as INV_ANNEE
    from dbo.Inventaires group by INV_DIV_ID
) AS result group by INV_DIV_ID

Thanks @rognonn & @maltaesousa

[kalbermattenm]

SELECT 
annee_max.VOLUME_PAR_HECTARE - annee_min.VOLUME_PAR_HECTARE as volume_accroissement_total,
(annee_max.DERNIERE_DATE - 1) - annee_min.DERNIERE_DATE as nbre_annee,

CASE WHEN (annee_max.DERNIERE_DATE - 1) - annee_min.DERNIERE_DATE > 0 THEN
(annee_max.VOLUME_PAR_HECTARE - annee_min.VOLUME_PAR_HECTARE) / ((annee_max.DERNIERE_DATE - 1) - annee_min.DERNIERE_DATE) 
ELSE
(annee_max.VOLUME_PAR_HECTARE - annee_min.VOLUME_PAR_HECTARE)
END as volume_accroissement_annuel,
annee_max.DERNIERE_DATE as dernier_inv,
annee_min.DERNIERE_DATE as avant_dernier_inv

FROM (
select DIV_NUMERO_CANTONAL, DIV_SERIE,
CASE WHEN DIV_SURFACE > 0 THEN VOLUME_TOTAL / DIV_SURFACE ELSE 0 END as VOLUME_PAR_HECTARE, annee as DERNIERE_DATE
from    
    (select INV_DIV_ID, sum(VOLUME) as VOLUME_TOTAL  , max(INV_ANNEE) annee 
        from
        (select INV_DIV_ID, INV_ID, INV_ANNEE, INV_NOMBRE * sub5.CAT_VOLUME as VOLUME from 
                    (select * from 
                        (select inv_div_id id1  , max(INV_ANNEE) annee 
                        from dbo.Inventaires group by INV_DIV_ID) sub1, 
                        dbo.Inventaires sub2
                    where sub1.id1 = sub2.INV_DIV_ID and sub1.annee = sub2.INV_ANNEE --EDITTIMESTRING
                    ) sub3,
                    dbo.Categories sub5
            where  sub5.CAT_CODE = INV_CODE_CATEGORIE
        ) sub6
    group by INV_DIV_ID) subLink,
    Administrations,
    Massifs,
    Divisions
    where Divisions.DIV_ID = subLink.INV_DIV_ID 
        and DIV_MASSIF_ID = Massifs.MAS_ID 
        and Administrations.ADM_ID = Massifs.MAS_ADM_ID
) annee_max,
(
select DIV_NUMERO_CANTONAL, DIV_SERIE,
CASE WHEN DIV_SURFACE > 0 THEN VOLUME_TOTAL / DIV_SURFACE ELSE 0 END as VOLUME_PAR_HECTARE, annee as DERNIERE_DATE
from    
    (select INV_DIV_ID, sum(VOLUME) as VOLUME_TOTAL  , max(INV_ANNEE) annee 
        from
        (select INV_DIV_ID, INV_ID, INV_ANNEE, INV_NOMBRE * sub5.CAT_VOLUME as VOLUME from 
                    (select * from 
                        (select INV_DIV_ID id1, INV_ANNEE annee
                                        from dbo.Inventaires
                                        EXCEPT
                            select INV_DIV_ID, max(INV_ANNEE) as INV_ANNEE
                            from dbo.Inventaires group by INV_DIV_ID

                        ) sub1, 
                        dbo.Inventaires sub2
                    where sub1.id1 = sub2.INV_DIV_ID and sub1.annee = sub2.INV_ANNEE --EDITTIMESTRING
                    ) sub3,
                    dbo.Categories sub5
            where  sub5.CAT_CODE = INV_CODE_CATEGORIE
        ) sub6
    group by INV_DIV_ID) subLink,
    Administrations,
    Massifs,
    Divisions
    where Divisions.DIV_ID = subLink.INV_DIV_ID 
        and DIV_MASSIF_ID = Massifs.MAS_ID 
        and Administrations.ADM_ID = Massifs.MAS_ADM_ID
) annee_min
WHERE annee_max.DIV_NUMERO_CANTONAL = annee_min.DIV_NUMERO_CANTONAL AND annee_max.DIV_SERIE = annee_min.DIV_SERIE

[kalbermattenm]

Manque encore: volume récolté entre les deux inventaires

On peut utiliser le SQL de coupe pour un période:

select DIV_NUMERO_CANTONAL, VOLUME_COUPES, DIV_SERIE from 

    (select COU_DIV_ID, sum(COU_NOMBRE * CAT_VOLUME) as VOLUME_COUPES
    from Coupes inner join Categories on Coupes.COU_CODE_CATEGORIE = Categories.CAT_CODE
    where   COU_ANNEE >= --STARTYEAR
            and COU_ANNEE < --ENDYEAR     --EDITCOUPETYPE
    group by COU_DIV_ID) vcou 
    inner join Divisions on DIV_ID = COU_DIV_ID
    inner join Massifs on DIV_MASSIF_ID = MAS_ID
    inner join Administrations on ADM_ID = MAS_ADM_ID
    --EDITADMCODE