前提
つい先日質問した週番号についてなのですが,
4月1日が含まれる週を週番号の1として52まで振っていくSQL文について
4月1日はISO規格の週番号だと13か14になるので
問合せにて各年の4月1日の週番号が13と14どっちになるのかを取得して
条件分岐し、週番号を得たいと思っています。
###実現したいこと
4月1日を含む週を週番号1として
1~52まで週番号の振り分け
4月1日を含む週に始まって
3月の最終週までの
月曜スタートの週番号の取得
該当のソースコード
SQL
1SELECT YMD,CASE 2WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 1 AND 13 3AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '2' AND YMD LIKE '%-04-01') 4 IN ('2','3','4','5') 5THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))+39 6WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 1 AND 13 7AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '3' AND YMD LIKE '%-04-01') 8 IN ('2','3','4','5') 9THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))+39 10WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 1 AND 13 11AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '4' AND YMD LIKE '%-04-01') 12 IN ('2','3','4','5') 13THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))+39 14WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 1 AND 13 15AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '5' AND YMD LIKE '%-04-01') 16 IN ('2','3','4','5') 17THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))+39 18 19WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 14 AND 52 20AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '2' AND YMD LIKE '%-04-01') 21 IN ('2','3','4','5') 22THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))-13 23WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 14 AND 52 24AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '3' AND YMD LIKE '%-04-01') 25 IN ('2','3','4','5') 26THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))-13 27WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 14 AND 52 28AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '4' AND YMD LIKE '%-04-01') 29 IN ('2','3','4','5') 30THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))-13 31WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 14 AND 52 32AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '5' AND YMD LIKE '%-04-01') 33 IN ('2','3','4','5') 34THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))-13 35 36 37 38WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 1 AND 12 39AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '1' AND YMD LIKE '%-04-01') 40 IN ('1','6','7') 41THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))+40 42WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 1 AND 12 43AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '6' AND YMD LIKE '%-04-01') 44 IN ('1','6','7') 45THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))+40 46WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 1 AND 12 47AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '7' AND YMD LIKE '%-04-01') 48 IN ('1','6','7') 49THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))+40 50 51 52WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 13 AND 52 53AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '1' AND YMD LIKE '%-04-01') 54 IN ('1','6','7') 55THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))-12 56WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 13 AND 52 57AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '6' AND YMD LIKE '%-04-01') 58 IN ('1','6','7') 59THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))-12 60WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 13 AND 52 61AND (SELECT DISTINCT(K_DAY) FROM 'DATE' WHERE K_DAY = '7' AND YMD LIKE '%-04-01') 62 IN ('1','6','7') 63THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))-12 64 65 66END AS SY_NO 67 68 FROM 'DATE' 69 WHERE YMD LIKE '%-04-01' 70 ORDER BY YMD;
↑曜日でデータを取得し各自条件分岐
しかし例外があり失敗
SQL
1 2SELECT YMD 3,CASE TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW')) 4 5WHEN 14 6THEN (CASE WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 1 AND 13 7 THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))+39 8 WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 14 AND 52 9 THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))-13 10 END 11 ) 12 13 14ELSE (CASE WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 1 AND 12 15 THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))+40 16 WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))BETWEEN 13 AND 52 17 THEN TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(NEXT_DAY(YMD -7,2),'YYYY/MM/DD'),'YYYY/MM/DD'),'IW'))-12 18 END 19 ) 20 21 END AS SY_NO 22 23FROM 'DATE' 24ORDER BY YMD;
↑4月1日を指定して週番号を取得。そこから条件分岐
しかし上の「WHEN 14 」の文しか分岐せずデータ不整合
少しずらすと複数行問合せのエラー
試したこと
上記ソースコードのように
指定項目を変えて条件分岐の試行
カーソルやファンクションの作成
(知識不足から中途半端に終了)
補足情報(FW/ツールのバージョンなど)
SQL-developer- バージョン17.4.0.355
テーブル名:'DATE'
カラム:YEAR char
MONTH char
DAY char
NYEAR(年度) char
K_DAY(曜日番号) char →日曜が1 土曜が7 の順番
YMD(年月日) DATE
KI(0→前期,1→後期) char
回答1件
あなたの回答
tips
プレビュー