MySQL 複数の日付範囲の検索方法

Question

前提・実現したいこと

お世話になります。MySQLでsalariesテーブルの2020年に支払われたsalaryの合計値を出したいと考えています。

salariesテーブル

salary(INT)	from_date(DATE)	to_date(DATE)
60000	2020-01-01	2020-01-31
40000	2020-03-01	2020-03-31
50000	2019-01-01	2019-01-01

例えばfrom_date1つだけで検索するとなると下記でできると認識しています。

SELECT sum(salaries.salary)
FROM salaries 
WHERE salaries.from_date >= "2020-01-01" 
AND salaries.from_date <= "2020-12-31";

しかし今回のように複数の日付範囲から指定する場合、どのように取得すればよいか悩んでおります。
恐れ入りますがご教示いただきたく存じます。

Answer 1

業務上の問題としてとらえた場合、これは、SQLの問題ではなく、仕様の問題です。
「2020年に支払われたsalary」を、もう少し正確に決めましょう。
可能性としては、以下など、幾つかが考えられます。
なお、前提として、from_dateとto_dateは、salaryの計算根拠の労務期間の始終日と仮定します。

・支払日が2020年のsalary
この場合、from_dateもto_dateも関係なく、支払日のデータが必要になります。

・経理上の給与年月が、2020年1月から12月のsalary
この場合、from_dateとto_dateから、給与年月を導く式が必要となります。

・労務期間が1日でも2020年に含まれるsalary
この場合、以下の条件になります。
'2020-01-01' <= to_date and '2020-12-31' <= from_date

・労務期間が2020年に相当する分のsalary
これは、日単位で考える方法で、按分の方法などを別途定める必要があります。

これ以外にも、考えられるかもしれません。

なので、まず、hasayaさんの言う「2020年に支払われたsalary」の正確な意味を決定してください。

Answer 2

複数の日付範囲から指定する場合

が、実際は「 年を跨ぐ日付範囲から指定する場合 」であった場合。

パラメータ:

p1	p2
2020-01-01	2020-12-31

テーブル: 対象範囲

_from	_to
2018-01-01	2019-12-31
2019-09-01	2020-03-31
2020-05-01	2020-08-31
2020-11-01	2021-02-28
2019-01-01	2021-01-31
2021-04-01	2021-08-31

だったとします。

パラメータp1 .. p2が
対象範囲_from .. _toに一部でも内在するかどうかを判定するには
以下のように記述します。

SELECT * 
  FROM 対象範囲 t
 WHERE t._to   >= date '2020-01-01' 
   AND t._from <= date '2020-12-31'
;

■結果

_from	_to
2019-09-01	2020-03-31
2020-05-01	2020-08-31
2020-11-01	2021-02-28
2019-01-01	2021-01-31

Answer 3

そもそも運用の問題では？
年をまたがる日付の設定をすると2020年が一部だけ含まれる
支払いが表現できなくなるので設定自体が無意味でしょう
したがってto_dateの年月はfrom_dateと同じという前提で組んで

from_date between '2020-01-01' and '2020-12-31'とするか
集計を前提にdate_formatしておいた生成列をつくっておけばよいでしょう

create table tbl(id int primary key,
salary int,from_date date,to_date date,
from_y int as (date_format(from_date,'%Y')),
index(from_y)
);
insert into tbl(id,salary,from_date,to_date) values
(1,60000,'2020-01-01','2020-01-31'),
(2,40000,'2020-03-01','2020-03-31'),
(3,50000,'2019-01-01','2019-01-01');

select * from tbl where from_y=2020

Answer 4

()で括った上でorでつなぐとかどうでしょう

( 
salaries.from_date >= "2020-01-01" 
AND salaries.from_date <= "2020-12-31"
)
or
( 
salaries.from_date >= "2021-06-01" 
AND salaries.from_date <= "2021-12-31"
)

between使ったほうが短くなりそうですけどね。

Comments

[hasaya]

コメントありがとうございます！
すみません私の質問文がおかしくなってました。
今回from_dateとto_dateの2つのカラムが存在しており、例えば2020年のsalaryの合計値を出したいとする場合、2つのカラムを考慮する必要があると思うのですがその場合にどういうSQLにするのかを知りたいというのが正しい質問でした。

[m.ts10806]

fromからtoで月をまたぐケースはないですか？
いずれにしても当年の実績なわけですから元々toを考慮する必要なく、fromを年だけ抽出するフォーマットかけた上でイコールにすれば良さそうに思います。