1 2 3 4 5 6 7 8 9 10 11
| select E.date1,E.conso_HC,E.conso_HP ,
coalesce(E.conso_HC-lag(E.conso_HC,1)over(order by E.date1 ASC),0)as conso_hc_jour_hier,
coalesce(E.conso_HC-lag(E.conso_HP,1)over(order by E.date1 ASC),0)as conso_hc_jour_hier,
(E.conso_HC-INDEXE2.conso_HC)as conso_hc_mensuelle,
(E.conso_HP-INDEXE2.conso_HP)as conso_hc_mensuelle,
(E.conso_HC-INDEXE3.conso_HC)as conso_hc_annuelle,
(E.conso_HP-INDEXE3.conso_HP)as conso_hp_annuelle
from indexe E left outer join (select * from indexe where date1=DATEADD ( mm , DATEDIFF ( mm , 0 , date1), 0 )) as indexe2
ON MONTH(E.date1)=MONTH(indexe2.date1)
left outer join ( select * from indexe where date1=DATEADD ( yy , DATEDIFF ( yy , 0 , date1), 0 )) as indexe3
on year(E.date1)=year(indexe3.date1) |
Partager