/* ==========================================================================
银行日记账：① 期初 ② 明细 ③ 期末余额（汇总行）
========================================================================== */
DECLARE @BOOK_YEAR VARCHAR(4) = '2026',
@OPEN_PERIOD INT = 8, -- 8 期期末 = 9 月期初
@DATE_FROM DATE = '2026-09-01',
@DATE_TO_EXCL DATE = '2026-10-01', -- 用开区间，别写 <= '2026-09-30'
@BANK_ACCOUNT_NO NVARCHAR(30) = '6********99999',
@COMPANY_CODE VARCHAR(20) = '1';
;WITH CO_ORG AS (
SELECT COMPANY_ID FROM dbo.COMPANY WHERE COMPANY_CODE = @COMPANY_CODE
),
ACC_INFO AS (
SELECT ACC.BANK_ACCOUNT_ID, ACC.BANK_ACCOUNT_NO, BK.BANK_SHORT_NAME
FROM BANK_ACCOUNT AS ACC
INNER JOIN BANK AS BK ON BK.BANK_ID = ACC.BANK_ID
WHERE ACC.BANK_ACCOUNT_NO = @BANK_ACCOUNT_NO OR ISNULL(@BANK_ACCOUNT_NO, '') = ''
),
CUR_INFO AS (
SELECT CURRENCY_ID, CURRENCY_CODE FROM CURRENCY
),
/* ------- 期初 ------- */
OPEN_BAL AS (
SELECT BAL.SOURCE_ID_ROid AS BANK_ACCOUNT_ID,
BAL.CURRENCY_ID,
SUM(ISNULL(BAL.OPENING_AMT_TC, 0) + ISNULL(BAL.DEBIT_AMT_TC, 0) - ISNULL(BAL.CREDIT_AMT_TC, 0)) AS 期初_原币,
SUM(ISNULL(BAL.OPENING_AMT_FC, 0) + ISNULL(BAL.DEBIT_AMT_FC, 0) - ISNULL(BAL.CREDIT_AMT_FC, 0)) AS 期初_本币,
CAST(0 AS DECIMAL(18, 6)) AS 借_原币,
CAST(0 AS DECIMAL(18, 6)) AS 贷_原币,
CAST(0 AS DECIMAL(18, 6)) AS 借_本币,
CAST(0 AS DECIMAL(18, 6)) AS 贷_本币
FROM BANK_BALANCE AS BAL
WHERE BAL.Owner_Org_ROid IN (SELECT COMPANY_ID FROM CO_ORG)
AND BAL.ACCOUNT_YEAR = @BOOK_YEAR
AND BAL.ACCOUNT_PERIOD_SEQNO = @OPEN_PERIOD
AND BAL.SOURCE_ID_RTK = 'BANK_ACCOUNT'
GROUP BY BAL.SOURCE_ID_ROid, BAL.CURRENCY_ID
),
/* ------- 本期发生额 ------- */
MOVEMENT AS (
SELECT HIS.SOURCE_ID_ROid AS BANK_ACCOUNT_ID,
HIS.CURRENCY_ID,
CAST(0 AS DECIMAL(18, 6)) AS 期初_原币,
CAST(0 AS DECIMAL(18, 6)) AS 期初_本币,
SUM(CASE WHEN HIS.BALANCE_DIRECTION = 1 THEN ISNULL(HIS.AMT_TC, 0) ELSE 0 END) AS 借_原币,
SUM(CASE WHEN HIS.BALANCE_DIRECTION = 1 THEN 0 ELSE ISNULL(HIS.AMT_TC, 0) END) AS 贷_原币,
SUM(CASE WHEN HIS.BALANCE_DIRECTION = 1 THEN ISNULL(HIS.AMT_FC, 0) ELSE 0 END) AS 借_本币,
SUM(CASE WHEN HIS.BALANCE_DIRECTION = 1 THEN 0 ELSE ISNULL(HIS.AMT_FC, 0) END) AS 贷_本币
FROM BANK_HISTORY AS HIS
WHERE HIS.Owner_Org_ROid IN (SELECT COMPANY_ID FROM CO_ORG)
AND HIS.SOURCE_ID_RTK = 'BANK_ACCOUNT'
AND HIS.BOOKKEEPING_DATE >= @DATE_FROM
AND HIS.BOOKKEEPING_DATE < @DATE_TO_EXCL
GROUP BY HIS.SOURCE_ID_ROid, HIS.CURRENCY_ID
),
/* ------- 每账号+币种一行：期初 / 本期借 / 本期贷 ------- */
BAL_ALL AS (
SELECT BANK_ACCOUNT_ID,
CURRENCY_ID,
SUM(期初_原币) AS 期初_原币, SUM(借_原币) AS 借_原币, SUM(贷_原币) AS 贷_原币,
SUM(期初_本币) AS 期初_本币, SUM(借_本币) AS 借_本币, SUM(贷_本币) AS 贷_本币
FROM (SELECT * FROM OPEN_BAL
UNION ALL
SELECT * FROM MOVEMENT) AS X
GROUP BY BANK_ACCOUNT_ID, CURRENCY_ID
),
/* ------- 明细逐笔 ------- */
DETAIL AS (
SELECT HIS.SOURCE_ID_ROid AS BANK_ACCOUNT_ID,
HIS.CURRENCY_ID,
CONVERT(VARCHAR(10), HIS.BOOKKEEPING_DATE, 120) AS 记账日期,
HIS.BOOKKEEPING_DATE AS 排序_日期,
ISNULL(HIS.TRANS_DOC_NO, '') AS 排序_单据,
CASE HIS.TRANS_NATURE WHEN '1' THEN '存现'
WHEN '2' THEN '取现'
WHEN '3' THEN '收款'
WHEN '4' THEN '付款'
WHEN '5' THEN '调拨'
WHEN '6' THEN '票据兑付'
WHEN '7' THEN '票据贴现'
WHEN '8' THEN '调汇'
WHEN '90' THEN '业务费用' END AS 交易性质,
CASE HIS.SOURCE5_ID_RTK WHEN 'BANK' THEN '银行'
WHEN 'CUSTOMER' THEN '客户'
WHEN 'EMPLOYEE' THEN '人员'
WHEN 'OTHER' THEN '其他'
WHEN 'SUPPLIER' THEN '供应商' END AS 结算对象类型,
CASE HIS.SOURCE5_ID_RTK
WHEN 'BANK' THEN '(' + ISNULL(PBK.BANK_CODE, '') + ') ' + ISNULL(PBK.BANK_SHORT_NAME, '')
WHEN 'CUSTOMER' THEN '(' + ISNULL(PCU.CUSTOMER_CODE, '') + ') ' + ISNULL(PCU.CUSTOMER_NAME, '')
WHEN 'EMPLOYEE' THEN '(' + ISNULL(PEM.EMPLOYEE_CODE, '') + ') ' + ISNULL(PEM.EMPLOYEE_NAME, '')
WHEN 'OTHER' THEN '其他'
WHEN 'SUPPLIER' THEN '(' + ISNULL(PSU.SUPPLIER_CODE, '') + ') ' + ISNULL(PSU.SUPPLIER_NAME, '')
END AS 结算对象,
CASE WHEN HIS.BALANCE_DIRECTION = 1 THEN ISNULL(HIS.AMT_TC, 0) ELSE 0 END AS 借_原币,
CASE WHEN HIS.BALANCE_DIRECTION = 1 THEN 0 ELSE ISNULL(HIS.AMT_TC, 0) END AS 贷_原币,
CASE WHEN HIS.BALANCE_DIRECTION = 1 THEN ISNULL(HIS.AMT_FC, 0) ELSE 0 END AS 借_本币,
CASE WHEN HIS.BALANCE_DIRECTION = 1 THEN 0 ELSE ISNULL(HIS.AMT_FC, 0) END AS 贷_本币,
ISNULL(HIS.REMARK, '') AS 业务备注
FROM BANK_HISTORY AS HIS
LEFT JOIN dbo.BANK AS PBK ON PBK.BANK_ID = HIS.SOURCE5_ID_ROid
AND HIS.SOURCE5_ID_RTK = 'BANK'
LEFT JOIN dbo.CUSTOMER AS PCU ON PCU.CUSTOMER_BUSINESS_ID = HIS.SOURCE5_ID_ROid
AND HIS.SOURCE5_ID_RTK = 'CUSTOMER'
LEFT JOIN dbo.EMPLOYEE AS PEM ON PEM.EMPLOYEE_ID = HIS.SOURCE5_ID_ROid
AND HIS.SOURCE5_ID_RTK = 'EMPLOYEE'
LEFT JOIN dbo.SUPPLIER AS PSU ON PSU.SUPPLIER_BUSINESS_ID = HIS.SOURCE5_ID_ROid
AND HIS.SOURCE5_ID_RTK = 'SUPPLIER'
WHERE HIS.Owner_Org_ROid IN (SELECT COMPANY_ID FROM CO_ORG)
AND HIS.SOURCE_ID_RTK = 'BANK_ACCOUNT'
AND HIS.BOOKKEEPING_DATE >= @DATE_FROM
AND HIS.BOOKKEEPING_DATE < @DATE_TO_EXCL
)
SELECT 银行账号, 银行简称, 货币, 行类型, 记账日期, 交易性质, 交易单据,
结算对象类型, 结算对象,
期初金额原币, 借方金额原币, 贷方金额原币, 余额原币,
期初金额本币, 借方金额本币, 贷方金额本币, 余额本币,
业务备注
FROM
(
/* ================= 第一段：期初 ================= */
SELECT ACC.BANK_ACCOUNT_NO AS 银行账号,
ACC.BANK_SHORT_NAME AS 银行简称,
CUR.CURRENCY_CODE AS 货币,
'期初' AS 行类型,
'' AS 记账日期,
'' AS 交易性质,
'' AS 交易单据,
'' AS 结算对象类型,
'' AS 结算对象,
B.期初_原币 AS 期初金额原币,
0 AS 借方金额原币,
0 AS 贷方金额原币,
B.期初_原币 AS 余额原币,
B.期初_本币 AS 期初金额本币,
0 AS 借方金额本币,
0 AS 贷方金额本币,
B.期初_本币 AS 余额本币,
'' AS 业务备注,
ACC.BANK_ACCOUNT_NO AS 排序_账号,
CUR.CURRENCY_CODE AS 排序_货币,
0 AS 排序_段序,
CAST(NULL AS DATETIME) AS 排序_日期,
'' AS 排序_单据
FROM BAL_ALL AS B
INNER JOIN ACC_INFO AS ACC ON ACC.BANK_ACCOUNT_ID = B.BANK_ACCOUNT_ID
INNER JOIN CUR_INFO AS CUR ON CUR.CURRENCY_ID = B.CURRENCY_ID
UNION ALL
/* ================= 第二段：明细 ================= */
SELECT ACC.BANK_ACCOUNT_NO AS 银行账号,
ACC.BANK_SHORT_NAME AS 银行简称,
CUR.CURRENCY_CODE AS 货币,
'明细' AS 行类型,
D.记账日期 AS 记账日期,
D.交易性质 AS 交易性质,
D.排序_单据 AS 交易单据,
D.结算对象类型 AS 结算对象类型,
D.结算对象 AS 结算对象,
0 AS 期初金额原币,
D.借_原币 AS 借方金额原币,
D.贷_原币 AS 贷方金额原币,
NULL AS 余额原币, -- 明细行不挂余额，见文末可选方案
0 AS 期初金额本币,
D.借_本币 AS 借方金额本币,
D.贷_本币 AS 贷方金额本币,
NULL AS 余额本币,
D.业务备注 AS 业务备注,
ACC.BANK_ACCOUNT_NO AS 排序_账号,
CUR.CURRENCY_CODE AS 排序_货币,
1 AS 排序_段序,
D.排序_日期 AS 排序_日期,
D.排序_单据 AS 排序_单据
FROM DETAIL AS D
INNER JOIN ACC_INFO AS ACC ON ACC.BANK_ACCOUNT_ID = D.BANK_ACCOUNT_ID
INNER JOIN CUR_INFO AS CUR ON CUR.CURRENCY_ID = D.CURRENCY_ID
UNION ALL
/* ================= 第三段：期末余额（汇总行） ================= */
SELECT ACC.BANK_ACCOUNT_NO AS 银行账号,
ACC.BANK_SHORT_NAME AS 银行简称,
CUR.CURRENCY_CODE AS 货币,
'期末' AS 行类型,
'' AS 记账日期,
'' AS 交易性质,
'' AS 交易单据,
'' AS 结算对象类型,
'' AS 结算对象,
B.期初_原币 AS 期初金额原币,
B.借_原币 AS 借方金额原币,
B.贷_原币 AS 贷方金额原币,
B.期初_原币 + B.借_原币 - B.贷_原币 AS 余额原币,
B.期初_本币 AS 期初金额本币,
B.借_本币 AS 借方金额本币,
B.贷_本币 AS 贷方金额本币,
B.期初_本币 + B.借_本币 - B.贷_本币 AS 余额本币,
'' AS 业务备注,
ACC.BANK_ACCOUNT_NO AS 排序_账号,
CUR.CURRENCY_CODE AS 排序_货币,
2 AS 排序_段序,
CAST(NULL AS DATETIME) AS 排序_日期,
'' AS 排序_单据
FROM BAL_ALL AS B
INNER JOIN ACC_INFO AS ACC ON ACC.BANK_ACCOUNT_ID = B.BANK_ACCOUNT_ID
INNER JOIN CUR_INFO AS CUR ON CUR.CURRENCY_ID = B.CURRENCY_ID
) AS tt
ORDER BY tt.排序_账号,
tt.排序_货币,
tt.排序_段序, -- 0=期初 1=明细 2=期末，三段永不串位
tt.排序_日期,
tt.排序_单据;
浙公网安备 33010602011771号