{"id":2109,"date":"2007-02-28T01:00:00","date_gmt":"2007-02-27T16:00:00","guid":{"rendered":"http:\/\/keuko.exp.jp\/diary\/?p=2109"},"modified":"2007-02-28T01:00:00","modified_gmt":"2007-02-27T16:00:00","slug":"%e3%83%a6%e3%83%bc%e3%82%b6%e5%ae%9a%e7%be%a9%e9%96%a2%e6%95%b0%ef%bc%88%e3%82%b9%e3%82%ab%e3%83%a9%e5%80%a4%e9%96%a2%e6%95%b0%ef%bc%89%e3%81%ae%e4%bd%9c%e3%82%8a%e6%96%b9%ef%bc%88sql-server-2005","status":"publish","type":"post","link":"https:\/\/keuko.exp.jp\/diary\/?p=2109","title":{"rendered":"\u30e6\u30fc\u30b6\u5b9a\u7fa9\u95a2\u6570\uff08\u30b9\u30ab\u30e9\u5024\u95a2\u6570\uff09\u306e\u4f5c\u308a\u65b9\uff08SQL Server 2005\uff09"},"content":{"rendered":"<p>\u5177\u4f53\u4f8b\u304c\u610f\u5916\u306b\u5c11\u306a\u304f\u3066\u521d\u5fc3\u8005\u306b\u306f\u5927\u5909\u60b2\u3057\u304b\u3063\u305f\u306e\u3067\u3001\u7c21\u5358\u306a\u30b5\u30f3\u30d7\u30eb\u3092\u6b8b\u3057\u3068\u304d\u307e\u3059\u3002<br \/>\n\u8272\u3093\u306a\u6a29\u9650\u306f\u3082\u3061\u308d\u3093\u6301\u3063\u3066\u3044\u308b\u3068\u3044\u3046\u524d\u63d0\u3067\u3002<\/p>\n<ul>\n<li>\u30b9\u30ad\u30fc\u30de\n<ul>\n<li>wkwk<\/li>\n<\/ul>\n<\/li>\n<li>\u30c6\u30fc\u30d6\u30eb\n<ul>\n<li>tblBuilding\uff08\u30d3\u30eb\u60c5\u5831\u683c\u7d0d\u30c6\u30fc\u30d6\u30eb\uff09\n<ul>\n<li>BuildingCode:\u30d3\u30eb\u30b3\u30fc\u30c9<\/li>\n<li>AreaCode:\u5730\u57df\u30b3\u30fc\u30c9<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<ul>\n<li>tblChange\uff08\u5909\u66f4\u60c5\u5831\u683c\u7d0d\u30c6\u30fc\u30d6\u30eb\uff09\n<ul>\n<li>BuildingCode:\u30d3\u30eb\u30b3\u30fc\u30c9<\/li>\n<li>SeqNo:\u30d3\u30eb\u30b3\u30fc\u30c9\u6bce\u306e\u9023\u756a<\/li>\n<li>ChangeDate:\u5909\u66f4\u65e5<\/li>\n<li>ChangeColumn:\u5909\u66f4\u3057\u305f\u9805\u76ee\uff081:\u5730\u57df\u30b3\u30fc\u30c9\uff09<\/li>\n<li>ValueBefore:\u5909\u66f4\u524d\u306e\u5024<\/li>\n<li>ValueAfter:\u5909\u66f4\u5f8c\u306e\u5024<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/ul>\n<ul>\n<li>sys.objects\u306etype\n<ul>\n<li>FN = SQL \u30b9\u30ab\u30e9\u95a2\u6570<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>\u8a73\u3057\u304f\u306f\u4ee5\u4e0b\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002<br \/>\n<a href=\"http:\/\/msdn2.microsoft.com\/ja-jp\/library\/ms190324.aspx\" target=\"_blank\">http:\/\/msdn2.microsoft.com\/ja-jp\/library\/ms190324.aspx<\/a><\/p>\n<p>\u30d3\u30eb\u306e\u5730\u57df\u30b3\u30fc\u30c9\u3092\u3061\u307f\u3061\u307f\u5909\u66f4\u3059\u308b\u306e\u3067\u3059\u304c\u3001\u4f8b\u3048\u3070\u53bb\u5e74\u306e12\u6708\u672b\u6642\u70b9\u306e\u5730\u57df\u30b3\u30fc\u30c9\u304c\u4f55\u3060\u3063\u305f\u304b\u77e5\u308a\u305f\u3044\u308f\u3001\u3068\u3044\u3046\u3068\u304d\u306b\u4f7f\u3046\u95a2\u6570\u3067\u3059\u3002<br \/>\n\u30d3\u30eb\u30b3\u30fc\u30c9\u3068\u65e5\u4ed8\u3092\u6307\u5b9a\u3059\u308b\u3068\u5730\u57df\u30b3\u30fc\u30c9\u304c\u8fd4\u308a\u307e\u3059\u3002<br \/>\n\u3042\u304f\u307e\u3067\u3082\u30b5\u30f3\u30d7\u30eb\u306a\u306e\u3067\u3001\u5730\u57df\u30b3\u30fc\u30c9\u304c\u3061\u307f\u3061\u307f\u5909\u308f\u308b\u306a\u3093\u3066\uff01\u3068\u304b\u305d\u3046\u3044\u3046\u30c4\u30c3\u30b3\u30df\u306f\u3044\u3084\u3093\u3067\u3059\u3002<br \/>\n\u3053\u3093\u306a\u611f\u3058\u3067\u3059\u3002<\/p>\n<pre>\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n\/* \u5b58\u5728\u3059\u308b\u5834\u5408\u306f\u524a\u9664 *\/\nIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'wkwk.GetBuildingArea') AND type in (N'FN'))\nDROP FUNCTION wkwk.GetBuildingArea;\nGO\nCREATE FUNCTION wkwk.GetBuildingArea (@BuildingCode int, @TargetDate smalldatetime)\nRETURNS smallint\nAS\nBEGIN\nDECLARE @intRtn smallint\nDECLARE @intValueNow smallint\nDECLARE @intValueBefore smallint\nSET @intValueNow =\n(SELECT AreaCode\nFROM wkwk.tblBuilding\nWHERE\nBuildingCode = @BuildingCode\n)\nSET @intRtn = @intValueNow\n\/* \u5909\u66f4\u3057\u3066\u3044\u308b\u5834\u5408\u306f\u3001\u6307\u5b9a\u65e5\u76f4\u5f8c\u306e\u5909\u66f4\u524d\u306e\u5024\u3092\u63a1\u7528 *\/\nSET @intValueBefore =\n(SELECT TOP 1 ValueBefore\nFROM wkwk.tblChange\nWHERE\nBuildingCode = @BuildingCode\nAND ChangeDate &#62; @TargetDate\nAND ChangeColumn = 1\nORDER BY\nChangeDate ASC,\nSeqNo ASC\n)\nIF @intValueBefore IS NOT NULL\nSET @intRtn = @intValueBefore\nRETURN @intRtn\nEND;\nGO\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5177\u4f53\u4f8b\u304c\u610f\u5916\u306b\u5c11\u306a\u304f\u3066\u521d\u5fc3\u8005\u306b\u306f\u5927\u5909\u60b2\u3057\u304b\u3063\u305f\u306e\u3067\u3001\u7c21\u5358\u306a\u30b5\u30f3\u30d7\u30eb\u3092\u6b8b\u3057\u3068\u304d\u307e\u3059\u3002 \u8272\u3093\u306a\u6a29\u9650\u306f\u3082\u3061\u308d\u3093\u6301\u3063\u3066\u3044\u308b\u3068\u3044\u3046\u524d\u63d0\u3067\u3002 \u30b9\u30ad\u30fc\u30de wkwk \u30c6\u30fc\u30d6\u30eb tblBuilding\uff08\u30d3\u30eb\u60c5\u5831\u683c\u7d0d\u30c6\u30fc\u30d6\u30eb\uff09 Buildi<\/p>\n<p><a class=\"button\" href=\"https:\/\/keuko.exp.jp\/diary\/?p=2109\" title=\"More\">  \u7d9a\u304d\u3092\u8aad\u3080 \u2192<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"class_list":["post-2109","post","type-post","status-publish","format-standard","hentry","category-job"],"_links":{"self":[{"href":"https:\/\/keuko.exp.jp\/diary\/index.php?rest_route=\/wp\/v2\/posts\/2109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/keuko.exp.jp\/diary\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/keuko.exp.jp\/diary\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/keuko.exp.jp\/diary\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/keuko.exp.jp\/diary\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2109"}],"version-history":[{"count":0,"href":"https:\/\/keuko.exp.jp\/diary\/index.php?rest_route=\/wp\/v2\/posts\/2109\/revisions"}],"wp:attachment":[{"href":"https:\/\/keuko.exp.jp\/diary\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/keuko.exp.jp\/diary\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/keuko.exp.jp\/diary\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}