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
| PARAMETERS année Short, mois Short;
SELECT TFacture.datefacture AS dateCA, Sum([volume]*[PU]) AS ValeurCA
FROM TFacture
WHERE (((Year([datefacture]))=[année]) AND ((Month([datefacture]))=[mois]))
GROUP BY TFacture.datefacture
UNION
SELECT dateserial([année],[mois],1) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],1) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],2) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],2) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],3) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],3) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],4) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],4) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],5) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],5) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],6) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],6) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],7) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],7) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],8) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],8) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],9) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],9) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],10) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],10) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],11) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],11) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],12) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],12) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],13) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],13) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],14) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],14) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],15) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],15) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],16) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],16) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],17) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],17) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],18) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],18) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],19) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],19) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],20) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],20) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],21) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],21) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],22) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],22) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],23) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],23) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],24) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],24) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],25) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],25) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],26) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],26) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],27) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],27) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],28) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],28) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],29) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],29) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois]) AND Year(dateserial([année],[mois],29))=[année] AND Month(dateserial([année],[mois],29))=[mois]
UNION
SELECT dateserial([année],[mois],30) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],30) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois]) AND Year(dateserial([année],[mois],30))=[année] AND Month(dateserial([année],[mois],30))=[mois]
UNION
SELECT dateserial([année],[mois],31) AS dateCA, 0 AS valeurCa FROM TFacture WHERE dateserial([année],[mois],31) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois]) AND Year(dateserial([année],[mois],31))=[année] AND Month(dateserial([année],[mois],31))=[mois]; |