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

編集履歴

回答編集履歴

14

変更

2019/03/09 07:24

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -25,7 +25,7 @@
25
25
  となるので、それぞれをunion結合したものを、範囲外の期間とします。
26
26
  その期間に含まれるものを取得できたならエラーという事で。
27
27
  ```SQL
28
- select *
28
+ select count(*)
29
29
  from (
30
30
  -- 最小の期間外期間
31
31
  select '000000' as st_ymd

13

推敲

2019/03/09 07:24

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -19,9 +19,9 @@
19
19
  質問の意図を読み違えていました。
20
20
  入力した期間が有効な期間外であればエラーという事で、無効な期間に含まれる場合にエラーとします。
21
21
  無効な期間としては、有効期限マスタに対して、
22
- ・最小の開始以前の期間
22
+ ・最小の開始未満の期間
23
23
  ・有効な期間の隙間
24
- ・最大の終了以降の期間
24
+ ・最大の終了超過の期間
25
25
  となるので、それぞれをunion結合したものを、範囲外の期間とします。
26
26
  その期間に含まれるものを取得できたならエラーという事で。
27
27
  ```SQL

12

推敲

2019/03/08 07:38

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -50,7 +50,7 @@
50
50
  ) st inner join m_rireki ed
51
51
  on st.lead_st_ymd=ed.st_ymd
52
52
  ) step1
53
- where st_ymd!=next_st_ymd -- 隙間があるか？
53
+ where st_ymd!=next_st_ymd -- 隙間があるもの
54
54
  ) OverRange
55
55
  where st_ymd between 入力の開始 and 入力の終了 or ed_ymd between 入力の開始 and 入力の終了
56
56
  ```

11

推敲

2019/03/08 05:44

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -18,7 +18,7 @@
18
18
  --
19
19
  質問の意図を読み違えていました。
20
20
  入力した期間が有効な期間外であればエラーという事で、無効な期間に含まれる場合にエラーとします。
21
- 無効な期間としては、期間のマスタに対して、
21
+ 無効な期間としては、有効期限マスタに対して、
22
22
  ・最小の開始以前の期間
23
23
  ・有効な期間の隙間
24
24
  ・最大の終了以降の期間

10

補足

2019/03/08 05:42

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -18,9 +18,9 @@
18
18
  --
19
19
  質問の意図を読み違えていました。
20
20
  入力した期間が有効な期間外であればエラーという事で、無効な期間に含まれる場合にエラーとします。
21
- 無効な期間としては、
21
+ 無効な期間としては、期間のマスタに対して、
22
22
  ・最小の開始以前の期間
23
- ・指定された期間の隙間
23
+ ・有効な期間の隙間
24
24
  ・最大の終了以降の期間
25
25
  となるので、それぞれをunion結合したものを、範囲外の期間とします。
26
26
  その期間に含まれるものを取得できたならエラーという事で。

9

訂正

2019/03/08 05:30

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -52,5 +52,5 @@
52
52
  ) step1
53
53
  where st_ymd!=next_st_ymd -- 隙間があるか？
54
54
  ) OverRange
55
- where st_ymd 入力の開始 between 入力の終了 or ed_ymd 入力の開始 between 入力の終了
55
+ where st_ymd between 入力の開始 and 入力の終了 or ed_ymd between 入力の開始 and 入力の終了
56
56
  ```

8

訂正

2019/03/08 05:29

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -27,27 +27,30 @@
27
27
  ```SQL
28
28
  select *
29
29
  from (
30
+ -- 最小の期間外期間
30
31
  select '000000' as st_ymd
31
32
  , DATE_FORMAT(DATE_ADD(str_to_date(concat(min(st_ymd),'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as ed_ymd
32
33
  from m_rireki
34
+ -- 最大の期間外期間
33
35
  union all
34
36
  select DATE_FORMAT(DATE_ADD(str_to_date(concat(max(ed_ymd),'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as st_ymd
35
37
  ,'999999'
36
38
  from m_rireki
39
+ -- 有効期間の隙間期間
37
40
  union all
38
41
  select next_st_ymd, pre_ed_ymd
39
42
  from (
40
43
  select ed.*
41
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd
44
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd -- 連続していた場合の次の開始
42
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd
45
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd -- 連続していた場合の前の終了
43
46
  from (
44
47
  select r.*
45
- ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd
48
+ ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd -- 次の期間の開始
46
49
  from m_rireki r
47
50
  ) st inner join m_rireki ed
48
51
  on st.lead_st_ymd=ed.st_ymd
49
52
  ) step1
50
- where st_ymd!=next_st_ymd
53
+ where st_ymd!=next_st_ymd -- 隙間があるか？
51
54
  ) OverRange
52
55
  where st_ymd 入力の開始 between 入力の終了 or ed_ymd 入力の開始 between 入力の終了
53
56
  ```

7

訂正

2019/03/08 05:27

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -25,5 +25,29 @@
25
25
  となるので、それぞれをunion結合したものを、範囲外の期間とします。
26
26
  その期間に含まれるものを取得できたならエラーという事で。
27
27
  ```SQL
28
+ select *
29
+ from (
30
+ select '000000' as st_ymd
31
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(min(st_ymd),'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as ed_ymd
32
+ from m_rireki
33
+ union all
34
+ select DATE_FORMAT(DATE_ADD(str_to_date(concat(max(ed_ymd),'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as st_ymd
28
- --複数の期間に跨る場合の考慮もれ
35
+ ,'999999'
36
+ from m_rireki
37
+ union all
38
+ select next_st_ymd, pre_ed_ymd
39
+ from (
40
+ select ed.*
41
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd
42
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd
43
+ from (
44
+ select r.*
45
+ ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd
46
+ from m_rireki r
47
+ ) st inner join m_rireki ed
48
+ on st.lead_st_ymd=ed.st_ymd
49
+ ) step1
50
+ where st_ymd!=next_st_ymd
51
+ ) OverRange
52
+ where st_ymd 入力の開始 between 入力の終了 or ed_ymd 入力の開始 between 入力の終了
29
53
  ```

6

訂正

2019/03/08 05:26

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -25,5 +25,5 @@
25
25
  となるので、それぞれをunion結合したものを、範囲外の期間とします。
26
26
  その期間に含まれるものを取得できたならエラーという事で。
27
27
  ```SQL
28
- --複数の機関にまたがるばあいの考慮もれ
28
+ --複数の期間に跨る場合の考慮もれ
29
29
  ```

5

修正中

2019/03/08 05:22

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -25,32 +25,5 @@
25
25
  となるので、それぞれをunion結合したものを、範囲外の期間とします。
26
26
  その期間に含まれるものを取得できたならエラーという事で。
27
27
  ```SQL
28
- select *
29
- from (
30
- -- 最小の期間外期間
31
- select '000000' as st_ymd
32
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(min(st_ymd),'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as ed_ymd
33
- from m_rireki
34
- -- 最大の期間外期間
35
- union all
36
- select DATE_FORMAT(DATE_ADD(str_to_date(concat(max(ed_ymd),'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as st_ymd
37
- ,'999999'
28
+ --複数の機関にまたがるばあいの考慮もれ
38
- from m_rireki
39
- -- 有効期間の隙間期間
40
- union all
41
- select next_st_ymd, pre_ed_ymd
42
- from (
43
- select ed.*
44
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd -- 連続していた場合の次の開始
45
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd -- 連続していた場合の前の終了
46
- from (
47
- select r.*
48
- ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd -- 次の期間の開始
49
- from m_rireki r
50
- ) st inner join m_rireki ed
51
- on st.lead_st_ymd=ed.st_ymd
52
- ) step1
53
- where st_ymd!=next_st_ymd -- 隙間があるか？
54
- ) OverRange
55
- where [入力の開始] between st_ymd and ed_ymd or [入力の終了] between st_ymd and ed_ymd
56
29
  ```

4

コメント追加

2019/03/08 05:22

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -41,16 +41,16 @@
41
41
  select next_st_ymd, pre_ed_ymd
42
42
  from (
43
43
  select ed.*
44
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd
44
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd -- 連続していた場合の次の開始
45
- , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd
45
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd -- 連続していた場合の前の終了
46
46
  from (
47
47
  select r.*
48
- ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd
48
+ ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd -- 次の期間の開始
49
49
  from m_rireki r
50
50
  ) st inner join m_rireki ed
51
51
  on st.lead_st_ymd=ed.st_ymd
52
52
  ) step1
53
- where st_ymd!=next_st_ymd
53
+ where st_ymd!=next_st_ymd -- 隙間があるか？
54
54
  ) OverRange
55
55
  where [入力の開始] between st_ymd and ed_ymd or [入力の終了] between st_ymd and ed_ymd
56
56
  ```

3

追記

2019/03/08 05:15

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -12,4 +12,45 @@
12
12
  ```SQL
13
13
  select count(*) from m_rireki
14
14
  where st_ymd<=[入力での開始] and ed_ymd>=[入力での終了]
15
+ ```
16
+
17
+ 追記
18
+ --
19
+ 質問の意図を読み違えていました。
20
+ 入力した期間が有効な期間外であればエラーという事で、無効な期間に含まれる場合にエラーとします。
21
+ 無効な期間としては、
22
+ ・最小の開始以前の期間
23
+ ・指定された期間の隙間
24
+ ・最大の終了以降の期間
25
+ となるので、それぞれをunion結合したものを、範囲外の期間とします。
26
+ その期間に含まれるものを取得できたならエラーという事で。
27
+ ```SQL
28
+ select *
29
+ from (
30
+ -- 最小の期間外期間
31
+ select '000000' as st_ymd
32
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(min(st_ymd),'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as ed_ymd
33
+ from m_rireki
34
+ -- 最大の期間外期間
35
+ union all
36
+ select DATE_FORMAT(DATE_ADD(str_to_date(concat(max(ed_ymd),'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as st_ymd
37
+ ,'999999'
38
+ from m_rireki
39
+ -- 有効期間の隙間期間
40
+ union all
41
+ select next_st_ymd, pre_ed_ymd
42
+ from (
43
+ select ed.*
44
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd
45
+ , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd
46
+ from (
47
+ select r.*
48
+ ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd
49
+ from m_rireki r
50
+ ) st inner join m_rireki ed
51
+ on st.lead_st_ymd=ed.st_ymd
52
+ ) step1
53
+ where st_ymd!=next_st_ymd
54
+ ) OverRange
55
+ where [入力の開始] between st_ymd and ed_ymd or [入力の終了] between st_ymd and ed_ymd
15
56
  ```

2

追記

2019/03/08 05:09

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -4,5 +4,12 @@
4
4
 
5
5
  なので、以下のSQLで問い合わせて結果が返却されなければ、期間外という事になります。
6
6
  ```SQL
7
+ select count(*) from m_rireki
7
- select * from m_rireki where st_ymd<=[入力での開始] and ed_ymd>=[入力での終了]
8
+ where st_ymd<=[入力での開始] and ed_ymd>=[入力での終了]
9
+ having count(*)=1
10
+ ```
11
+ 若しくは、HITする件数=1の場合のみOKとする
12
+ ```SQL
13
+ select count(*) from m_rireki
14
+ where st_ymd<=[入力での開始] and ed_ymd>=[入力での終了]
8
15
  ```

1

推敲

2019/03/08 03:07

投稿

sazi
sazi

スコア25430

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