{"id":2705,"date":"2018-07-17T02:09:08","date_gmt":"2018-07-16T17:09:08","guid":{"rendered":"http:\/\/1bed.allright.life\/?p=2705"},"modified":"2020-04-18T19:35:47","modified_gmt":"2020-04-18T10:35:47","slug":"post-2705","status":"publish","type":"post","link":"https:\/\/1bed.allright.life\/?p=2705","title":{"rendered":"SQL Server \u8a08\u7b97\u5217\u3067\u3001\u548c\u66a6\u3068\u6d88\u8cbb\u7a0e\u8a08\u7b97\u3057\u3066\u307f\u308b"},"content":{"rendered":"

\u7d4c\u7def<\/h2>\n

\u8a08\u7b97\u5217\u3092\u899a\u3048\u3066\u304a\u304f\u305f\u3081\u306b\u904a\u3073\u305f\u304f\u3066\u7c21\u5358\u306b\u8a66\u3057\u305f\u3002<\/p>\n

\u74b0\u5883<\/h2>\n

\u65e5\u4ed8\u578b\u3068\u901a\u8ca8\u578b\u306e\u30d5\u30a3\u30fc\u30eb\u30c9\u3092\u6301\u3064\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3002<\/p>\n

CREATE TABLE [dbo].[dummy2](\n    [SALES_DATE] [date] NULL,\n    [SALES_PRICE] [money] NULL,\n);<\/code><\/pre>\n
\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u306e\u4f5c\u6210\u3092\u4f5c\u6210\u3002
\n\u300c\u5143\u53f7\u304c\u5207\u308a\u66ff\u308f\u308b\u524d\u65e5\u300d\u300c\u5143\u53f7\u304c\u5207\u308a\u66ff\u308f\u3063\u305f\u5f53\u65e5\u300d\u300c\u5143\u53f7\u304c\u5207\u308a\u66ff\u308f\u3063\u305f\u7fcc\u5e74\u300d\u3092 SALES_PRICE \u304c NULL \u306e\u5024\u3068\u3057\u3066\u30bb\u30c3\u30c8\u3002<\/p>\n
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1868-01-24', null); -- \u660e\u6cbb\u4ee5\u524d\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1868-01-25', null); -- \u660e\u6cbb\u5143\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1869-01-01', null); -- \u660e\u6cbb2\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1912-07-29', null); -- \u660e\u6cbb45\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1912-07-30', null); -- \u5927\u6b63\u5143\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1913-01-01', null); -- \u5927\u6b632\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1926-12-24', null); -- \u5927\u6b6315\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1926-12-25', null); -- \u662d\u548c\u5143\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1927-01-01', null); -- \u662d\u548c2\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1989-01-07', null); -- \u662d\u548c64\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1989-01-08', null); -- \u5e73\u6210\u5143\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1990-01-01', null); -- \u5e73\u62102\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('2019-04-30', null); -- \u5e73\u621031\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('2019-05-01', null); -- \u65b0\u5143\u53f7\u5143\u5e74\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('2020-01-01', null); -- \u65b0\u5143\u53f72\u5e74<\/code><\/pre>\n
\u300c\u6d88\u8cbb\u7a0e\u304c\u5207\u308a\u66ff\u308f\u308b\u524d\u65e5\u300d\u300c\u6d88\u8cbb\u7a0e\u304c\u5207\u308a\u66ff\u308f\u3063\u305f\u5f53\u65e5\u300d\u3092 SALES_PRICE \u304c 1000 \u306e\u5024\u3068\u3057\u3066\u30bb\u30c3\u30c8\u3002<\/p>\n
INSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1989-03-31', 1000); -- \u6d88\u8cbb\u7a0e\u306a\u3057\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1989-04-01', 1000); -- \u6d88\u8cbb\u7a0e3%\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1997-03-31', 1000); -- \u6d88\u8cbb\u7a0e3%\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('1997-04-01', 1000); -- \u6d88\u8cbb\u7a0e5%\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('2014-03-31', 1000); -- \u6d88\u8cbb\u7a0e5%\nINSERT INTO dummy2 (SALES_DATE, SALES_PRICE) VALUES('2014-04-01', 1000); -- \u6d88\u8cbb\u7a0e8%<\/code><\/pre>\n
\u8a08\u7b97\u5217\u306e\u4f5c\u6210<\/h2>\n
\u8a08\u7b97\u5217\u3067\u548c\u66a6\u5217\u306e\u4f5c\u6210\u3002<\/p>\n
ALTER TABLE dbo.dummy2 ADD SALES_DATE_JP AS (CASE\n    WHEN [SALES_DATE] >= '2019-5-1' THEN\n        '\u65b0\u5143\u53f7' + CASE\n        WHEN YEAR([SALES_DATE]) = 2019 THEN\n            '\u5143'\n        ELSE\n            CONVERT(NVARCHAR, YEAR([SALES_DATE]) - 2019 + 1) \n        END\n    WHEN [SALES_DATE] >= '1989-1-8' THEN\n        '\u5e73\u6210' + CASE\n        WHEN YEAR([SALES_DATE]) = 1989 THEN\n            '\u5143'\n        ELSE\n            CONVERT(NVARCHAR, YEAR([SALES_DATE]) - 1989 + 1) \n        END\n    WHEN [SALES_DATE] >= '1926-12-25' THEN\n        '\u662d\u548c' + CASE\n        WHEN YEAR([SALES_DATE]) = 1926 THEN\n            '\u5143'\n        ELSE\n            CONVERT(NVARCHAR, YEAR([SALES_DATE]) - 1926 + 1) \n        END\n    WHEN [SALES_DATE] >= '1912-07-30' THEN\n        '\u5927\u6b63' + CASE\n        WHEN YEAR([SALES_DATE]) = 1912 THEN\n            '\u5143'\n        ELSE\n            CONVERT(NVARCHAR, YEAR([SALES_DATE]) - 1912 + 1) \n        END\n    WHEN [SALES_DATE] >= '1868-01-25' THEN\n        '\u660e\u6cbb' + CASE\n        WHEN YEAR([SALES_DATE]) = 1868 THEN\n            '\u5143'\n        ELSE\n            CONVERT(NVARCHAR, YEAR([SALES_DATE]) - 1868 + 1) \n        END\n    ELSE\n        CONVERT(NVARCHAR, YEAR([SALES_DATE]))\n    END + FORMAT([SALES_DATE], '\u5e74MM\u6708dd\u65e5')\n) PERSISTED;<\/code><\/pre>\n
\u8a08\u7b97\u5217\u3067\u6d88\u8cbb\u7a0e\u8fbc\u307f SALES_PRICE \u5217\u306e\u4f5c\u6210\u3002<\/p>\n
ALTER TABLE dbo.dummy2 ADD SALES_PRICE_WITH_TAX AS (CASE\n    WHEN [SALES_DATE] >= '2014-04-01' THEN\n       [SALES_PRICE] * 1.08\n    WHEN [SALES_DATE] >= '1997-04-01' THEN\n       [SALES_PRICE] * 1.05\n    WHEN [SALES_DATE] >= '1989-04-01' THEN\n       [SALES_PRICE] * 1.03\n    ELSE\n       [SALES_PRICE]\n    END\n) PERSISTED;<\/code><\/pre>\n
\u30c6\u30b9\u30c8\u5b9f\u884c<\/h2>\n
\u548c\u66a6\u5217\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u306e\u53d6\u5f97 (SALES_PRICE \u304c NULL)<\/p>\n
SELECT SALES_DATE, SALES_DATE_JP FROM dummy2 WHERE SALES_PRICE IS NULL ORDER BY SALES_DATE;\n\nSALES_DATE  SALES_DATE_JP\n1868-01-24  1868\u5e7401\u670824\u65e5\n1868-01-25  \u660e\u6cbb\u5143\u5e7401\u670825\u65e5\n1869-01-01  \u660e\u6cbb2\u5e7401\u670801\u65e5\n1912-07-29  \u660e\u6cbb45\u5e7407\u670829\u65e5\n1912-07-30  \u5927\u6b63\u5143\u5e7407\u670830\u65e5\n1913-01-01  \u5927\u6b632\u5e7401\u670801\u65e5\n1926-12-24  \u5927\u6b6315\u5e7412\u670824\u65e5\n1926-12-25  \u662d\u548c\u5143\u5e7412\u670825\u65e5\n1927-01-01  \u662d\u548c2\u5e7401\u670801\u65e5\n1989-01-07  \u662d\u548c64\u5e7401\u670807\u65e5\n1989-01-08  \u5e73\u6210\u5143\u5e7401\u670808\u65e5\n1990-01-01  \u5e73\u62102\u5e7401\u670801\u65e5\n2019-04-30  \u5e73\u621031\u5e7404\u670830\u65e5\n2019-05-01  \u65b0\u5143\u53f7\u5143\u5e7405\u670801\u65e5\n2020-01-01  \u65b0\u5143\u53f72\u5e7401\u670801\u65e5<\/code><\/pre>\n
\u7a0e\u8fbc\u307f\u5217\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u306e\u53d6\u5f97  (SALES_PRICE \u304c NULL \u4ee5\u5916)<\/p>\n
SELECT SALES_DATE, SALES_DATE_JP, SALES_PRICE, SALES_PRICE_WITH_TAX FROM dummy2 WHERE SALES_PRICE IS NOT NULL ORDER BY SALES_DATE;\n\nSALES_DATE  SALES_DATE_JP   SALES_PRICE SALES_PRICE_WITH_TAX\n1989-03-31  \u5e73\u6210\u5143\u5e7403\u670831\u65e5  1000.00         1000.000000\n1989-04-01  \u5e73\u6210\u5143\u5e7404\u670801\u65e5  1000.00         1030.000000\n1997-03-31  \u5e73\u62109\u5e7403\u670831\u65e5  1000.00         1030.000000\n1997-04-01  \u5e73\u62109\u5e7404\u670801\u65e5  1000.00         1050.000000\n2014-03-31  \u5e73\u621026\u5e7403\u670831\u65e5 1000.00         1050.000000\n2014-04-01  \u5e73\u621026\u5e7404\u670801\u65e5 1000.00         1080.000000<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
\u7d4c\u7def \u8a08\u7b97\u5217\u3092\u899a\u3048\u3066\u304a\u304f\u305f\u3081\u306b\u904a\u3073\u305f\u304f\u3066\u7c21\u5358\u306b\u8a66\u3057\u305f\u3002 \u74b0\u5883 \u65e5\u4ed8\u578b\u3068\u901a\u8ca8\u578b\u306e\u30d5\u30a3\u30fc\u30eb\u30c9\u3092\u6301\u3064\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3002 CREATE TABLE [dbo].[dummy2]( [SALES_DATE] [date] NULL, […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49],"tags":[117],"class_list":["post-2705","post","type-post","status-publish","format-standard","hentry","category-database","tag-sqlserver"],"_links":{"self":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts\/2705","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2705"}],"version-history":[{"count":4,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts\/2705\/revisions"}],"predecessor-version":[{"id":3106,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts\/2705\/revisions\/3106"}],"wp:attachment":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2705"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2705"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2705"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}