应付发票期间付款明细

代码示例

SELECT T0.[CardCode], T0.[CardName], T1.[SlpName], B2.QC AS '期初应付余额', T0.[Docentry] AS '应付发票编号'
	, T0.[DocDate] AS '发票日期', T0.[DocTotal] AS '开票金额',  B1.DocTotal AS '付款金额'
	, ISNULL(B2.QC, 0) + ISNULL(B3.QJFP, 0) - ISNULL(B1.DocTotal, 0) AS '期末应付余额'
FROM OPCH T0
	INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
	LEFT JOIN (
		SELECT T1.baseAbs, SUM(T1.SumApplied) AS DocTotal
		FROM OVPM T0
			INNER JOIN VPM2 T1 ON T1.DocNum = T0.DocEntry
		WHERE T1.InvType = '18'
			AND T0.[DocDate] >= [%0]
			AND T0.[DocDate] <= [%1]
		GROUP BY T1.baseAbs
	) B1
	ON B1.baseAbs = T0.DocEntry
	LEFT JOIN (
		SELECT T0.[CardCode]
			, ISNULL(SUM(T0.[DocTotal]), 0) - ISNULL(SUM(T1.FK), 0) AS 'QC'
		FROM OPCH T0
			LEFT JOIN (
				SELECT T0.[CardCode], T0.[DocTotal] AS 'FK'
				FROM OVPM T0
				WHERE T0.[DocDate] < [%0]
			) T1
			ON T1.[CardCode] = T0.[CardCode]
		WHERE T0.[DocDate] < [%0]
		GROUP BY T0.[CardCode]
	) B2
	ON B2.[CardCode] = T0.[CardCode]
	LEFT JOIN (
		SELECT T0.[CardCode], SUM(T0.[DocTotal]) AS 'QJFP'
		FROM OPCH T0
		WHERE T0.[TaxDate] >= [%0]
			AND T0.[TaxDate] <= [%1]
		GROUP BY T0.[CardCode]
	) B3
	ON B3.[CardCode] = T0.[CardCode]
WHERE T0.[TaxDate] >= [%0]
	AND T0.[TaxDate] <= [%1]