SQL Server 計算列で、和暦と消費税計算してみる

2020/04/18

経緯

計算列を覚えておくために遊びたくて簡単に試した。

環境

日付型と通貨型のフィールドを持つテーブルを作成。

CREATE TABLE [dbo].[dummy2](
    [SALES_DATE] [date] NULL,
    [SALES_PRICE] [money] NULL,
);

テストデータの作成を作成。
「元号が切り替わる前日」「元号が切り替わった当日」「元号が切り替わった翌年」を SALES_PRICE が NULL の値としてセット。

INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1868-01-24', null); -- 明治以前
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1868-01-25', null); -- 明治元年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1869-01-01', null); -- 明治2年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1912-07-29', null); -- 明治45年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1912-07-30', null); -- 大正元年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1913-01-01', null); -- 大正2年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1926-12-24', null); -- 大正15年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1926-12-25', null); -- 昭和元年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1927-01-01', null); -- 昭和2年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1989-01-07', null); -- 昭和64年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1989-01-08', null); -- 平成元年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1990-01-01', null); -- 平成2年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('2019-04-30', null); -- 平成31年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('2019-05-01', null); -- 新元号元年
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('2020-01-01', null); -- 新元号2年

「消費税が切り替わる前日」「消費税が切り替わった当日」を SALES_PRICE が 1000 の値としてセット。

INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1989-03-31', 1000); -- 消費税なし
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1989-04-01', 1000); -- 消費税3%
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1997-03-31', 1000); -- 消費税3%
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1997-04-01', 1000); -- 消費税5%
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('2014-03-31', 1000); -- 消費税5%
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('2014-04-01', 1000); -- 消費税8%

計算列の作成

計算列で和暦列の作成。

ALTER TABLE dbo.dummy2 ADD SALES_DATE_JP AS (CASE
    WHEN [SALES_DATE] >= '2019-5-1' THEN
        '新元号' + CASE
        WHEN YEAR([SALES_DATE]) = 2019 THEN
            '元'
        ELSE
            CONVERT(NVARCHAR, YEAR([SALES_DATE]) - 2019 + 1) 
        END
    WHEN [SALES_DATE] >= '1989-1-8' THEN
        '平成' + CASE
        WHEN YEAR([SALES_DATE]) = 1989 THEN
            '元'
        ELSE
            CONVERT(NVARCHAR, YEAR([SALES_DATE]) - 1989 + 1) 
        END
    WHEN [SALES_DATE] >= '1926-12-25' THEN
        '昭和' + CASE
        WHEN YEAR([SALES_DATE]) = 1926 THEN
            '元'
        ELSE
            CONVERT(NVARCHAR, YEAR([SALES_DATE]) - 1926 + 1) 
        END
    WHEN [SALES_DATE] >= '1912-07-30' THEN
        '大正' + CASE
        WHEN YEAR([SALES_DATE]) = 1912 THEN
            '元'
        ELSE
            CONVERT(NVARCHAR, YEAR([SALES_DATE]) - 1912 + 1) 
        END
    WHEN [SALES_DATE] >= '1868-01-25' THEN
        '明治' + CASE
        WHEN YEAR([SALES_DATE]) = 1868 THEN
            '元'
        ELSE
            CONVERT(NVARCHAR, YEAR([SALES_DATE]) - 1868 + 1) 
        END
    ELSE
        CONVERT(NVARCHAR, YEAR([SALES_DATE]))
    END + FORMAT([SALES_DATE], '年MM月dd日')
) PERSISTED;

計算列で消費税込み SALES_PRICE 列の作成。

ALTER TABLE dbo.dummy2 ADD SALES_PRICE_WITH_TAX AS (CASE
    WHEN [SALES_DATE] >= '2014-04-01' THEN
       [SALES_PRICE] * 1.08
    WHEN [SALES_DATE] >= '1997-04-01' THEN
       [SALES_PRICE] * 1.05
    WHEN [SALES_DATE] >= '1989-04-01' THEN
       [SALES_PRICE] * 1.03
    ELSE
       [SALES_PRICE]
    END
) PERSISTED;

テスト実行

和暦列テストデータの取得 (SALES_PRICE が NULL)

SELECT SALES_DATE, SALES_DATE_JP FROM dummy2 WHERE SALES_PRICE IS NULL ORDER BY SALES_DATE;

SALES_DATE  SALES_DATE_JP
1868-01-24  1868年01月24日
1868-01-25  明治元年01月25日
1869-01-01  明治2年01月01日
1912-07-29  明治45年07月29日
1912-07-30  大正元年07月30日
1913-01-01  大正2年01月01日
1926-12-24  大正15年12月24日
1926-12-25  昭和元年12月25日
1927-01-01  昭和2年01月01日
1989-01-07  昭和64年01月07日
1989-01-08  平成元年01月08日
1990-01-01  平成2年01月01日
2019-04-30  平成31年04月30日
2019-05-01  新元号元年05月01日
2020-01-01  新元号2年01月01日

税込み列テストデータの取得 (SALES_PRICE が NULL 以外)

SELECT SALES_DATE, SALES_DATE_JP, SALES_PRICE, SALES_PRICE_WITH_TAX FROM dummy2 WHERE SALES_PRICE IS NOT NULL ORDER BY SALES_DATE;

SALES_DATE  SALES_DATE_JP   SALES_PRICE SALES_PRICE_WITH_TAX
1989-03-31  平成元年03月31日  1000.00         1000.000000
1989-04-01  平成元年04月01日  1000.00         1030.000000
1997-03-31  平成9年03月31日  1000.00         1030.000000
1997-04-01  平成9年04月01日  1000.00         1050.000000
2014-03-31  平成26年03月31日 1000.00         1050.000000
2014-04-01  平成26年04月01日 1000.00         1080.000000

DatabaseSQLServer

Posted by みやみや