\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9\u66f8\u304c\u306a\u3044\u306e\u3067\u3001\u81ea\u5206\u3067\u60f3\u50cf\u3067\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u5b9a\u7fa9\u66f8\u3092\u4f5c\u308a\u3001\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u3082\u60f3\u50cf\u3067\u4f5c\u308a\u3002<\/p>\n
\u3060\u308c\u304c\u3069\u306e\u5e33\u7968\u3092\u4f5c\u308b\u304b\u3082\u6c7a\u307e\u3063\u3066\u304a\u3089\u305a\u3001\u3053\u3061\u3068\u3089\u3001\u4f5c\u696d\u304c\u3067\u304d\u306a\u3044\u72b6\u614b\u306a\u306e\u3067\u3059\u3002
\n<\/p>\n
\u30b7\u30b9\u30c6\u30e0\u7a3c\u50cd\u307e\u30673\u304b\u6708\u3092\u5207\u3063\u305f\u4eca\u3002<\/p>\n
\u30ae\u30ea\u30ae\u30ea\u3067\u300c\u3055\u3041\u3084\u3063\u3066\uff01\uff01\u300d\u3068\u8a00\u308f\u308c\u3066\u3082\u3044\u3044\u3088\u3046\u306b\u3001\u3072\u306a\u5f62\u3092\u4f5c\u308a\u307e\u304f\u3063\u3066\u308b\u3002<\/p>\n
\u3053\u306eSQL\u3082\u30a8\u30e9\u30fc\u3084\u3001\u5909\u306a\u51fa\u529b\u3057\u3066\u305f\u306e\u3067\u300115\u56de\u4f5c\u308a\u76f4\u3057\u3066\u3001\u3084\u3063\u3068\u6e80\u8db3\u306e\u3044\u304f\u51fa\u529b\u304c\u3067\u304d\u305f\u3002<\/p>\n
1\u65e51\u672c\u30da\u30fc\u30b9\u3067\u4f5c\u3089\u306a\u3044\u3068\u3001\u8ffd\u3044\u3064\u304b\u306a\u3044\u306e\u3067\u3059\u3002
\n\u305d\u306e\u30da\u30fc\u30b9\u3067\u3082\u8ffd\u3044\u3064\u304b\u306a\u3044\u306a\u3001\u5e33\u7968200\u672c\u3042\u308b\u3057\u3002<\/p>\n
\u52d5\u7684SQL\u306f\u521d\u3081\u3066\u3060\u3051\u308c\u3069\u3001\u3084\u3063\u3066\u307f\u308b\u3068\u9762\u767d\u3044\u3002<\/p>\n
DECLARE @loop_counter int\r\nDECLARE @date_from varchar(max)\r\nDECLARE @date_to varchar(max)\r\nDECLARE @date_diff int\r\nDECLARE @date_now varchar(max)\r\nDECLARE @date_next varchar(max)\r\nDECLARE @CAL_TABLE table(\r\n PlantCode varchar(5) NOT NULL\r\n ,LineCode varchar(2) NOT NULL\r\n\t,PartNumber varchar(20) NOT NULL\r\n ,CalDate datetime NOT NULL\r\n)\r\n\r\nset @loop_counter = 0\r\nset @date_from = '2016\/04\/02'\r\nset @date_to = '2016\/05\/03'\r\n\r\nSELECT\r\n @date_diff =\r\n\tDATEDIFF(day,@date_from,@date_to) + 1\r\n\r\nWHILE @loop_counter < @date_diff\r\n BEGIN\r\n\t IF @loop_counter = 0\r\n\t\t BEGIN\r\n\t\t\t --INSERT INTO @CAL_TABLE VALUES ( @date_from )\r\n\t\t\t\tINSERT INTO @CAL_TABLE\r\n\t\t\t\t SELECT DISTINCT\r\n\t\t\t\t\t PlantCode\r\n\t\t\t\t\t\t,LineCode\r\n\t\t\t\t\t\t,PartNumber\r\n\t\t\t\t\t\t,@date_from\r\n\t\t\t\t\tFROM WORK_PERFORMANCE\r\n\t\t\t\tset @date_now = @date_from\r\n\t\t\tEND\r\n\t\tELSE\r\n\t\t BEGIN\r\n\t\t SELECT @date_next = FORMAT(DATEADD(day,1,@date_now), 'yyyy\/MM\/dd')\r\n\t\t\t --INSERT INTO @CAL_TABLE VALUES ( @date_next )\r\n\t\t\t\tINSERT INTO @CAL_TABLE\r\n\t\t\t\t SELECT DISTINCT\r\n\t\t\t\t\t PlantCode\r\n\t\t\t\t\t\t,LineCode\r\n\t\t\t\t\t\t,PartNumber\r\n\t\t\t\t\t\t,@date_next\r\n\t\t\t\t\tFROM WORK_PERFORMANCE\r\n\t\t\t set @date_now = @date_next\r\n\t\t END\r\n\t \r\n set @loop_counter = @loop_counter + 1\r\n END\r\n\r\nSELECT DISTINCT\r\n\t CALTABLE.PlantCode AS PlantCode\r\n\t,CALTABLE.LineCode AS LineCode\r\n\t,CALTABLE.PartNumber AS PartNumber\r\n\t,CALTABLE.CalDate AS CalDate\r\n\t,FORMAT(CALTABLE.CalDate, 'MM') AS WorkMonth\r\n\t,FORMAT(CALTABLE.caldate, 'dd') AS WorkDate\r\n\t,CASE WHEN (DATENAME(weekday,CALTABLE.caldate) = '\u571f\u66dc\u65e5' OR DATENAME(weekday,CALTABLE.caldate) = '\u65e5\u66dc\u65e5')\r\n\t THEN '*' + FORMAT(CALTABLE.caldate, 'dd')\r\n\t ELSE FORMAT(CALTABLE.caldate, 'dd') END AS YoubiAdd\r\n -- \u4e88\u5b9a\u6570\r\n ,T2.NgrYotei AS NgrYotei\r\n\t-- \u5b9f\u7e3e\u6570\r\n\t,T3.NgrJisseki AS NgrJisseki\r\n\t-- \u7a3c\u50cd\u7387\r\n\t,CASE WHEN (T3.NgrJisseki IS NULL) OR (T3.NgrJisseki = 0) THEN 0 ELSE ROUND((CONVERT(FLOAT,T2.NgrYotei) \/ T3.NgrJisseki) * 100,2) END AS KadoRt\r\n\t-- \u5408\u683c\r\n\t,CASE WHEN (T3.NgrJisseki - (CASE WHEN T4.NgrPeke IS NULL THEN 0 ELSE T4.NgrPeke END)) < 0 THEN 0\r\n\t ELSE (T3.NgrJisseki - (CASE WHEN T4.NgrPeke IS NULL THEN 0 ELSE T4.NgrPeke END)) END AS OKJisskeki\r\n\t,CASE WHEN T4.NgrPeke IS NULL THEN 0 ELSE T4.NgrPeke END AS NgrPeke\r\n\t-- \u5408\u683c\u6570\r\n\t,CASE WHEN (T3.NgrJisseki IS NULL) OR (T3.NgrJisseki = 0) THEN 0 ELSE \r\n\t CASE WHEN (T3.NgrJisseki - (CASE WHEN T4.NgrPeke IS NULL THEN 0 ELSE T4.NgrPeke END)) < 0 THEN 0\r\n\t\tELSE ROUND((CONVERT(FLOAT,(T3.NgrJisseki - (CASE WHEN T4.NgrPeke IS NULL THEN 0 ELSE T4.NgrPeke END))) \/ T3.NgrJisseki) * 100,2) END END AS Budomari\r\n\r\nFROM\r\nWORK_PERFORMANCE AS T1\r\n\r\n-- \u4e88\u5b9a\u6570\r\nINNER JOIN (\r\nSELECT\r\n PlantCode AS PlantCode\r\n\t,LineCode AS LineCode\r\n\t,PartNumber AS PartNumber\r\n\t,Date\r\n\t,SUM(Qty) AS NgrYotei\r\nFROM\r\nMOLDING_PLAN T2\r\nGROUP BY PlantCode, LineCode, PartNumber, Date\r\n) T2 ON\r\n T1.PlantCode = T2.PlantCode\r\nAND T1.LineCode = T2.LineCode\r\nAND FORMAT(T1.WorkDate, 'yyyy\/MM\/dd') = FORMAT(T2.Date, 'yyyy\/MM\/dd')\r\nAND T1.ProcessCode = '01'\r\nAND T1.PartNumber = T2.PartNumber\r\n\r\n-- \u5b9f\u7e3e\u6570\r\nLEFT OUTER JOIN (\r\nSELECT\r\n PlantCode AS PlantCode\r\n\t,LineCode AS LineCode\r\n\t,PartNumber AS PartNumber\r\n\t,WorkDate AS WorkDate\r\n\t,SUM(Qty) AS NgrJisseki\r\nFROM\r\nWORK_PERFORMANCE T3\r\nWHERE\r\nProcessCode = '01'\r\nGROUP BY PlantCode, LineCode, PartNumber, WorkDate\r\n) T3 ON\r\n T1.PlantCode = T3.PlantCode\r\nAND T1.LineCode = T3.LineCode\r\nAND FORMAT(T1.WorkDate, 'yyyy\/MM\/dd') = FORMAT(T3.WorkDate, 'yyyy\/MM\/dd')\r\nAND T1.PartNumber = T3.PartNumber\r\n\r\n-- \u7834\u640d\u6570\r\nLEFT OUTER JOIN (\r\nSELECT\r\n PlantCode AS PlantCode\r\n\t,LineCode AS LineCode\r\n\t,PartNumber AS PartNumber\r\n\t,WorkDate AS WorkDate\r\n\t,SUM(Qty) AS NgrPeke\r\nFROM\r\nWORK_PERFORMANCE\r\nWHERE\r\n ProcessCode = '01'\r\nAND WorkPerformanceResultCode = '02'\r\nGROUP BY PlantCode, LineCode, PartNumber, WorkDate\r\n) T4 ON\r\n T1.PlantCode = T4.PlantCode\r\nAND T1.LineCode = T4.LineCode\r\nAND FORMAT(T1.WorkDate, 'yyyy\/MM\/dd') = FORMAT(T4.WorkDate, 'yyyy\/MM\/dd')\r\nAND T1.PartNumber = T4.PartNumber\r\n\r\nRIGHT OUTER JOIN @CAL_TABLE CALTABLE ON\r\n T1.PlantCode = CALTABLE.PlantCode\r\nAND T1.LineCode = CALTABLE.LineCode\r\nAND T1.PartNumber = CALTABLE.PartNumber\r\nAND FORMAT(T1.WorkDate, 'yyyy\/MM\/dd') = FORMAT(CALTABLE.caldate, 'yyyy\/MM\/dd')\r\n\r\nORDER BY FORMAT(CALTABLE.CalDate, 'MM'),FORMAT(CALTABLE.CalDate, 'dd') ASC<\/pre>\n","protected":false},"excerpt":{"rendered":"\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9\u66f8\u304c\u306a\u3044\u306e\u3067\u3001\u81ea\u5206\u3067\u60f3\u50cf\u3067\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u5b9a\u7fa9\u66f8\u3092\u4f5c\u308a\u3001\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u3082\u60f3\u50cf\u3067\u4f5c\u308a\u3002 \u3060\u308c\u304c\u3069\u306e\u5e33\u7968\u3092\u4f5c\u308b\u304b\u3082\u6c7a\u307e\u3063\u3066\u304a\u3089\u305a\u3001\u3053\u3061\u3068\u3089\u3001\u4f5c\u696d\u304c\u3067\u304d\u306a\u3044\u72b6\u614b\u306a\u306e\u3067\u3059\u3002<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[10],"tags":[],"class_list":{"0":"post-235","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-10"},"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7j620-3N","_links":{"self":[{"href":"http:\/\/blog.ucchanx.net\/index.php?rest_route=\/wp\/v2\/posts\/235","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/blog.ucchanx.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.ucchanx.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.ucchanx.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.ucchanx.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=235"}],"version-history":[{"count":7,"href":"http:\/\/blog.ucchanx.net\/index.php?rest_route=\/wp\/v2\/posts\/235\/revisions"}],"predecessor-version":[{"id":242,"href":"http:\/\/blog.ucchanx.net\/index.php?rest_route=\/wp\/v2\/posts\/235\/revisions\/242"}],"wp:attachment":[{"href":"http:\/\/blog.ucchanx.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=235"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.ucchanx.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=235"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.ucchanx.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=235"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}