应付发票：按供应商汇总统计每月应收发票金额

提示

--报表：按供应商默认付款条件天数汇总统计每月应收发票金额 --日期汇总条件公式：应付发票日期+默认付款条件日期天数

代码示例

SELECT
 T0.[CardCode] , T0.[CardName],
'2020.09月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202009' THEN T0.[DocTotal] ELSE 0 END),
'2020.10月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202010' THEN T0.[DocTotal] ELSE 0 END),
'2020.11月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202011' THEN T0.[DocTotal] ELSE 0 END),
'2020.12月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202012' THEN T0.[DocTotal] ELSE 0 END),
'2021.01月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202101' THEN T0.[DocTotal] ELSE 0 END),
'2021.02月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202102' THEN T0.[DocTotal] ELSE 0 END),
'2021.03月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202103' THEN T0.[DocTotal] ELSE 0 END),
'2021.04月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202104' THEN T0.[DocTotal] ELSE 0 END),
'2021.05月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202105' THEN T0.[DocTotal] ELSE 0 END),
'2021.06月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202106' THEN T0.[DocTotal] ELSE 0 END),
'2021.07月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202107' THEN T0.[DocTotal] ELSE 0 END),
'2021.08月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202108' THEN T0.[DocTotal] ELSE 0 END),
'2021.09月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202109' THEN T0.[DocTotal] ELSE 0 END),
'2021.10月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202110' THEN T0.[DocTotal] ELSE 0 END),
'2021.11月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202111' THEN T0.[DocTotal] ELSE 0 END),
'2021.12月'=SUM(Case convert(nvarchar(6),t0.docdate +CAST(Ts.[ExtraDays] AS NUMERIC ),112)  WHEN '202112' THEN T0.[DocTotal] ELSE 0 END)

FROM OPCH T0
LEFT JOIN 
(
SELECT T0.[CardCode], T1.[ExtraDays] FROM OCRD T0  INNER JOIN OCTG T1 ON T0.[GroupNum] = T1.[GroupNum]
) TS ON TS.[CardCode] =T0.[CardCode]

GROUP BY
 T0.[CardCode], T0.[CardName]
ORDER BY
T0.[CardName]

效果图

销售