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 111 112 113 114 115
|
SELECT
SUM(aTM) ATM, SUM(aM3) AM3, SUM(aFret) AFret,
SUM(lTM) LTM, SUM(lM3) LM3, SUM(lFret) LFret,
SUM(rTM) RTM, SUM(rM3) RM3, SUM(rFret) RFret,
SUM(tTM) TTM, SUM(tM3) TM3, SUM(tFret) TFret
FROM
-- ALLER --
(SELECT SUM(masse)*0.001 aTM, SUM(cube) aM3, SUM(masse)*0.001 aFret
FROM manifcolis mc, manifrecus mr, voyages vo, contrats ct, compagnies ci
WHERE mc.manifrecuid = mr.manifrecuid
AND mc.voyaid = vo.voyaid
AND mr.contid = ct.contid
AND ct.cieid = ci.compid
AND vo.voyano = :voyano
AND ci.compid = :compid
AND mr.origid = '1'
AND mr.destid <> '1'
AND masse >= cube * 400
UNION
SELECT SUM(masse)*0.001 aTM, SUM(cube) aM3, SUM(cube/2.5) aFret
FROM manifcolis mc, manifrecus mr, voyages vo, contrats ct, compagnies ci
WHERE mc.manifrecuid = mr.manifrecuid
AND mc.voyaid = vo.voyaid
AND mr.contid = ct.contid
AND ct.cieid = ci.compid
AND vo.voyano = :voyano
AND ci.compid = :compid
AND mr.origid = '1'
AND mr.destid <> '1'
AND masse < cube * 400) AS ATMM3Fret,
-- LATERAL --
(SELECT SUM(masse)*0.001 lTM, SUM(cube) lM3, SUM(masse)*0.001 lFret
FROM manifcolis mc, manifrecus mr, voyages vo, contrats ct, compagnies ci
WHERE mc.manifrecuid = mr.manifrecuid
AND mc.voyaid = vo.voyaid
AND mr.contid = ct.contid
AND ct.cieid = ci.compid
AND vo.voyano = :voyano
AND ci.compid = :compid
AND mr.origid <> '1'
AND mr.destid <> '1'
AND masse >= cube * 400
UNION
SELECT SUM(masse)*0.001 lTM, SUM(cube) lM3, SUM(cube/2.5) lFret
FROM manifcolis mc, manifrecus mr, voyages vo, contrats ct, compagnies ci
WHERE mc.manifrecuid = mr.manifrecuid
AND mc.voyaid = vo.voyaid
AND mr.contid = ct.contid
AND ct.cieid = ci.compid
AND vo.voyano = :voyano
AND ci.compid = :compid
AND mr.origid <> '1'
AND mr.destid <> '1'
AND masse < cube * 400) AS LTMM3Fret,
-- RETRO --
(SELECT SUM(masse)*0.001 rTM, SUM(cube) rM3, SUM(masse)*0.001 rFret
FROM manifcolis mc, manifrecus mr, voyages vo, contrats ct, compagnies ci
WHERE mc.manifrecuid = mr.manifrecuid
AND mc.voyaid = vo.voyaid
AND mr.contid = ct.contid
AND ct.cieid = ci.compid
AND vo.voyano = :voyano
AND ci.compid = :compid
AND mr.origid <> '1'
AND mr.destid = '1'
AND masse >= cube * 400
UNION
SELECT SUM(masse)*0.001 rTM, SUM(cube) rM3, SUM(cube/2.5) rFret
FROM manifcolis mc, manifrecus mr, voyages vo, contrats ct, compagnies ci
WHERE mc.manifrecuid = mr.manifrecuid
AND mc.voyaid = vo.voyaid
AND mr.contid = ct.contid
AND ct.cieid = ci.compid
AND vo.voyano = :voyano
AND ci.compid = :compid
AND mr.origid <> '1'
AND mr.destid = '1'
AND masse < cube * 400) AS RTMM3Fret,
-- TOTAL --
(SELECT SUM(masse)*0.001 tTM, SUM(cube) tM3, SUM(masse)*0.001 tFret
FROM manifcolis mc, manifrecus mr, voyages vo, contrats ct, compagnies ci
WHERE mc.manifrecuid = mr.manifrecuid
AND mc.voyaid = vo.voyaid
AND mr.contid = ct.contid
AND ct.cieid = ci.compid
AND vo.voyano = :voyano
AND ci.compid = :compid
AND masse >= cube * 400
UNION
SELECT SUM(masse)*0.001 tTM, SUM(cube) tM3, SUM(cube/2.5) tFret
FROM manifcolis mc, manifrecus mr, voyages vo, contrats ct, compagnies ci
WHERE mc.manifrecuid = mr.manifrecuid
AND mc.voyaid = vo.voyaid
AND mr.contid = ct.contid
AND ct.cieid = ci.compid
AND vo.voyano = :voyano
AND ci.compid = :compid
AND masse < cube * 400) AS TTMM3Fret |
Partager