应收发票期间收款明细
小于 1 分钟
代码示例
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 OINV T0
INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
LEFT JOIN (
SELECT T1.baseAbs, SUM(T1.SumApplied) AS DocTotal
FROM ORCT T0
INNER JOIN RCT2 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 OINV T0
LEFT JOIN (
SELECT T0.[CardCode], T0.[DocTotal] AS 'FK'
FROM ORCT 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 OINV 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]