Changes between Version 21 and Version 22 of Recette SQL SommeEffectifs

Timestamp:

May 20, 2016 5:23:58 PM (9 years ago)

Author:

cedric

Comment:

--

Recette SQL SommeEffectifs

@@ -103 +103 @@
 ||Silurus glanis||3||||
 ||Tinca tinca||1||||
+
+
+[[BR]]
+== Somme des effectifs de civelles sur les deux passes du barrage d'Arzal par mois et années ==
+{{{
+/*
+requete complete
+*/
+with lot_ope_detail as (
+SELECT t_operation_ope.ope_identifiant, t_lot_lot.lot_identifiant, t_operation_ope.ope_dic_identifiant, 
+t_lot_lot.lot_lot_identifiant AS lot_pere, t_operation_ope.ope_date_debut, t_operation_ope.ope_date_fin, 
+t_lot_lot.lot_effectif, t_lot_lot.lot_quantite, t_lot_lot.lot_tax_code, t_lot_lot.lot_std_code, tr_taxon_tax.tax_nom_latin, 
+tr_stadedeveloppement_std.std_libelle, tr_devenirlot_dev.dev_code, tr_devenirlot_dev.dev_libelle
+   FROM iav.t_operation_ope
+   JOIN iav.t_lot_lot ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant
+   LEFT JOIN ref.tr_typequantitelot_qte ON tr_typequantitelot_qte.qte_code::text = t_lot_lot.lot_qte_code::text
+   LEFT JOIN ref.tr_devenirlot_dev ON tr_devenirlot_dev.dev_code::text = t_lot_lot.lot_dev_code::text
+   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = t_lot_lot.lot_tax_code::text
+   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = t_lot_lot.lot_std_code::text)
+select * from lot_ope_detail
+
+
+/*
+Somme des effectifs de civelles sur les deux passes d'Arzal par mois
+*/
+
+with lot_ope_detail as (
+SELECT t_operation_ope.ope_identifiant, t_lot_lot.lot_identifiant, t_operation_ope.ope_dic_identifiant, 
+t_lot_lot.lot_lot_identifiant AS lot_pere, t_operation_ope.ope_date_debut, t_operation_ope.ope_date_fin, 
+t_lot_lot.lot_effectif, t_lot_lot.lot_quantite, t_lot_lot.lot_tax_code, t_lot_lot.lot_std_code, tr_taxon_tax.tax_nom_latin, 
+tr_stadedeveloppement_std.std_libelle, tr_devenirlot_dev.dev_code, tr_devenirlot_dev.dev_libelle
+   FROM iav.t_operation_ope
+   JOIN iav.t_lot_lot ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant
+   LEFT JOIN ref.tr_typequantitelot_qte ON tr_typequantitelot_qte.qte_code::text = t_lot_lot.lot_qte_code::text
+   LEFT JOIN ref.tr_devenirlot_dev ON tr_devenirlot_dev.dev_code::text = t_lot_lot.lot_dev_code::text
+   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = t_lot_lot.lot_tax_code::text
+   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = t_lot_lot.lot_std_code::text)
+
+select  sum(s.lot_quantite),s.annee,s.mois from(
+select *, extract(month from ope_date_debut) as mois,extract(year from ope_date_debut) as annee  from lot_ope_detail
+where lot_tax_code='2038'
+and (ope_dic_identifiant=6 or ope_dic_identifiant=12)
+and lot_quantite is not null
+and lot_std_code='CIV'
+) as s
+group by s.annee,s.mois
+order by s.annee,s.mois
+
+}}}