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
| select FAR_CODE, ART_CODE, ART_LIB, 0 AS NBR_JOUR,
(SELECT SUM(MVT_SORTIE) AS Expr1
FROM dbo.ART_MVT
WHERE (DOC_DATE BETWEEN '31/12/2018'
AND '31/12/2019') AND (ART_CODE = articles.ART_CODE)) AS
QTE
from articles where (FAR_CODE BETWEEN 'L' AND 'L')
union all
SELECT
FAR_CODE, ART_CODE , ART_LIB
, SUM(NbJours) AS NBR_JOUR ,0 AS QTE
FROM (SELECT
ART_MVT.ART_CODE, ART_LIB,
FAR_CODE
, DATEDIFF(
DAY
, DOC_DATE
, COALESCE(LEAD
(DOC_DATE) OVER(PARTITION BY ART_MVT.ART_CODE
ORDER BY DOC_DATE),GETDATE())
) AS NbJours
, MVT_STOCK
FROM dbo.ART_MVT LEFT OUTER JOIN
dbo.ARTICLES AS ART ON ART_MVT.ART_CODE =
ART.ART_CODE
WHERE DOC_DATE>'31/12/2018') AS tmp
WHERE MVT_STOCK = 0 AND FAR_CODE BETWEEN 'L' AND 'L'
GROUP BY ART_CODE, ART_LIB , FAR_CODE
ORDER BY ART_CODE |
Partager