SQL文による残業時間の算出について

質問

MySQLにて、勤怠プログラムを作成しているのですが、例えば出勤時間と退勤時間をそれぞれdatetime型で保持していたとして、「1ヶ月間の勤務時間の合計を算出」と言った形であれば
SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF( '退勤時間', '出勤時間' )))) AS WORK_TIME FROM 'テーブル名'
のような形で算出できるかと思いますが、例えば、「1ヶ月間の残業時間の合計を算出」といったことはSQL文のみで実現可能なのでしょうか？ちなみに残業時間の定義は「平日の18時以降、土日出勤時間は除く」となります。
よろしくお願いします。

回答No.1

yambejp · ベストアンサー

>土日出勤時間は除く

土日のような処理は危険、土日が営業日のこともあるでしょうし、
土日以外が祝日でやすみの場合もあるでしょう。
そういうときのため会社自体の営業日をベースに処理していいなら
営業日テーブルをつくっておくと便利です。
（個人ごとに出勤シフトが違う場合はこまかい調整が必要ですが）

以下サンプル

/* 勤怠テーブル作成 */

create table 勤怠(
社員ID varchar(10) not null,勤務日 date not null,出社 time,退社 time
,primary key (社員ID,勤務日)
);
insert into 勤怠(社員ID,勤務日,出社,退社) values(
'10000','2011-01-01','9:30','12:00'),(
'10000','2011-01-02','9:30','12:00'),(
'10000','2011-01-03','9:30','12:00'),(
'10000','2011-01-04','9:30','12:00'),(
'10000','2011-01-05','9:30','18:00'),(
'10000','2011-01-06','9:30','18:10'),(
'10000','2011-01-07','9:30','18:20'),(
'10000','2011-01-08','9:30','18:30'),(
'10000','2011-01-09','9:30','18:10'),(
'10000','2011-01-10','9:30','18:20'),(
'10000','2011-01-11','9:30','18:30'),(
'10000','2011-01-12','9:30','18:10'),(
'10000','2011-01-13','9:30','18:20'),(
'10000','2011-01-14','9:30','18:30');

/* 営業日テーブル作成 1,2,3,10は祝日、8,9は土日*/

create table 営業日(
営業日 date not null primary key);
insert into 営業日 values(
'2011-01-04'),(
'2011-01-05'),(
'2011-01-06'),(
'2011-01-07'),(
'2011-01-11'),(
'2011-01-12'),(
'2011-01-13'),(
'2011-01-14');

/* 平日の残業時間を集計 */

select sum(time_to_sec(timediff(退社,'18:00') * (退社>'18:00')))
from 勤怠
INNER JOIN 営業日 ON 営業日=勤務日
where 1
AND 社員ID='10000'
AND 勤務日 BETWEEN '2011-01-01' AND '2011-01-31';

お礼

ご連絡が遅くなり申し訳ございません。
ご回答頂きどうもありがとうございました。
とても参考になりました。