1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| SELECT a.date_transaction, a.nomCaissier, b.montant_depot, a.montant_ventes
FROM
(SELECT TO_CHAR(t.date_transaction,'DD/MM/YYYY') date_transaction,
u.nom || ', ' || u.prenom nomCaissier, t.usr_id,
SUM(t.prix) montant_ventes
FROM SGV_TAB_TRANSACTIONS t, SGV_TAB_USAGERS u
WHERE u.ID = t.USR_ID
AND t.date_transaction >= TO_DATE('30-04-2007 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
AND t.date_transaction <= TO_DATE('30-04-2007 23:59:59', 'DD/MM/YYYY HH24:MI:SS') AND t.usr_id = '392'
GROUP BY TO_CHAR(t.date_transaction,'DD/MM/YYYY'), u.nom || ', ' || u.prenom, t.usr_id
) a --j'ai enlevé la virgule
FULL OUTER JOIN
(SELECT TO_CHAR(mc.date_transaction,'DD/MM/YYYY') DATE_TRANSACTION,
u.nom || ', ' || u.prenom nomCaissier, mc.usr_id,
SUM(mc.montant_depot) montant_depot
FROM SGV_TAB_MONTANT_CAISSE mc, SGV_TAB_USAGERS u
WHERE u.ID = mc.USR_ID
AND mc.date_transaction >= TO_DATE('30-04-2007 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
AND mc.date_transaction <= TO_DATE('30-04-2007 23:59:59', 'DD/MM/YYYY HH24:MI:SS') AND mc.usr_id = '392'
GROUP BY TO_CHAR(mc.date_transaction,'DD/MM/YYYY'), u.nom || ', ' || u.prenom, mc.usr_id
) b
ON a.date_transaction = b.date_transaction
AND a.usr_id = b.usr_id |
Partager