1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
|
create or replace view MFT_AR_FACTURE_INFO_PROMO
(YRES_ID,SHORTDESC,MONTANT,NBR_JOUR,DU,AU)
as
SELECT MFT_AR_PROMO_YRES YRES_ID,
MFT_AR_PROMO_SHORTDESC SHORTDESC,
max(MFT_AR_PROMO_MOUNT) as MONTANT,
count(*) AS Nbr_jours NBR_JOUR,
min(MFT_AR_PROMO_DATE) AS Du,
max(MFT_AR_PROMO_DATE) AS Au
FROM
(SELECT MFT_AR_PROMO_YRES,MFT_AR_PROMO_SHORTDESC,MFT_AR_PROMO_MOUNT,MFT_AR_PROMO_DATE,
sum(dif) OVER (PARTITION BY MFT_AR_PROMO_YRES ORDER BY MFT_AR_PROMO_DATE) AS plage
FROM
(SELECT MFT_AR_PROMO_YRES,MFT_AR_PROMO_SHORTDESC,MFT_AR_PROMO_MOUNT,MFT_AR_PROMO_DATE,
coalesce(abs(MFT_AR_PROMO_MOUNT-lag(MFT_AR_PROMO_MOUNT) over (PARTITION BY MFT_AR_PROMO_YRES ORDER BY MFT_AR_PROMO_DATE)),0) AS dif
FROM MFT_AR_PROMO ))
GROUP BY MFT_AR_PROMO_YRES,MFT_AR_PROMO_SHORTDESC,plage ORDER BY MFT_AR_PROMO_YRES, Du
/ |
Partager