SELECT
A.会計区分コード
, A.会計年度
, '00000' AS 部門コード
, '00000' AS セグメントコード
, A.当方_科目コード AS 科目コード
, '0000000000' AS 予算科目コード
, '000' AS 会計口座コード
, '00' AS 補助科目コード
, '000' AS 財源コード
, 鑑.決算仕訳区分
, 鑑.承認フラグ
, CASE 鑑.伝票種別
WHEN '22' THEN '1'
ELSE '0'
END AS 配賦伝票区分
, 科目M.科目レベル AS 科目レベル
, 科目M.勘定科目総括コード AS 科目総括コード
, 0 AS 前年度実績
, 0 AS 借方発生_期首残高
, 0 AS 貸方発生_期首残高
, CASE
WHEN A.貸借区分 = '0'
THEN (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20230401'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) < '20240101'
THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END AS 借方発生_前日
, CASE
WHEN A.貸借区分 = '1'
THEN (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20230401'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) < '20240101'
THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END AS 貸方発生_前日
, CASE
WHEN A.貸借区分 = '0'
THEN (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END AS 借方発生_範囲
, CASE
WHEN A.貸借区分 = '1'
THEN (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END AS 貸方発生_範囲
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '04' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_01ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '04' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_01ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '05' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_02ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '05' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_02ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '06' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_03ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '06' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_03ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '07' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_04ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '07' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_04ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '08' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_05ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '08' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_05ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '09' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_06ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '09' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_06ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '10' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_07ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '10' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_07ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '11' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_08ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '11' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_08ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '12' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_09ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '12' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_09ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '01' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_10ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '01' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_10ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '02' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_11ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '02' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_11ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '0'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '03' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 借方発生_12ヶ月目
, (
CASE
WHEN CONVERT(CHAR (8), 鑑.伝票日付_出納日) >= '20240101'
AND CONVERT(CHAR (8), 鑑.伝票日付_出納日) <= '20240131'
THEN CASE
WHEN A.貸借区分 = '1'
THEN (
CASE SUBSTRING(CONVERT(CHAR (8), 鑑.伝票日付_出納日), 5, 2)
WHEN '03' THEN A.当方_金額
ELSE 0
END
)
ELSE 0
END
ELSE 0
END
) AS 貸方発生_12ヶ月目
, 0 AS 借方発生_決算仕訳
, 0 AS 貸方発生_決算仕訳
, (
CASE
WHEN A.貸借区分 = '0'
THEN A.当方_金額
ELSE 0
END - CASE
WHEN A.貸借区分 = '1'
THEN A.当方_金額
ELSE 0
END
) * (CASE 科目M.貸借区分 WHEN '0' THEN 1 ELSE - 1 END) AS 当年度実績
FROM
"T_D元帳明細" A
这个SQL文哪块有问题
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
