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
| select jr, usag, sum(conso/1000) as conso, CONCAT(an,'-',LPAD(moi,2,'0'),'-',LPAD(jr,2,'0')) as date_sem,
case
when jr=DAY(DATE_SUB(now(),interval weekday(now()) day )) then 'Lundi'
when jr=DAY(DATE_SUB(now(),interval weekday(now())-1 day )) then 'Mardi'
when jr=DAY(DATE_SUB(now(),interval weekday(now())-2 day )) then 'Mercredi'
when jr=DAY(DATE_SUB(now(),interval weekday(now())-3 day )) then 'Jeudi'
when jr=DAY(DATE_SUB(now(),interval weekday(now())-4 day )) then 'Vendredi'
when jr=DAY(DATE_SUB(now(),interval weekday(now())-5 day )) then 'Samedi'
when jr= DAY(DATE_SUB(now(),interval weekday(now())-6 day )) then 'Dimanche'
end as jr_sem,
case
when usag='1' then 'ECLAIRAGE'
when usag='2' then 'AUXILIAIRE'
when usag='3' then 'ECS'
when usag='4' then 'CHAUFFAGE'
when usag='5' then'CLIMATISATION'
end as Char_Usage
from nrj
where CONCAT(an,'-',LPAD(moi,2,'0'),'-',LPAD(jr,2,'0')) between
CONCAT(YEAR(DATE_SUB(now(),interval weekday(now()) day )),'-',LPAD(MONTH(DATE_SUB(now(),interval weekday(now()) day )),2,'0'),'-',LPAD(DAY(DATE_SUB(now(),interval weekday(now()) day )),2,'0')) and
CONCAT(YEAR(now()),'-',LPAD(MONTH(now()),2,'0'),'-',LPAD(DAY(now()),2,'0')) and
usag between 1 and 5
group by usag, date_sem
order by usag desc, date_sem asc |
Partager