質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

ASP

ASP(Active Server Pages) あるいはClassic ASP (ASP Classic)は、マイクロソフト社開発した動的なウェブページ製作に利用可能なサーバー側のスプリクトエンジンです。

Q&A

解決済

4回答

1707閲覧

期間持ちのデータ(空白期間あり)内で有効な期間かどうかをチェックする方法

smaeda

総合スコア16

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

ASP

ASP(Active Server Pages) あるいはClassic ASP (ASP Classic)は、マイクロソフト社開発した動的なウェブページ製作に利用可能なサーバー側のスプリクトエンジンです。

0グッド

0クリップ

投稿2019/03/07 09:35

編集2019/03/08 03:08

前提・実現したいこと

初めて投稿させて頂きます。

Classic ASP + MySQLにてシステムを作成しております。
下記のようなマスタテーブルに、期間持ちのデータが存在しています。
ID1~ID2のように、空白期間が存在する場合があります。
ID2~ID3のように、2レコードに分かれていたとしても一つの期間として扱いたい場合もあります

■有効期限マスタ

SQL

1CREATE TABLE m_rireki ( 2 id INT NOT NULL PRIMARY KEY, 3 st_ymd VARCHAR(6), 4 ed_ymd VARCHAR(6) 5); 6 7insert into m_rireki 8(id,st_ymd,ed_ymd) values 9(1,'201801','201805'), 10(2,'201807','201809'), 11(3,'201810','201812'); 12

画面で入力された期間の値が、有効な期間内かどうかチェックを行いたいです。

■入力例
2017/11~2018/2 → 無効
2018/1~2018/4 → 有効
2018/4~2018/8 → 無効
2018/8~2018/12 → 有効

一応考えついた方法としては
1.画面で入力された開始期間以前、かつマスタの開始月が一番大きいデータを取得し、開始年月と終了年月をそれぞれ変数に保持。
2.取得したデータの終了年月+1した値と、開始年月が一致するマスタデータを取得する
→データが存在すれば、終了年月を変数で保持し、再び「2」の工程を繰り返し
→データが存在しなければ、「3」へ
3.変数に保持してある開始年月と終了年月の期間内に、画面で入力された期間が収まるかをチェック

と、いうものなのですが、とにかくASPコードが冗長になってしまうため
もう少しシンプルな方法があれば・・・と考えております。
SQLのみでチェック出来ればと思ったのですが、残念ながら良い方法が思い浮かびませんでした。

皆様のお知恵をお借り出来ればと思います。
どうぞよろしくお願いいたします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

mather

2019/03/07 09:56

> 2018/8~2015/12 → 有効 こちらは記載ミスですか?
Orlofsky

2019/03/07 10:26

>2018/8~2015/12 → 有効 2015? SQLで解決したいなら、質問にCREATE TABLEとテーブルに存在するデータをINSERT文で提示すると適切なコメントが付き易いです。
smaeda

2019/03/08 02:29

申し訳ありません。記入ミスがございました。 2018/8~2015/12 → 2018/8~2018/12 また、アドバイス頂きありがとうございました。 CREATE TABLEとINSERT文を追加いたしました。
guest

回答4

0

カレンダーテーブルを予め作っておくとよいかも

SQL

1create table m_rireki ( 2 id int primary key, 3 st_ymd varchar(6), 4 ed_ymd varchar(6) 5); 6 7insert into m_rireki 8(id,st_ymd,ed_ymd) values 9(1,'201801','201805'), 10(2,'201807','201809'), 11(3,'201810','201812'); 12 13/* 予め想定される100年分くらいのデータを入れておく*/ 14create table all_month(ymd varchar(6) primary key); 15insert into all_month values 16('201701'),('201702'),('201703'),('201704'),('201705'),('201706'), 17('201707'),('201708'),('201709'),('201710'),('201711'),('201712'), 18('201801'),('201802'),('201803'),('201804'),('201805'),('201806'), 19('201807'),('201808'),('201809'),('201810'),('201811'),('201812'), 20('201901'),('201902'),('201903'),('201904'),('201905'),('201906'), 21('201907'),('201908'),('201909'),('201910'),('201911'),('201912');
  • 検索

SQL

1/* 201711~2018/2 → 無効 */ 2select if( 3(select count(*) from all_month as t2 4where ymd between '201711' and '201802')> 5(select count(*) from all_month as t1 6where exists(select 1 from m_rireki where t1.ymd between st_ymd and ed_ymd) 7and ymd between '201711' and '201802' 8),'無効','有効') as 判定; 9 10/* 2018/1~2018/4 → 有効 */ 11select if( 12(select count(*) from all_month as t2 13where ymd between '201801' and '201804')> 14(select count(*) from all_month as t1 15where exists(select 1 from m_rireki where t1.ymd between st_ymd and ed_ymd) 16and ymd between '201801' and '201804' 17),'無効','有効') as 判定; 18 19/* 2018/4~2018/8 → 無効 */ 20select if( 21(select count(*) from all_month as t2 22where ymd between '201804' and '201808')> 23(select count(*) from all_month as t1 24where exists(select 1 from m_rireki where t1.ymd between st_ymd and ed_ymd) 25and ymd between '201804' and '201808' 26),'無効','有効') as 判定; 27 28/* 2018/8~2018/12 → 有効 */ 29select if( 30(select count(*) from all_month as t2 31where ymd between '201808' and '201812')> 32(select count(*) from all_month as t1 33where exists(select 1 from m_rireki where t1.ymd between st_ymd and ed_ymd) 34and ymd between '201808' and '201812' 35),'無効','有効') as 判定; 36

※おそらくもう少し効率的な書き方があるとは思います。

投稿2019/03/08 03:38

編集2019/03/08 03:39
yambejp

総合スコア114585

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

smaeda

2019/03/11 01:28

ご回答を頂きましてありがとうございました。 今回はSQLのみで解決ができるsaziさんのご回答をベストアンサーにさせて頂こうと思います。
guest

0

ベストアンサー

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

なので、以下のSQLで問い合わせて結果が返却されなければ、期間外という事になります。

SQL

1select count(*) from m_rireki 2where st_ymd<=[入力での開始] and ed_ymd>=[入力での終了] 3having count(*)=1

若しくは、HITする件数=1の場合のみOKとする

SQL

1select count(*) from m_rireki 2where st_ymd<=[入力での開始] and ed_ymd>=[入力での終了]

追記

質問の意図を読み違えていました。
入力した期間が有効な期間外であればエラーという事で、無効な期間に含まれる場合にエラーとします。
無効な期間としては、有効期限マスタに対して、
・最小の開始未満の期間
・有効な期間の隙間
・最大の終了超過の期間
となるので、それぞれをunion結合したものを、範囲外の期間とします。
その期間に含まれるものを取得できたならエラーという事で。

SQL

1select count(*) 2from ( 3 -- 最小の期間外期間 4 select '000000' as st_ymd 5 , DATE_FORMAT(DATE_ADD(str_to_date(concat(min(st_ymd),'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as ed_ymd 6 from m_rireki 7 -- 最大の期間外期間 8 union all 9 select DATE_FORMAT(DATE_ADD(str_to_date(concat(max(ed_ymd),'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as st_ymd 10 ,'999999' 11 from m_rireki 12 -- 有効期間の隙間期間 13 union all 14 select next_st_ymd, pre_ed_ymd 15 from ( 16 select ed.* 17 , DATE_FORMAT(DATE_ADD(str_to_date(concat(st.ed_ymd,'01'), '%Y%m%d'), INTERVAL 1 MONTH), '%Y%m') as next_st_ymd -- 連続していた場合の次の開始 18 , DATE_FORMAT(DATE_ADD(str_to_date(concat(ed.st_ymd,'01'), '%Y%m%d'), INTERVAL -1 MONTH), '%Y%m') as pre_ed_ymd -- 連続していた場合の前の終了 19 from ( 20 select r.* 21 ,(select min(st_ymd) from m_rireki where r.ed_ymd<st_ymd) lead_st_ymd -- 次の期間の開始 22 from m_rireki r 23 ) st inner join m_rireki ed 24 on st.lead_st_ymd=ed.st_ymd 25 ) step1 26 where st_ymd!=next_st_ymd -- 隙間があるもの 27) OverRange 28where st_ymd between 入力の開始 and 入力の終了 or ed_ymd between 入力の開始 and 入力の終了

投稿2019/03/08 02:55

編集2019/03/09 07:24
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

smaeda

2019/03/08 03:02

ご回答頂きありがとうございます。 お教え頂いたSQLですと「2018/8~2018/12」のように 2レコードをまたぐ期間の場合、無効となってしまうようです。 1レコードならば、これで大丈夫なのですが・・・
sazi

2019/03/08 03:08

訂正および追記しました。
sazi

2019/03/08 03:32

追記された内容でようやく理解できました。 設定のない空きの期間を含んでいる場合はエラーという事ですね。
smaeda

2019/03/11 01:29

素晴らしいSQLをありがとうございます。 思い通りの動きになりそうです。大変勉強になりました。
guest

0

簡単なSQLで、

SQL

1SELECT * 2FROM m_rireki 3WHERE '201711' BETWEEN st_ymd AND ed_ymd 4AND '201805' BETWEEN st_ymd AND ed_ymd ; 5対象データなし 6 7SELECT * 8FROM m_rireki 9WHERE '201801' BETWEEN st_ymd AND ed_ymd 10AND '201804' BETWEEN st_ymd AND ed_ymd ; 11対象データあり

投稿2019/03/08 02:49

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

smaeda

2019/03/08 02:58

ご回答頂きありがとうございます。 お教え頂いたSQLですと「2018/8~2018/12」のように 2レコードをまたぐ期間の場合、無効となってしまうようです。
Orlofsky

2019/03/08 03:11

'201807'~'201809' '201810'~'201812' って有効期限なら '201807'~'201812' の1行にまとめたデータを追加しても良いのでは?
smaeda

2019/03/11 00:45

申し訳ありません。 例の場合では省略したのですが、もうひとつキーとなるカラムがあるため1行にまとめる事は出来ないのです。
Orlofsky

2019/03/11 00:56

必要な情報はきちんと質問に提示しないとアドバイスはできません。
guest

0

単純に、入力された開始年月と終了年月を包括するデータが有効期限マスタに存在すれば「有効」存在しなければ「無効」でいいように思いますが。

投稿2019/03/07 10:48

cerfweb

総合スコア1899

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

smaeda

2019/03/08 02:31

申し訳ありませんが、包括と言いますとどういった意味でしょうか? GROUP化するという事でしょうか・・・?
cerfweb

2019/03/08 08:13

わかりにくい表現でしたね。すみません。 簡単に言うと、開始年月以降終了年月以内のデータということですが、「2レコードに分かれていたとしても一つの期間として扱いたい」というのには単純には対応できないですね。またいい案があったら追記します。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問