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
| SELECT TOP (100) PERCENT dbo.OITM.ItemCode AS Article, dbo.OITB.ItmsGrpNam AS [Groupe Article], dbo.OITM.LstEvlPric AS 'Dernier prix déterminé',
SI.InitStock AS 'Stock Initial', ESDAD.SumIn AS 'Entrées', MVF.SumInF AS 'Livraisons Fournisseurs', ESDAD.SumOut AS 'Sorties',
MVF.SumOutF AS 'Retours Fournisseurs', SF.FinalStock AS 'Stock Final'
FROM dbo.OITM INNER JOIN
dbo.OITB ON dbo.OITB.ItmsGrpCod = dbo.OITM.ItmsGrpCod LEFT OUTER JOIN
(SELECT TOP (100) PERCENT ItemCode, SUM(InQty) - SUM(OutQty) AS InitStock
FROM dbo.ES AS ESI
WHERE (ItmsGrpCod = '110') AND (DocDate < '01.04.2013')
GROUP BY ItemCode) AS SI ON dbo.OITM.ItemCode = SI.ItemCode LEFT OUTER JOIN
(SELECT TOP (100) PERCENT ItemCode, SUM(InQty) AS SumIn, SUM(OutQty) AS SumOut
FROM dbo.ES
WHERE (ItmsGrpCod = '110') AND (DocDate BETWEEN '01.04.2013' AND '30.09.2013')
GROUP BY ItemCode) AS ESDAD ON dbo.OITM.ItemCode = ESDAD.ItemCode LEFT OUTER JOIN
(SELECT TOP (100) PERCENT ItemCode, SUM(InQty) AS SumInF, SUM(OutQty) AS SumOutF
FROM dbo.ES AS ESF
WHERE (ItmsGrpCod = '110') AND (DocDate BETWEEN '01.04.2013' AND '30.09.2013') AND (TransType = '18' OR
TransType = '19' OR
TransType = '20' OR
TransType = '21')
GROUP BY ItemCode) AS MVF ON dbo.OITM.ItemCode = MVF.ItemCode INNER JOIN
(SELECT TOP (100) PERCENT ItemCode, SUM(InQty) - SUM(OutQty) AS FinalStock
FROM dbo.ES AS ESF
WHERE (ItmsGrpCod = '110') AND (DocDate < '30.09.2013')
GROUP BY ItemCode) AS SF ON dbo.OITM.ItemCode = SF.ItemCode
ORDER BY Article |
Partager