--报表：按供应商默认付款条件天数汇总统计每月应收发票金额
--日期汇总条件公式：应付发票日期+默认付款条件日期天数
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]
效果
声明：欢迎来到五六零学院。
