teratail
回答率
85.48%
質問するログイン新規登録
トップMySQLに関する質問期間持ちのデータ(空白期間あり)内で有効な期間かどうかをチェックする方法

編集履歴

回答編集履歴

14

変更

2019/03/09 07:24

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -52,7 +52,7 @@
52
52
 
53
53
  ```SQL
54
54
 
55
- select *
55
+ select count(*)
56
56
 
57
57
  from (
58
58
 

13

推敲

2019/03/09 07:24

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -40,11 +40,11 @@
40
40
 
41
41
  無効な期間としては、有効期限マスタに対して、
42
42
 
43
- ・最小の開始以前の期間
43
+ ・最小の開始未満の期間
44
44
 
45
45
  ・有効な期間の隙間
46
46
 
47
- ・最大の終了以降の期間
47
+ ・最大の終了超過の期間
48
48
 
49
49
  となるので、それぞれをunion結合したものを、範囲外の期間とします。
50
50
 

12

推敲

2019/03/08 07:38

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -102,7 +102,7 @@
102
102
 
103
103
  ) step1
104
104
 
105
- where st_ymd!=next_st_ymd -- 隙間があるか？
105
+ where st_ymd!=next_st_ymd -- 隙間があるもの
106
106
 
107
107
  ) OverRange
108
108
 

11

推敲

2019/03/08 05:44

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -38,7 +38,7 @@
38
38
 
39
39
  入力した期間が有効な期間外であればエラーという事で、無効な期間に含まれる場合にエラーとします。
40
40
 
41
- 無効な期間としては、期間のマスタに対して、
41
+ 無効な期間としては、有効期限マスタに対して、
42
42
 
43
43
  ・最小の開始以前の期間
44
44
 

10

補足

2019/03/08 05:42

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -38,11 +38,11 @@
38
38
 
39
39
  入力した期間が有効な期間外であればエラーという事で、無効な期間に含まれる場合にエラーとします。
40
40
 
41
- 無効な期間としては、
41
+ 無効な期間としては、期間のマスタに対して、
42
42
 
43
43
  ・最小の開始以前の期間
44
44
 
45
- ・指定された期間の隙間
45
+ ・有効な期間の隙間
46
46
 
47
47
  ・最大の終了以降の期間
48
48
 

9

訂正

2019/03/08 05:30

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -106,6 +106,6 @@
106
106
 
107
107
  ) OverRange
108
108
 
109
- where st_ymd 入力の開始 between 入力の終了 or ed_ymd 入力の開始 between 入力の終了
109
+ where st_ymd between 入力の開始 and 入力の終了 or ed_ymd between 入力の開始 and 入力の終了
110
110
 
111
111
  ```

8

訂正

2019/03/08 05:29

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -56,11 +56,15 @@
56
56
 
57
57
  from (
58
58
 
59
+ -- 最小の期間外期間
60
+
59
61
  select '000000' as st_ymd
60
62
 
61
63
  , DATE_FORMAT(DATE_ADD(str_to_date(concat(min(st_ymd),'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as ed_ymd
62
64
 
63
65
  from m_rireki
66
+
67
+ -- 最大の期間外期間
64
68
 
65
69
  union all
66
70
 
@@ -70,6 +74,8 @@
70
74
 
71
75
  from m_rireki
72
76
 
77
+ -- 有効期間の隙間期間
78
+
73
79
  union all
74
80
 
75
81
  select next_st_ymd, pre_ed_ymd
@@ -78,15 +84,15 @@
78
84
 
79
85
  select ed.*
80
86
 
81
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd
87
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd -- 連続していた場合の次の開始
82
88
 
83
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd
89
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd -- 連続していた場合の前の終了
84
90
 
85
91
  from (
86
92
 
87
93
  select r.*
88
94
 
89
- ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd
95
+ ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd -- 次の期間の開始
90
96
 
91
97
  from m_rireki r
92
98
 
@@ -96,7 +102,7 @@
96
102
 
97
103
  ) step1
98
104
 
99
- where st_ymd!=next_st_ymd
105
+ where st_ymd!=next_st_ymd -- 隙間があるか？
100
106
 
101
107
  ) OverRange
102
108
 

7

訂正

2019/03/08 05:27

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -52,6 +52,54 @@
52
52
 
53
53
  ```SQL
54
54
 
55
+ select *
56
+
57
+ from (
58
+
59
+ select '000000' as st_ymd
60
+
61
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(min(st_ymd),'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as ed_ymd
62
+
63
+ from m_rireki
64
+
65
+ union all
66
+
67
+ select DATE_FORMAT(DATE_ADD(str_to_date(concat(max(ed_ymd),'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as st_ymd
68
+
55
- --複数の期間に跨る場合の考慮もれ
69
+ ,'999999'
70
+
71
+ from m_rireki
72
+
73
+ union all
74
+
75
+ select next_st_ymd, pre_ed_ymd
76
+
77
+ from (
78
+
79
+ select ed.*
80
+
81
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd
82
+
83
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd
84
+
85
+ from (
86
+
87
+ select r.*
88
+
89
+ ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd
90
+
91
+ from m_rireki r
92
+
93
+ ) st inner join m_rireki ed
94
+
95
+ on st.lead_st_ymd=ed.st_ymd
96
+
97
+ ) step1
98
+
99
+ where st_ymd!=next_st_ymd
100
+
101
+ ) OverRange
102
+
103
+ where st_ymd 入力の開始 between 入力の終了 or ed_ymd 入力の開始 between 入力の終了
56
104
 
57
105
  ```

6

訂正

2019/03/08 05:26

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -52,6 +52,6 @@
52
52
 
53
53
  ```SQL
54
54
 
55
- --複数の機関にまたがるばあいの考慮もれ
55
+ --複数の期間に跨る場合の考慮もれ
56
56
 
57
57
  ```

5

修正中

2019/03/08 05:22

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -52,60 +52,6 @@
52
52
 
53
53
  ```SQL
54
54
 
55
- select *
56
-
57
- from (
58
-
59
- -- 最小の期間外期間
60
-
61
- select '000000' as st_ymd
62
-
63
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(min(st_ymd),'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as ed_ymd
64
-
65
- from m_rireki
66
-
67
- -- 最大の期間外期間
68
-
69
- union all
70
-
71
- select DATE_FORMAT(DATE_ADD(str_to_date(concat(max(ed_ymd),'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as st_ymd
72
-
73
- ,'999999'
55
+ --複数の機関にまたがるばあいの考慮もれ
74
-
75
- from m_rireki
76
-
77
- -- 有効期間の隙間期間
78
-
79
- union all
80
-
81
- select next_st_ymd, pre_ed_ymd
82
-
83
- from (
84
-
85
- select ed.*
86
-
87
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd -- 連続していた場合の次の開始
88
-
89
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd -- 連続していた場合の前の終了
90
-
91
- from (
92
-
93
- select r.*
94
-
95
- ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd -- 次の期間の開始
96
-
97
- from m_rireki r
98
-
99
- ) st inner join m_rireki ed
100
-
101
- on st.lead_st_ymd=ed.st_ymd
102
-
103
- ) step1
104
-
105
- where st_ymd!=next_st_ymd -- 隙間があるか？
106
-
107
- ) OverRange
108
-
109
- where [入力の開始] between st_ymd and ed_ymd or [入力の終了] between st_ymd and ed_ymd
110
56
 
111
57
  ```

4

コメント追加

2019/03/08 05:22

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -84,15 +84,15 @@
84
84
 
85
85
  select ed.*
86
86
 
87
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd
87
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd -- 連続していた場合の次の開始
88
88
 
89
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd
89
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd -- 連続していた場合の前の終了
90
90
 
91
91
  from (
92
92
 
93
93
  select r.*
94
94
 
95
- ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd
95
+ ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd -- 次の期間の開始
96
96
 
97
97
  from m_rireki r
98
98
 
@@ -102,7 +102,7 @@
102
102
 
103
103
  ) step1
104
104
 
105
- where st_ymd!=next_st_ymd
105
+ where st_ymd!=next_st_ymd -- 隙間があるか？
106
106
 
107
107
  ) OverRange
108
108
 

3

追記

2019/03/08 05:15

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -27,3 +27,85 @@
27
27
  where st_ymd<=[入力での開始] and ed_ymd>=[入力での終了]
28
28
 
29
29
  ```
30
+
31
+
32
+
33
+ 追記
34
+
35
+ --
36
+
37
+ 質問の意図を読み違えていました。
38
+
39
+ 入力した期間が有効な期間外であればエラーという事で、無効な期間に含まれる場合にエラーとします。
40
+
41
+ 無効な期間としては、
42
+
43
+ ・最小の開始以前の期間
44
+
45
+ ・指定された期間の隙間
46
+
47
+ ・最大の終了以降の期間
48
+
49
+ となるので、それぞれをunion結合したものを、範囲外の期間とします。
50
+
51
+ その期間に含まれるものを取得できたならエラーという事で。
52
+
53
+ ```SQL
54
+
55
+ select *
56
+
57
+ from (
58
+
59
+ -- 最小の期間外期間
60
+
61
+ select '000000' as st_ymd
62
+
63
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(min(st_ymd),'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as ed_ymd
64
+
65
+ from m_rireki
66
+
67
+ -- 最大の期間外期間
68
+
69
+ union all
70
+
71
+ select DATE_FORMAT(DATE_ADD(str_to_date(concat(max(ed_ymd),'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as st_ymd
72
+
73
+ ,'999999'
74
+
75
+ from m_rireki
76
+
77
+ -- 有効期間の隙間期間
78
+
79
+ union all
80
+
81
+ select next_st_ymd, pre_ed_ymd
82
+
83
+ from (
84
+
85
+ select ed.*
86
+
87
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd
88
+
89
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd
90
+
91
+ from (
92
+
93
+ select r.*
94
+
95
+ ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd
96
+
97
+ from m_rireki r
98
+
99
+ ) st inner join m_rireki ed
100
+
101
+ on st.lead_st_ymd=ed.st_ymd
102
+
103
+ ) step1
104
+
105
+ where st_ymd!=next_st_ymd
106
+
107
+ ) OverRange
108
+
109
+ where [入力の開始] between st_ymd and ed_ymd or [入力の終了] between st_ymd and ed_ymd
110
+
111
+ ```

2

追記

2019/03/08 05:09

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -10,6 +10,20 @@
10
10
 
11
11
  ```SQL
12
12
 
13
+ select count(*) from m_rireki
14
+
13
- select * from m_rireki where st_ymd<=[入力での開始] and ed_ymd>=[入力での終了]
15
+ where st_ymd<=[入力での開始] and ed_ymd>=[入力での終了]
16
+
17
+ having count(*)=1
14
18
 
15
19
  ```
20
+
21
+ 若しくは、HITする件数=1の場合のみOKとする
22
+
23
+ ```SQL
24
+
25
+ select count(*) from m_rireki
26
+
27
+ where st_ymd<=[入力での開始] and ed_ymd>=[入力での終了]
28
+
29
+ ```

1

推敲

2019/03/08 03:07

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -1,4 +1,4 @@
1
- 入力での開始<=入力での終了の前提で、有効な期間かどうかというのが、有効期限マスタの期間内に収まるということであれば、
1
+ [入力での開始<=入力での終了]の前提で、有効な期間かどうかというのが、有効期限マスタの期間内に収まるということであれば、
2
2
 
3
3
  ・st_ymd<=入力での開始 and ed_ymd>=入力での終了
4
4