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 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
|
;WITH RES
AS (SELECT T012_11.FAM + ' - ' + T012_11.LIB AS col10,
T012_12.FAM + ' - ' + T012_12.LIB AS col11,
ART.REFUN AS col12,
Sum(CASE
WHEN Year(PIDT) = 2009
THEN TOTALCA * SENS2
ELSE 0
END) AS col14,
Grouping(T012_11.FAM + ' - ' + T012_11.LIB) AS col30,
Grouping(T012_12.FAM + ' - ' + T012_12.LIB) AS col31,
Grouping(ART.REFUN) AS col32
FROM (SELECT MOUV.DOS,
ENT.PIDT,
MOUV.TIERS,
MOUV.REF,
MOUV.OP,
MOUV.DEPO,
ENT.REPR_0001,
0 AS TOTALQTE,
MOUV.PUSTAT * MOUV.REFQTE AS TOTALCA,
0 AS TOTALMARGE1,
0 AS TOTALMARGE2,
0 AS TOTALMARGE3,
(CASE
WHEN T020.SENS = 1
THEN -1
ELSE 1
END) AS SENS2,
ENT.PINO AS PIECE
FROM MOUV
JOIN ENT
ON ENT.DOS = MOUV.DOS
AND ENT.TICOD = 'C'
AND ENT.PICOD = 4
AND ENT.CE4 = 1
AND ENT.PINO = MOUV.FANO
JOIN T020
ON T020.DOS = 999
AND T020.CEBIN = 20
AND T020.OP = MOUV.OP
JOIN CLI
ON ENT.DOS = CLI.DOS
AND ENT.TIERS = CLI.TIERS
AND CLI.CE1 = 3
WHERE ((ENT.PIDT >= { d '2009-01-01'}
AND ENT.PIDT <= {d '2009-12-31'}))
AND (ENT.DOS = 50
AND ENT.CE4 = '1'
AND ENT.TICOD = 'C'
AND ENT.PICOD = 4
AND MOUV.FACE4 = 1
AND CLI.TIERS NOT IN ('C0200901','C0000172','C0200903','C0200904',
'C0200920'))) AS BASE
LEFT JOIN ART
ON ART.DOS = BASE.DOS
AND ART.REF = BASE.REF
LEFT JOIN T012 AS T012_11
ON T012_11.DOS = BASE.DOS
AND T012_11.CEBIN = 12
AND T012_11.FAMNO = 1
AND T012_11.FAM = Left(ART.FAM_0001,1)
LEFT JOIN T012 AS T012_12
ON T012_12.DOS = BASE.DOS
AND T012_12.CEBIN = 12
AND T012_12.FAMNO = 1
AND T012_12.FAM = Left(ART.FAM_0001,3)
GROUP BY T012_11.FAM + ' - ' + T012_11.LIB,
T012_12.FAM + ' - ' + T012_12.LIB,
ART.REFUN WITH ROLLUP)
SELECT *
FROM RES
ORDER BY col30,
Max(CASE
WHEN col32 = 1
AND col31 = 1
AND col30 = 0
THEN col14
END)
OVER(PARTITION BY col10 ) ASC,
CASE
WHEN col32 = 1
AND col31 = 1
AND col30 = 0
THEN 0
ELSE 1
END DESC,
Max(CASE
WHEN col32 = 1
AND col31 = 0
AND col30 = 0
THEN col14
END)
OVER(PARTITION BY col11 ) ASC,
CASE
WHEN col32 = 1
AND col31 = 0
AND col30 = 0
THEN 0
ELSE 1
END DESC,
CASE
WHEN col32 = 0
AND col31 = 0
AND col30 = 0
THEN 0
ELSE 1
END DESC,
col14 ASC |
Partager