1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
| select
ifnull(A_marque,'Marque') as 'Marque',
ifnull(L_nom_groupement,'Groupement') as 'Groupement',
ifnull(A_nom_laboratoire,'Laboratoire') as 'Laboratoire',
EXTRACT(YEAR FROM (DATE_SUB(DATE_FORMAT(curdate(),'%Y-%m-%d'), INTERVAL 2 year))) as 'annee_moins_2',
DATE_FORMAT(((DATE_SUB(DATE_FORMAT(curdate(),'%Y-%m-%d'), INTERVAL 1 month))), '%Y') as 'annee_courant',
DATE_FORMAT(((DATE_SUB(DATE_FORMAT(curdate(),'%Y-%m-%d'), INTERVAL 1 month))), '%b') as 'mois_cumul',
EXTRACT(YEAR FROM (DATE_SUB(DATE_FORMAT(curdate(),'%Y-%m-%d'), INTERVAL 1 year))) as 'annee_moins_1',
sums.ansmoins2,
sums.ansmoins1,
sums.ansmoins1cumul,
sums.anscumul,
round(100 / sums.ansmoins2 * sums.ansmoins1,2) as Pourcents_ans,
round(100 / sums.ansmoins1cumul * sums.anscumul,2) as Pourcents_ans_cumul
from
(
select
L_no_client,
L_nom,
L_ville,
A_no,
A_nom,
A_marque,
L_nom_groupement,
A_nom_laboratoire,
sum(
if(
(statn.`D_facture` >= DATE_SUB(DATE_FORMAT(curdate(),'%Y-01-01'), INTERVAL 2 year)
and
statn.`D_facture` <=
DATE_SUB(DATE_FORMAT(curdate(),'%Y-12-31'), INTERVAL 2 year)),A_montant,0)) as 'ansmoins2',
sum(
if(
(statn.`D_facture` >=
DATE_SUB(DATE_FORMAT(curdate(),'%Y-01-01'), INTERVAL 1 year)
and
statn.`D_facture` <=
DATE_SUB(DATE_FORMAT(curdate(),'%Y-12-31'), INTERVAL 1 year)),A_montant,0)) as 'ansmoins1',
sum(
if(
(statn.`D_facture` >=
DATE_SUB(DATE_FORMAT(curdate(),'%Y-01-01'), INTERVAL 1 year)
and
statn.`D_facture` <=
DATE_SUB(DATE_FORMAT(curdate(),'%Y-%m-%d'), INTERVAL 1 year)),A_montant,0)) as 'ansmoins1cumul',
sum(
if(
statn.`D_facture` >= DATE_FORMAT(curdate(),'%Y-01-01')
and
statn.`D_facture` <= DATE_FORMAT(curdate(),'%Y-12-31'),A_montant,0)) as 'anscumul'
from statn
group by A_marque,L_nom_groupement with rollup)
as sums; |
Partager