-- =============================================
-- BI财务报表核心指标整合输出（2025年度 | 冷柜中小业态）
-- 已修复：GROUP BY 常量字符串错误
-- 使用聚合后直接添加科目名称字段，避免非法 GROUP BY
-- =============================================
WITH
-- ================== 营业收入 ==================
RevenueData AS (
SELECT
'营业收入' AS 科目名称,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 1 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 2 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 二月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 3 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 三月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 4 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 四月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 5 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 五月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 6 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 六月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 7 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 七月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 8 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 八月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 9 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 九月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 10 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 11 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(rec.FDATE) = 12 THEN entry.FNOTAXAMOUNT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十二月,
CAST(ISNULL(SUM(entry.FNOTAXAMOUNT), 0) AS DECIMAL(18,3)) AS 合计
FROM T_AR_RECEIVABLE rec
INNER JOIN T_AR_RECEIVABLEENTRY entry ON rec.FID = entry.FID
INNER JOIN T_BD_DEPARTMENT dept ON rec.FSALEDEPTID = dept.FDEPTID
INNER JOIN T_BD_DEPARTMENT_L dept_l ON dept.FDEPTID = dept_l.FDEPTID AND dept_l.FLOCALEID = 2052
WHERE dept_l.FNAME = '冷柜中小业态'
AND YEAR(rec.FDATE) = 2025
),
-- ================== 成本项目 ==================
-- 生产制造成本 (6401.01/6401.05)
ProductionCost AS (
SELECT
'生产制造成本' AS 科目名称,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 1 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 2 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 二月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 3 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 三月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 4 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 四月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 5 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 五月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 6 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 六月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 7 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 七月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 8 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 八月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 9 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 九月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 10 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 11 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 12 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十二月,
CAST(ISNULL(SUM(tgve.FDEBIT), 0) AS DECIMAL(18,3)) AS 合计
FROM T_GL_VOUCHER tgv
LEFT JOIN T_GL_VOUCHERENTRY tgve ON tgv.FVOUCHERID = tgve.FVOUCHERID
LEFT JOIN T_BD_FLEXITEMDETAILV tbf ON tgve.FDETAILID = tbf.FID
LEFT JOIN T_BD_ACCOUNT tba ON tgve.FACCOUNTID = tba.FACCTID AND tba.FUSEORGID = '101041'
LEFT JOIN (
SELECT tbd.FMASTERID FROM T_BD_DEPARTMENT tbd
JOIN T_BD_DEPARTMENT_L tbdl ON tbd.FDEPTID = tbdl.FDEPTID
WHERE tbdl.FNAME = '冷柜中小业态' AND tbd.FUSEORGID = '101041'
) dept_info ON tbf.FFLEX5 = dept_info.FMASTERID
WHERE tgv.FACCTORGID = '101041'
AND tgv.FYEAR = 2025
AND tba.FNUMBER IN ('6401.01', '6401.05')
AND dept_info.FMASTERID IS NOT NULL
-- ✅ 移除了 GROUP BY '生产制造成本' 非法语句
-- 因为整个子查询只有一个结果行，无需分组
),
-- 物流运费 (6401.03)
LogisticsCost AS (
SELECT
'物流运费' AS 科目名称,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 1 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 2 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 二月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 3 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 三月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 4 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 四月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 5 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 五月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 6 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 六月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 7 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 七月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 8 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 八月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 9 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 九月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 10 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 11 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 12 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十二月,
CAST(ISNULL(SUM(tgve.FDEBIT), 0) AS DECIMAL(18,3)) AS 合计
FROM T_GL_VOUCHER tgv
LEFT JOIN T_GL_VOUCHERENTRY tgve ON tgv.FVOUCHERID = tgve.FVOUCHERID
LEFT JOIN T_BD_FLEXITEMDETAILV tbf ON tgve.FDETAILID = tbf.FID
LEFT JOIN T_BD_ACCOUNT tba ON tgve.FACCOUNTID = tba.FACCTID AND tba.FUSEORGID = '101041'
LEFT JOIN (
SELECT tbd.FMASTERID FROM T_BD_DEPARTMENT tbd
JOIN T_BD_DEPARTMENT_L tbdl ON tbd.FDEPTID = tbdl.FDEPTID
WHERE tbdl.FNAME = '冷柜中小业态' AND tbd.FUSEORGID = '101041'
) dept_info ON tbf.FFLEX5 = dept_info.FMASTERID
WHERE tgv.FACCTORGID = '101041'
AND tgv.FYEAR = 2025
AND tba.FNUMBER = '6401.03'
AND dept_info.FMASTERID IS NOT NULL
),
-- 安装费 (6401.04)
InstallationCost AS (
SELECT
'安装费' AS 科目名称,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 1 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 2 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 二月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 3 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 三月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 4 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 四月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 5 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 五月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 6 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 六月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 7 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 七月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 8 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 八月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 9 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 九月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 10 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 11 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 12 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十二月,
CAST(ISNULL(SUM(tgve.FDEBIT), 0) AS DECIMAL(18,3)) AS 合计
FROM T_GL_VOUCHER tgv
LEFT JOIN T_GL_VOUCHERENTRY tgve ON tgv.FVOUCHERID = tgve.FVOUCHERID
LEFT JOIN T_BD_FLEXITEMDETAILV tbf ON tgve.FDETAILID = tbf.FID
LEFT JOIN T_BD_ACCOUNT tba ON tgve.FACCOUNTID = tba.FACCTID AND tba.FUSEORGID = '101041'
LEFT JOIN (
SELECT tbd.FMASTERID FROM T_BD_DEPARTMENT tbd
JOIN T_BD_DEPARTMENT_L tbdl ON tbd.FDEPTID = tbdl.FDEPTID
WHERE tbdl.FNAME = '冷柜中小业态' AND tbd.FUSEORGID = '101041'
) dept_info ON tbf.FFLEX5 = dept_info.FMASTERID
WHERE tgv.FACCTORGID = '101041'
AND tgv.FYEAR = 2025
AND tba.FNUMBER = '6401.04'
AND dept_info.FMASTERID IS NOT NULL
),
-- 成本合计
CostTotal AS (
SELECT
'成本合计' AS 科目名称,
p.一月 + l.一月 + i.一月 AS 一月,
p.二月 + l.二月 + i.二月 AS 二月,
p.三月 + l.三月 + i.三月 AS 三月,
p.四月 + l.四月 + i.四月 AS 四月,
p.五月 + l.五月 + i.五月 AS 五月,
p.六月 + l.六月 + i.六月 AS 六月,
p.七月 + l.七月 + i.七月 AS 七月,
p.八月 + l.八月 + i.八月 AS 八月,
p.九月 + l.九月 + i.九月 AS 九月,
p.十月 + l.十月 + i.十月 AS 十月,
p.十一月 + l.十一月 + i.十一月 AS 十一月,
p.十二月 + l.十二月 + i.十二月 AS 十二月,
p.合计 + l.合计 + i.合计 AS 合计
FROM ProductionCost p
CROSS JOIN LogisticsCost l
CROSS JOIN InstallationCost i
),
-- 毛利
GrossProfit AS (
SELECT
'毛利' AS 科目名称,
r.一月 - c.一月 AS 一月,
r.二月 - c.二月 AS 二月,
r.三月 - c.三月 AS 三月,
r.四月 - c.四月 AS 四月,
r.五月 - c.五月 AS 五月,
r.六月 - c.六月 AS 六月,
r.七月 - c.七月 AS 七月,
r.八月 - c.八月 AS 八月,
r.九月 - c.九月 AS 九月,
r.十月 - c.十月 AS 十月,
r.十一月 - c.十一月 AS 十一月,
r.十二月 - c.十二月 AS 十二月,
r.合计 - c.合计 AS 合计
FROM RevenueData r
CROSS JOIN CostTotal c
),
-- 毛利率 (%)
GrossMargin AS (
SELECT
'毛利率' AS 科目名称,
CASE WHEN r.一月 > 0 THEN CAST((gp.一月 / r.一月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 一月,
CASE WHEN r.二月 > 0 THEN CAST((gp.二月 / r.二月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 二月,
CASE WHEN r.三月 > 0 THEN CAST((gp.三月 / r.三月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 三月,
CASE WHEN r.四月 > 0 THEN CAST((gp.四月 / r.四月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 四月,
CASE WHEN r.五月 > 0 THEN CAST((gp.五月 / r.五月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 五月,
CASE WHEN r.六月 > 0 THEN CAST((gp.六月 / r.六月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 六月,
CASE WHEN r.七月 > 0 THEN CAST((gp.七月 / r.七月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 七月,
CASE WHEN r.八月 > 0 THEN CAST((gp.八月 / r.八月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 八月,
CASE WHEN r.九月 > 0 THEN CAST((gp.九月 / r.九月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 九月,
CASE WHEN r.十月 > 0 THEN CAST((gp.十月 / r.十月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 十月,
CASE WHEN r.十一月 > 0 THEN CAST((gp.十一月 / r.十一月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 十一月,
CASE WHEN r.十二月 > 0 THEN CAST((gp.十二月 / r.十二月) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 十二月,
CASE WHEN r.合计 > 0 THEN CAST((gp.合计 / r.合计) * 100 AS DECIMAL(18,2)) ELSE 0.00 END AS 合计
FROM GrossProfit gp
CROSS JOIN RevenueData r
),
-- ================== 费用明细示例（福利费、差旅费）==================
WelfareExpense AS (
SELECT
'福利费' AS 科目名称,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 1 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 2 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 二月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 3 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 三月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 4 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 四月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 5 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 五月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 6 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 六月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 7 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 七月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 8 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 八月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 9 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 九月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 10 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 11 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 12 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十二月,
CAST(ISNULL(SUM(tgve.FDEBIT), 0) AS DECIMAL(18,3)) AS 合计
FROM T_GL_VOUCHER tgv
LEFT JOIN T_GL_VOUCHERENTRY tgve ON tgv.FVOUCHERID = tgve.FVOUCHERID
LEFT JOIN T_BD_FLEXITEMDETAILV tbf ON tgve.FDETAILID = tbf.FID
LEFT JOIN T_BD_ACCOUNT tba ON tgve.FACCOUNTID = tba.FACCTID AND tba.FUSEORGID = '101041'
LEFT JOIN (
SELECT tbd.FMASTERID FROM T_BD_DEPARTMENT tbd
JOIN T_BD_DEPARTMENT_L tbdl ON tbd.FDEPTID = tbdl.FDEPTID
WHERE tbdl.FNAME = '冷柜中小业态' AND tbd.FUSEORGID = '101041'
) dept_info ON tbf.FFLEX5 = dept_info.FMASTERID
WHERE tgv.FACCTORGID = '101041'
AND tgv.FYEAR = 2025
AND tba.FNUMBER = '6601.02'
AND dept_info.FMASTERID IS NOT NULL
),
TravelExpense AS (
SELECT
'差旅费' AS 科目名称,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 1 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 2 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 二月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 3 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 三月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 4 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 四月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 5 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 五月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 6 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 六月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 7 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 七月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 8 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 八月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 9 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 九月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 10 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 11 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十一月,
CAST(ISNULL(SUM(CASE WHEN MONTH(tgv.FDATE) = 12 THEN tgve.FDEBIT ELSE 0 END), 0) AS DECIMAL(18,3)) AS 十二月,
CAST(ISNULL(SUM(tgve.FDEBIT), 0) AS DECIMAL(18,3)) AS 合计
FROM T_GL_VOUCHER tgv
LEFT JOIN T_GL_VOUCHERENTRY tgve ON tgv.FVOUCHERID = tgve.FVOUCHERID
LEFT JOIN T_BD_FLEXITEMDETAILV tbf ON tgve.FDETAILID = tbf.FID
LEFT JOIN T_BD_ACCOUNT tba ON tgve.FACCOUNTID = tba.FACCTID AND tba.FUSEORGID = '101041'
LEFT JOIN (
SELECT tbd.FMASTERID FROM T_BD_DEPARTMENT tbd
JOIN T_BD_DEPARTMENT_L tbdl ON tbd.FDEPTID = tbdl.FDEPTID
WHERE tbdl.FNAME = '冷柜中小业态' AND tbd.FUSEORGID = '101041'
) dept_info ON tbf.FFLEX5 = dept_info.FMASTERID
WHERE tgv.FACCTORGID = '101041'
AND tgv.FYEAR = 2025
AND tba.FNUMBER = '6601.04'
AND dept_info.FMASTERID IS NOT NULL
),
-- 费用合计
ExpenseTotal AS (
SELECT
'费用合计' AS 科目名称,
w.一月 + tr.一月 AS 一月,
w.二月 + tr.二月 AS 二月,
w.三月 + tr.三月 AS 三月,
w.四月 + tr.四月 AS 四月,
w.五月 + tr.五月 AS 五月,
w.六月 + tr.六月 AS 六月,
w.七月 + tr.七月 AS 七月,
w.八月 + tr.八月 AS 八月,
w.九月 + tr.九月 AS 九月,
w.十月 + tr.十月 AS 十月,
w.十一月 + tr.十一月 AS 十一月,
w.十二月 + tr.十二月 AS 十二月,
w.合计 + tr.合计 AS 合计
FROM WelfareExpense w
CROSS JOIN TravelExpense tr
)
-- ================== 最终合并输出 ==================
SELECT 科目名称, 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月, 合计 FROM RevenueData
UNION ALL SELECT 科目名称, 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月, 合计 FROM ProductionCost
UNION ALL SELECT 科目名称, 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月, 合计 FROM LogisticsCost
UNION ALL SELECT 科目名称, 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月, 合计 FROM InstallationCost
UNION ALL SELECT 科目名称, 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月, 合计 FROM CostTotal
UNION ALL SELECT 科目名称, 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月, 合计 FROM GrossProfit
UNION ALL SELECT 科目名称, 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月, 合计 FROM GrossMargin
UNION ALL SELECT '费用率', 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月, 合计 FROM ExpenseTotal
UNION ALL SELECT '费用明细：', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL SELECT * FROM WelfareExpense
UNION ALL SELECT * FROM TravelExpense
UNION ALL SELECT 科目名称, 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月, 合计 FROM ExpenseTotal
UNION ALL SELECT '应收账款', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL SELECT '销售回款', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL SELECT '利润总额', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL;
补全
扫一扫
1792
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
