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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
| SELECT DISTINCT
ITM.ITMREF_0 as 'Ref article',
MV1.YEAISSAMT_0 as 'Mnt OUT N-1',
MV1.YEAISSMVT_0 as 'Mvt OUT N-1',
MV1.YEAISSQTY_0 as 'Qté OUT N-1',
MV1.YEARCPAMT_0 as 'Mnt IN N-1',
MV1.YEARCPMVT_0 as 'Mvt IN N-1',
MV1.YEARCPQTY_0 as 'Qté IN N-1',
MV2.CUMISSAMT_0 as 'Mnt OUT 7 mois',
MV2.CUMISSMVT_0 as 'Mvt OUT 7 mois',
MV2.CUMISSQTY_0 as 'Qté OUT 7 mois',
MV2.CUMRCPAMT_0 as 'Mnt IN 7 mois',
MV2.CUMRCPMVT_0 as 'Mvt IN 7 mois',
MV2.CUMRCPQTY_0 as 'Qté IN 7 mois',
MV3.CUMISSAMT_0 as 'Mnt OUT mois N',
MV3.CUMISSMVT_0 as 'Mvt OUT mois N',
MV3.CUMISSQTY_0 as 'Qté OUT mois N',
MV3.CUMRCPAMT_0 as 'Mnt IN mois N',
MV3.CUMRCPMVT_0 as 'Mvt IN mois N',
MV3.CUMRCPQTY_0 as 'Qté IN mois N',
MV4.YEAISSAMT_0 as 'Mnt OUT N',
MV4.YEAISSMVT_0 as 'Mvt OUT N',
MV4.YEAISSQTY_0 as 'Qté OUT N',
MV4.YEARCPAMT_0 as 'Mnt IN N',
MV4.YEARCPMVT_0 as 'Mnt IN N',
MV4.YEARCPQTY_0 as 'Qté IN N'
FROM
ITMMASTER ITM
LEFT OUTER JOIN (SELECT
A.ITMREF_0,
A.FIYNUM_0,
A.PERNUM_0,
A.YEAISSAMT_0,
A.YEAISSMVT_0,
A.YEAISSQTY_0,
A.YEARCPAMT_0,
A.YEARCPMVT_0,
A.YEARCPQTY_0
FROM
ITMMVTHIS A) MV1 ON
MV1.ITMREF_0 = ITM.ITMREF_0 AND
MV1.FIYNUM_0 = 4 AND
MV1.PERNUM_0 = 12
LEFT OUTER JOIN ( SELECT DISTINCT
A.ITMREF_0,
A.FIYNUM_0,
A.PERNUM_0,
SUM(A.MONISSAMT_0) OVER (PARTITION BY A.ITMREF_0) AS CUMISSAMT_0,
SUM(A.MONISSMVT_0) OVER (PARTITION BY A.ITMREF_0) AS CUMISSMVT_0,
SUM(A.MONISSQTY_0) OVER (PARTITION BY A.ITMREF_0) AS CUMISSQTY_0,
SUM(A.MONRCPAMT_0) OVER (PARTITION BY A.ITMREF_0) AS CUMRCPAMT_0,
SUM(A.MONRCPMVT_0) OVER (PARTITION BY A.ITMREF_0) AS CUMRCPMVT_0,
SUM(A.MONRCPQTY_0) OVER (PARTITION BY A.ITMREF_0) AS CUMRCPQTY_0
FROM ITMMVTHIS A ) MV2 ON
MV2.ITMREF_0=ITM.ITMREF_0
LEFT OUTER JOIN ( SELECT DISTINCT
A.ITMREF_0,
A.FIYNUM_0,
A.PERNUM_0,
SUM(A.MONISSAMT_0) OVER (PARTITION BY A.ITMREF_0) AS CUMISSAMT_0,
SUM(A.MONISSMVT_0) OVER (PARTITION BY A.ITMREF_0) AS CUMISSMVT_0,
SUM(A.MONISSQTY_0) OVER (PARTITION BY A.ITMREF_0) AS CUMISSQTY_0,
SUM(A.MONRCPAMT_0) OVER (PARTITION BY A.ITMREF_0) AS CUMRCPAMT_0,
SUM(A.MONRCPMVT_0) OVER (PARTITION BY A.ITMREF_0) AS CUMRCPMVT_0,
SUM(A.MONRCPQTY_0) OVER (PARTITION BY A.ITMREF_0) AS CUMRCPQTY_0
FROM ITMMVTHIS A
WHERE
A.FIYNUM_0=5 AND
A.PERNUM_0=1 ) MV3 ON
MV3.ITMREF_0=ITM.ITMREF_0
LEFT OUTER JOIN (SELECT
A.ITMREF_0,
A.FIYNUM_0,
A.PERNUM_0,
A.YEAISSAMT_0,
A.YEAISSMVT_0,
A.YEAISSQTY_0,
A.YEARCPAMT_0,
A.YEARCPMVT_0,
A.YEARCPQTY_0
FROM
ITMMVTHIS A) MV4 ON
MV4.ITMREF_0 = ITM.ITMREF_0 AND
MV4.FIYNUM_0 = 5 AND
MV4.PERNUM_0 = 2 |
Partager