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

[ブログからの掲載](http://1bed.allright.life/?p=2705)

## 経緯

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

## 環境

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

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

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

```sql
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 の値としてセット。

```sql
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%
```

## 計算列の作成

計算列で和暦列の作成。

```sql
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 列の作成。

```sql
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)

```sql
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 以外)

```sql
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