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
|
WITH cr
as
(
select
immatriculation,
km,
date as date_cr,
row_number() over (partition by immatriculation, extract(month from date_cr), extract(year from date_cr) order by date_cr asc) as position_asc,
row_number() over (partition by immatriculation, extract(month from date_cr), extract(year from date_cr) order by date_cr desc) as position_desc,
extract(month from date_cr) as mois,
extract(year from date_cr) as annee
from course_reg
)
select
cr.mois,
cr.annee,
cr.immatriculation,
max(CASE WHEN cr.position_asc = 1 THEN cr.KM END) AS KM_debut,
max(CASE WHEN cr.position_asc = 1 THEN cr.date_cr END) AS date_debut,
max(CASE WHEN cr.position_desc = 1 THEN cr.KM END) AS KM_fin,
max(CASE WHEN cr.position_desc = 1 THEN cr.date_cr END) AS date_fin
from cr
where (position_asc = 1 or position_desc = 1)
group by cr.mois, cr.annee, cr.immatriculation |
Partager