1 2 3 4 5 6
| SELECT isin, numPF, (SELECT SUM(quantite) FROM Ordre o1 WHERE o1.isin = o.isin AND o1.numTO = 1 AND o1.numPF = o.numPF) as Sous, (SELECT SUM(quantiteInit) FROM Init i WHERE i.isin = o.isin AND i.pf = o.numpf) as init, (SELECT SUM(quantite) FROM Ordre o2 WHERE o2.isin = o.isin AND o2.numTO = 2 AND o2.numPF = o.numPF) as Rachats, (IIF(isnull(Sous), 0, Sous) + IIF(isnull(init), 0, init) - IIF(isnull(rachats), 0, rachats)) as 'Quantité détenue'
FROM Ordre AS o
WHERE o.isin NOT IN (SELECT isin FROM ValeurLiquidative WHERE format([dateFichier], "mm-yyyy") = format(CDATE("01/" & [Mois ?] & "/" & [Année?]), "mm-yyyy"))
AND dateReglement <= CDATE("01-" & [Mois ?] & "-" & [Année?])
GROUP BY isin, numPF
HAVING (IIF(isnull((SELECT SUM(quantite) FROM Ordre o1 WHERE o1.isin = o.isin AND o1.numTO = 1 AND o1.numPF = o.numPF)), 0, (SELECT SUM(quantite) FROM Ordre o1 WHERE o1.isin = o.isin AND o1.numTO = 1 AND o1.numPF = o.numPF)) + IIF(isnull((SELECT SUM(quantiteInit) FROM Init i WHERE i.isin = o.isin AND i.pf = o.numpf)), 0, (SELECT SUM(quantiteInit) FROM Init i WHERE i.isin = o.isin AND i.pf = o.numpf)) - IIF(isnull((SELECT SUM(quantite) FROM Ordre o2 WHERE o2.isin = o.isin AND o2.numTO = 2 AND o2.numPF = o.numPF)), 0, (SELECT SUM(quantite) FROM Ordre o2 WHERE o2.isin = o.isin AND o2.numTO = 2 AND o2.numPF = o.numPF))) |
Partager