No18086.勤務時間をSQL文で抽出する方法

OS:XP pro
Access Version: 2000

初めて質問させていただきます。
現在深夜勤務時間をSQL文で抽出する方法で苦戦しています。ご教授下さい。

深夜勤務時間を22：00〜翌5：00だとします。
15分単位での計算とします。
その時間帯に該当する時間を求めたいと思っています。
現在下記までSQL文で完成しました。

出勤時刻 　 | 退勤時刻　　　|残業開始時刻 |残業終了時間 |合計(分)
----------------+----------------+----------------+----------------+-------------
2004/01/31 17:12|2004/02/02 0:53 |2004/01/31 22:00|2004/02/02 00:45|1605 ※
2004/01/21 4:00 |2004/01/21 23:00|2004/01/21 04:00|2004/01/21 23:00|1140 ※
2004/01/21 22:00|2004/01/22 5:00 |2004/01/21 22:00|2004/01/22 05:00|420
2004/01/21 22:45|2004/01/22 6:00 |2004/01/21 22:45|2004/01/22 05:00|375
2004/01/20 23:47|2004/01/21 6:48 |2004/01/21 00:00|2004/01/21 05:00|300
2004/01/29 16:47|2004/01/30 2:51 |2004/01/29 22:00|2004/01/30 02:45|285
2004/02/03 14:57|2004/02/04 2:09 |2004/02/03 22:00|2004/02/04 02:00|240
2004/01/20 15:48|2004/01/21 1:53 |2004/01/20 22:00|2004/01/21 01:45|225
2004/01/21 2:04 |2004/01/21 5:56 |2004/01/21 02:15|2004/01/21 05:45|210

【方法】
出勤時間と退勤時間よりそれぞれ深夜開始時間と深夜終了時間を求める。
その後、datediffで差異を求め、合計（分）を出す。

とするとここで問題が発生しました。
1、2行目の出勤時間と退勤時間の場合です。
勤務のパターンとしては下記が挙げられると考えています。が問題は�Dと�Eの場合です。
0:00 22:00 5:00 22:00 5:00 22:00 5:00
+--------+=======+-----------------+=======+-----------+=========+
�@ |----|
�A |---------|
�B |-------------|
�C |-------------|
�D |----------------------|
�E |-------------------------------------------------------|

このような労働条件なんて辛くてありえないとは思いますが、その間
に休憩という形で数時間の休みが入っています。
�Dと�Eの場合は深夜以外の時間も合計してしまいます。
�Dの場合は表の2行目のパターンなので2時間
�Eの場合は表の１行目のパターンなので9時間45分
下記SQL文で取り出しているのですが、�D・�Eについても22：00〜5：0
0の時間だけを取り出す方法を教えいていただけないでしょうか。

SELECT
出勤時刻,
退勤時刻,
IIf((22<=Hour([出勤時刻]) And 24>=Hour([出勤時刻])) Or
(0<=Hour([出勤時刻]) And 5>=Hour([出勤時刻])),
Format(DateAdd("n",IIf(Format([出勤時刻],"nn") Mod 15=0,0,15-(Format([出勤時刻],"nn") Mod 15)),
[出勤時刻]),"yyyy/mm/dd hh:nn"),
Format([出勤時刻],"yyyy/mm/dd"" 22"":""00""")) AS 残業開始時刻,
IIf((22<=Hour([退勤時刻]) And 24>=Hour([退勤時刻])) Or
(0<=Hour([退勤時刻]) And 5>=Hour([退勤時刻])),
Format(DateAdd("n",-(Format([退勤時刻],"nn") Mod 15),[退勤時刻]),"yyyy/mm/dd hh:nn"),
Format([退勤時刻],"yyyy/mm/dd"" 05"":""00""")) AS 残業終了時間,
DateDiff("n",IIf((22<=Hour([出勤時刻]) And 24>=Hour([出勤時刻])) Or
(0<=Hour([出勤時刻]) And 5>=Hour([出勤時刻])),
Format(DateAdd("n",IIf(Format([出勤時刻],"nn") Mod 15=0,0,15-(Format([出勤時刻],"nn") Mod 15))
　　,[出勤時刻]),"yyyy/mm/dd hh:nn"),Format([出勤時刻],"yyyy/mm/dd"" 22"":""00""")),
IIf((22<=Hour([退勤時刻]) And 24>=Hour([退勤時刻])) Or
(0<=Hour([退勤時刻]) And 5>=Hour([退勤時刻])),
Format(DateAdd("n",-(Format([退勤時刻],"nn") Mod 15),[退勤時刻]),"yyyy/mm/dd hh:nn"),
Format([退勤時刻],"yyyy/mm/dd"" 05"":""00"""))) AS 合計(分)
FROM
TEST_TABLE;

> 出勤時刻 　 | 退勤時刻　　　|残業開始時刻 |残業終了時間 |合計(分)
> ----------------+----------------+----------------+----------------+-------------
> 2004/01/31 17:12|2004/02/02 0:53 |2004/01/31 22:00|2004/02/02 00:45|1605 ※
> 2004/01/21 4:00 |2004/01/21 23:00|2004/01/21 04:00|2004/01/21 23:00|1140 ※
> 2004/01/21 22:00|2004/01/22 5:00 |2004/01/21 22:00|2004/01/22 05:00|420
> 2004/01/21 22:45|2004/01/22 6:00 |2004/01/21 22:45|2004/01/22 05:00|375
> 2004/01/20 23:47|2004/01/21 6:48 |2004/01/21 00:00|2004/01/21 05:00|300
> 2004/01/29 16:47|2004/01/30 2:51 |2004/01/29 22:00|2004/01/30 02:45|285
> 2004/02/03 14:57|2004/02/04 2:09 |2004/02/03 22:00|2004/02/04 02:00|240
> 2004/01/20 15:48|2004/01/21 1:53 |2004/01/20 22:00|2004/01/21 01:45|225
> 2004/01/21 2:04 |2004/01/21 5:56 |2004/01/21 02:15|2004/01/21 05:45|210

単純に合計が　5：00〜22：00までの17時間(1020分）を超えたら
その分引いてやる。
　1605-1020＝585　(9時間45分)
　1140-1020＝120　(2時間)
では イカンかなぁ。

0:00 22:00 5:00 22:00 5:00 22:00 5:00
+--------+=======+-----------------+=======+-----------+=========+
(6) |-------------------------------------------------------|
こんな長時間勤務があるのなら
同様に　深夜勤務が 17+7+17＝41時間 以上ならその分を引くとか

> では イカンかなぁ。

きっと早死に するね。

> 0:00 22:00 5:00 22:00 5:00 22:00 5:00
> +--------+=======+-----------------+=======+-----------+=========+
> (6) |-------------------------------------------------------|
> こんな長時間勤務があるのなら
> 同様に　深夜勤務が 17+7+17＝41時間 以上ならその分を引くとか
>
> > では イカンかなぁ。
>
> きっと早死に するね。


Benkeiさん

ねこです。回答ありがとうございます。

>単純に合計が　5：00〜22：00までの17時間(1020分）を超えたら
>その分引いてやる。
>　1605-1020＝585　(9時間45分)
>　1140-1020＝120　(2時間)

そして、早死にするぐらい長時間勤務する場合は
> 同様に　深夜勤務が 17+7+17＝41時間 以上ならその分を引くとか

ですね。こういう方法があることを思いつきませんでした。

確かにこんな勤務はないと思いますが、休憩時間が別にあるので後でを減らそうと考えています。
(ここをどうやるかは今後の課題ですが。。)

イメージ的には長期出張にでかけて1出張でまとめて勤務時間を管理するという具合です。
本当に働いた時間だけを１データとして持っていればいいのですが、そうではないようです。
出張期間：1/1 9:00〜1/3 23:00
OFF ：1/1 23:00〜1/2 9:00
OFF : 1/2 17:00〜1/3 3:00
です。

この方法でやってみて、できなければまた助けてください。

今晩は。
面白そうな問題なので、ちょっと参加。
> 現在深夜勤務時間をSQL文で抽出する方法で苦戦しています。
SQL文中だけで閉じさせようとすると、IIf()の嵐になりそうなので、
ユーザー定義関数を使用する方法で考えてみました。

Public Function RangeTime( _
StartTime As Date _
, EndTime As Date _
, StartRange As Date _
, EndRange As Date _
) As Date
Dim StartDay As Date, EndDay As Date
Dim AdjustEndRange As Date, TargetDay As Date
Dim CulcTime As Date

StartDay = Format(StartTime, "yyyy/mm/dd")
EndDay = Format(EndTime + (Format(EndTime, "hh:mm") = "00:00"), "yyyy/mm/dd")
If StartRange > EndRange Then '範囲が日を跨ぐ場合の調整
CulcTime = DailyRangeTime(StartTime, EndTime, StartDay, StartDay + EndRange)
AdjustEndRange = EndRange + 1
Else
CulcTime = 0
AdjustEndRange = EndRange
End If
For TargetDay = StartDay To EndDay '日が変わる毎に範囲内の時間を加算
CulcTime = CulcTime _
+ DailyRangeTime(StartTime, EndTime, TargetDay + StartRange, TargetDay + AdjustEndRange)
Next
RangeTime = CulcTime
End Function

Public Function DailyRangeTime( _
StartTime As Date _
, EndTime As Date _
, StartRange As Date _
, EndRange As Date _
) As Date
If StartTime > EndRange Or StartRange > EndTime Then
DailyRangeTime = 0
Else
DailyRangeTime = IIf(EndTime < EndRange, EndTime, EndRange) _
- IIf(StartTime > StartRange, StartTime, StartRange)
End If

End Function
上記関数を定義し、SQLで使用します。

> 深夜勤務時間を22：00〜翌5：00だとします。
> 15分単位での計算とします。
勤怠を15分単位にして深夜残業時間を求めるのか、
残業時間を15分単位にするのか分かりませんので、
※後者なら、深夜残業時間帯に複数回作業している場合、
　合算値を15分単位にするのか、15分単位にしてから合算するのかも不明
SQL上での使い方をサンプルしておきます。

SELECT 出勤時刻
, 退勤時刻
, RangeTime(出勤時刻, 退勤時刻, CDate("22:00"), CDate("05:00")) AS 残業時間
, DateDiff("n", 0, RangeTime(出勤時刻, 退勤時刻, CDate("22:00"), CDate("05:00"))) AS [合計(分)]
FROM TEST_TABLE;

※休憩時間についても、時間帯があるなら上記関数で同様に求めることができます。

銀の匙さん。

ねこです。こんにちは。
で、できました♪
銀の匙より教えていただいた方法ですべての問題が解決しました。
ありがとうございました。

当初の質問が解決したので本来なら解決マークを入れるべきですが、少し発展させて質問させていただければと思います。

実はデータベースはAccessではなく、SQLServerなのです。
とりあえずAccessで作成してそれを移植と考えていました。

やりたいことはEXEを実行してSQLServerよりデータを取得た後、EXCELファイルに書込むです。

もしご存知ならでかまわないのですが、相手がSQLServerの場合教えていただいたユーザー定義関数は同じようにSQLServerのユーザー定義関数に定義すればよろしいのでしょうか？
(もちろんSQLServerの文法に変換して)

もし、質問する場所が違っているということであればご了承下さい。

> 実はデータベースはAccessではなく、SQLServerなのです。
> とりあえずAccessで作成してそれを移植と考えていました。
>
> もしご存知ならでかまわないのですが、相手がSQLServerの場合教えていただいたユーザー定義関数は
> 同じようにSQLServerのユーザー定義関数に定義すればよろしいのでしょうか？
> (もちろんSQLServerの文法に変換して)
SQL-Serverは詳しくありませんが、ストアドとして定義すれば実現可能だと思います。
但し、日付に関して演算やループ等、VB系の言語特性にかなり依存した記述だと思いますので、
単純な移植にはならない気がします。

Works - 逆引きSQL比較
http://homepage1.nifty.com/kojama/works/rdbms/jet/sql.html#fterm02
#参考になるかどうか分かりませんが。。

>
> やりたいことはEXEを実行してSQLServerよりデータを取得た後、EXCELファイルに書込むです。
#EXEがVB系なら、ADOとかで移植せずに何とかならないかな〜？
#まぁ、選択肢の話ですし、検討するなら新たなスレッドでお願いします。

銀の匙さん。

ねこです。本当に何から何までありがとうございました。

http://homepage1.nifty.com/kojama/works/rdbms/jet/sql.html#fterm02
拝見しました。今後も役に立ちそうな内容なのでぜひ参考にさせていただきます。



Benkeiさん。
親切にありがとうございました。

色々役立つ情報を頂きまして助かりました。
またお邪魔させていただきます。