1 2 3 4 5 6 7 8 9 10 11 12 13 14
|
SELECT c.CLI_CODCLI AS CodeClient, c.REP_CODE AS Cial, DATEPART(MM, GETDATE()) AS moisfac, DATEPART(yy, GETDATE()) AS anneefac,
SUM(CASE WHEN COALESCE (l.alvinvoicetyp, '') = 'FA' THEN l.nlvtotnet WHEN COALESCE (l.alvinvoicetyp, '') = 'AV' THEN - l.nlvtotnet ELSE 0 END)
AS MontantFactures, SUM(CASE WHEN COALESCE (l.alvblivtyp, '') = 'LC' AND l.alvinvoicetyp IS NULL THEN l.nlvtotnet WHEN COALESCE (l.alvblivtyp,
'') = 'RC' AND l.alvinvoicetyp IS NULL THEN - l.nlvtotnet ELSE 0 END) AS MontantBL, SUM(CASE WHEN COALESCE (l.alvordertyp, '') = 'CC' AND
l.alvblivtyp IS NULL AND l.alvinvoicetyp IS NULL THEN l.nlvtotnet ELSE 0 END) AS MontantCde, SUM(CASE WHEN COALESCE (l.alvinvoicetyp, '')
= 'FA' THEN l.nlvtotnet WHEN COALESCE (l.alvinvoicetyp, '') = 'AV' THEN - l.nlvtotnet ELSE 0 END) + SUM(CASE WHEN COALESCE (l.alvblivtyp, '')
= 'LC' AND l.alvinvoicetyp IS NULL THEN l.nlvtotnet WHEN COALESCE (l.alvblivtyp, '') = 'RC' AND l.alvinvoicetyp IS NULL
THEN - l.nlvtotnet ELSE 0 END) + SUM(CASE WHEN COALESCE (l.alvordertyp, '') = 'CC' AND l.alvblivtyp IS NULL AND l.alvinvoicetyp IS NULL
THEN l.nlvtotnet ELSE 0 END) AS TotGénéral
FROM dbo.CLIENT AS c WITH (nolock) LEFT OUTER JOIN
dbo.LIG_VTE AS l WITH (nolock) ON c.CLI_CODCLI = l.ALVCUSTNO AND l.ALVITEMTYP IN ('A', 'R', 'F', 'P') AND (l.ALVINVOICETYP IN ('FA', 'AV') AND
DATEPART(MM, l.DLVINVOICEDAT) = DATEPART(MM, GETDATE()) AND DATEPART(yy, l.DLVINVOICEDAT) = DATEPART(yy, GETDATE()) OR
COALESCE (l.ALVINVOICETYP, '') = '')GROUP BY c.CLI_CODCLI, c.REP_CODE |
Partager