Access2010で時間の計算方法

アルバイトの就業時間をアクセスで管理しております。

午前10：00～翌日朝6:00まで営業しております。
やりたいことは、夜22:00～翌朝の29:00（朝5時）までの時間を月ごとに集計をして
誰が深夜何時間働いているかを調べたいです。
22時から29時は時間給が高くなるのです。

タイムカードは、出社時と退社時に押します。（15分単位で切り捨てたいです。）

氏名　　　日付　　　　出社　　退社　　　調べたい時間
Aさん　　10月10日　20:00～25:20　　3時間15分
Bさん　　10月10日　21:00～29:10　　7時間
Aさん　　10月11日　22:00～26:05　　4時間
Bさん　　10月13日　18:13～28:23　　6時間15分
Aさん　　10月14日　21:16～30:00　　7時間

22:00～29:00までの時間を調べて、その月での集計をしたいです。

Aさん　14時間15分
Bさん　13時間15分

クエリの集計機能が使えるのかと思ったのですが、時間の計算ができませんでした。

説明不足な点も多々あるかと思いますが、ご教授頂けたら幸いです。
よろしくお願いいたします。

No.6 ベストアンサー 回答者： 30246kiku 回答日時： 2013/10/17 21:06

＃３です

その後、いろいろと検証等されていると思います。
元テーブルが

氏名　　日付　　　　出社　　　退社
Aさん　2013/10/10　20:00:00　1:20:00
Cさん　2013/10/15　 1:20:00　7:00:00
Dさん　2013/10/15　 9:20:00　17:30:00

だった場合、

氏名　　日付　　　　出社　　　退社　　時間
Aさん　2013/10/10　20:00:00　1:20:00　3.25
Cさん　2013/10/15　 1:20:00　7:00:00　3.5
Dさん　2013/10/15　 9:20:00　17:30:00　0
（1:20 + 2:00 = 3:20 の 15分単位切り捨て 3:15 → 3.25）
（5:00 - 1:20 = 3:40 の 15分単位切り捨て 3:30 → 3.5）

とするには、クエリの書き方を工夫すれば良いと思います。
例えば、

SELECT 氏名, 日付, 出社, 退社,
CalcNightTimes(日付+出社,日付+退社-(出社>退社)) AS 時間
FROM テーブル名;

出社>退社　なら１日加算する方法ですが、
出社>退社　の比較で True なら -1 になるので、
日付+退社-(出社>退社)　で、結果１日加算される事になります。

＃３では、出社・退社とも埋まっているものとして動くので、クエリ上で

SELECT 氏名, 日付, 出社, 退社,
CalcNightTimes(日付+出社,日付+退社-(出社>退社)) AS 時間
FROM テーブル名
WHERE 出社 Is Not Null AND 退社 Is Not Null;

と条件指定しても良いかも（元々 Null が無ければ不要ですが）
関数側で Null をはじく様に修正しても良いと思います。

※ 営業時間と勤務時間は違いますよね？


過剰情報になりますが、

TRANSFORM Sum(CalcNightTimes(日付+出社,日付+退社-(出社>退社))) AS 値
SELECT 氏名
FROM テーブル名
GROUP BY 氏名
PIVOT Format(日付,"yyyy/mm");

とすると、クロス集計で深夜時間合計が表示されます。



補足）＃３での VBA 記述の説明をしておきます。

22:00 ～ 翌 5:00 ・・・・
これ、考えにくいので、与えられた dtS / dtE 共に 2:00 時間加算しておく事に・・・
これにより、深夜時間帯は 0:00 ～ 7:00 に偏らせる事が出来ます。

その細工をしておいて、
dtS / dtE が同じ日なら、
・ 0:00 ～ 7:00 に dtE が入っていたら、dtE - dtS が深夜時間
・ 0:00 ～ 7:00 に dtS が入っていたら、7:00 - dtS（時刻部分）が深夜時間
dtS / dtE が同じ日でなかったら、
・ 0:00 ～ 7:00 に dtS が入っていたら、7:00 - dtS（時刻部分）が朝方の深夜時間
・ dtE - Int(dtE) ・・・ 0:00 からの差を覚えておいて、それが、7:00 以上なら 7:00 に訂正
・ 上記、朝方と日付が変わった分を加算したものが深夜時間


※ 一見、＃３で大丈夫そうに見えたりしますが、

　　　　wdt = dtE - Int(dtE)

部分でおかしくなっていく事があります。その条件は、
dtS / dtE が同じ日でなかったら・・・・　の場合で、日付差が 1 を超えた場合・・・
つまり、＃３のコードでは、日付差 = 1 を前提としてしています。

　　　　dtR = dtR + wdt

この部分を ↓

　　　　dtR = dtR + wdt
　　　　dtR = dtR + (Int(dtE) - Int(dtS) - 1) * #7:00:00 AM#

とでも変更すれば良さそうです。
ただ、40 時間以上の連続勤務・・・・　どうなんですかね（必要なら修正するってことで）


氏名　　日付　　　　出社　　　退社
Aさん　2013/10/10　20:00:00　1:20:00
Cさん　2013/10/15　 1:20:00　7:00:00

のデータで

SELECT 氏名, 日付, 出社, 退社,
CalcNightTimes(日付+出社,日付+退社-(出社>退社)) AS 時間
FROM テーブル名;

するのなら、日付差最大は 1 にしかならないので修正は不要と思います。

氏名　　　　出社　　　　　　　　　退社
Aさん　　2013/10/10 20:00:00　　2013/10/11 1:20:00

という持ち方をした時に、考えればいいと思います。


※ と一部、ブログ過去記事の内容を転記してみました。

No.5 回答者： hatena1989 回答日時： 2013/10/14 11:05

コードで変換ミスがあったので訂正して再登校します。

> データ型は日付時刻型です。
> 分かりやすくしようと思い、25時などと表現してしまいましたが、
>
> 翌日の朝5時や朝1時の事です。

日付　　　　出社　　退社
10月10日　20:00　　25:20

ではなく、

日付　　　　出社出社　退社
10月10日　20:00　　　　01:20

というように入力されているということでしょうか。

クエリで式で求めると複雑になるので、標準モジュールにユーザー定義関数を作成します。


'深夜の就業時間を分単位で返す
Public Function NightWorkTime(出社, 退社)
　　Dim t1 As Date, t2 As Date
　　Dim m1 As Long, m2 As Long
　　
　　If IsNull(出社) Or IsNull(退社) Then
　　　　NightWorkTime = Null
　　　　Exit Function
　　End If
　　
　　t1 = 出社
　　t2 = 退社
　　If t1 > t2 Then t2 = t2 + 1
　　If t2 < #10:00:00 PM# Or t1 > #5:00:00 AM# + 1 Then
　　　　NightWorkTime = 0
　　　　Exit Function
　　End If
　　
　　If t1 < #10:00:00 PM# Then t1 = #10:00:00 PM#
　　If t2 > #5:00:00 AM# + 1 Then t2 = #5:00:00 AM# + 1
　　
　　m1 = (DateDiff("n", #12:00:00 AM#, t1) \ 15) * 15 '分に変換して15分単位で切り捨て
　　m2 = (DateDiff("n", #12:00:00 AM#, t2) \ 15) * 15
　　
　　NightWorkTime = m2 - m1
End Function


集計クエリで、

フィールド　／　集計
氏名　／　グループ化
年月: Format(日付,"yyyy/mm")　／　グループ化
深夜就業時間: NightWorkTime(出社, 退社)　／　合計

とすれば、社員、月別の 深夜就業時間 が分単位で表示されます。
分単位を何時間何分という表示に変換するには、下記の式で。

Format(深夜就業時間 \ 60,"0時間") & Format(深夜就業時間 Mod 60,"0分")

No.4 回答者： hatena1989 回答日時： 2013/10/14 10:44

> データ型は日付時刻型です。

> 分かりやすくしようと思い、25時などと表現してしまいましたが、
>
> 翌日の朝5時や朝1時の事です。

出社　　退社
20:00　　25:20

ではなく、

出社　　退社
20:00　　01:20

というように入力されているということでしょうか。

クエリで式で求めると複雑になるので、標準モジュールにユーザー定義関数を作成します。


'深夜の就業時間を分単位で返す
Public Function NightWorkTime(出社, 退社)
　　Dim t1 As Date, t2 As Date
　　Dim m1 As Long, m2 As Long
　　
　　If IsNull(出社) Or IsNull(退社) Then
　　　　NightWorkTime = Null
　　　　Exit Function
　　End If
　　
　　t1 = 出社
　　t2 = 退社
　　If t1 > t2 Then t2 = t2 + 1
　　If t2 &lt; #10:00:00 PM# Or t1 > #5:00:00 AM# + 1 Then
　　　　NightWorkTime = 0
　　　　Exit Function
　　End If
　　
　　If t1 &lt; #10:00:00 PM# Then t1 = #10:00:00 PM#
　　If t2 > #5:00:00 AM# + 1 Then t2 = #5:00:00 AM# + 1
　　
　　m1 = (DateDiff("n", #12:00:00 AM#, t1) &yen; 15) * 15 '分に変換して15分単位で切り捨て
　　m2 = (DateDiff("n", #12:00:00 AM#, t2) &yen; 15) * 15
　　
　　NightWorkTime = m2 - m1
End Function


集計クエリで、


氏名　グループ化
年月: Format(日付,"yyyy/mm")　グループ化
深夜就業時間: NightWorkTime(出社, 退社)　合計

とすれば、社員、月別の 深夜就業時間 が分単位で表示されます。
分単位を何時間何分という表示に変換するには、下記の式で。

Format(深夜就業時間 \ 60,"0時間") & Format(深夜就業時間 Mod 60,"0分")

No.3 回答者： 30246kiku 回答日時： 2013/10/14 07:58

氏名　出社　退社

Aさん　　2013/10/10 20:00:00　　2013/10/11 1:20:00

とあった場合、

氏名　出社　退社　時間
Aさん　　2013/10/10 20:00:00　　2013/10/11 1:20:00　　3.25

とするものになります。（「出社」「退社」とも日付/時刻型）
以下の関数を標準モジュールに記述しておきます。

Public Function CalcNightTimes(ByVal dtS As Date, ByVal dtE As Date) As Double
　　Dim wdt As Date
　　Dim dtR As Date

　　dtS = dtS + #2:00:00 AM#
　　dtE = dtE + #2:00:00 AM#
　　wdt = Int(dtS) + #7:00:00 AM#
　　If (Int(dtS) = Int(dtE)) Then
　　　　If (dtE <= wdt) Then
　　　　　　dtR = dtE - dtS
　　　　ElseIf (dtS <= wdt) Then
　　　　　　dtR = wdt - dtS
　　　　End If
　　Else
　　　　If (dtS <= wdt) Then
　　　　　　dtR = wdt - dtS
　　　　End If
　　　　wdt = dtE - Int(dtE)
　　　　If (wdt > #7:00:00 AM#) Then wdt = #7:00:00 AM#
　　　　dtR = dtR + wdt
　　End If
　　CalcNightTimes = Hour(dtR) _
　　　　　　　　　　+ Choose(Minute(dtR) \ 15 + 1, 0, 0.25, 0.5, 0.75)
End Function

クエリでは、SQLビューでみた時

SELECT 氏名, 出社, 退社, CalcNightTimes(出社,退社) AS 時間
FROM テーブル名;

とすれば時間部分が計算されて表示されます。
１０月の・・・　で集計するとすれば以下のように記述すれば良いと思います。

SELECT 氏名, Sum(CalcNightTimes(出社,退社)) AS 時間
FROM テーブル名
WHERE 出社 >= #2013/10/1# AND 出社 < #2013/11/1#
GROUP BY 氏名;


なお、
> 15分単位で切り捨てたいです
が、いつの段階かわからないのですが、計算した結果に適用するのであれば上記のままで。
もし、

氏名　出社　退社
Aさん　　2013/10/10 20:03:00　　2013/10/11 1:20:00

のデータは

氏名　出社　退社
Aさん　　2013/10/10 20:15:00　　2013/10/11 1:15:00

と解釈してから・・・であれば、以下の記述を処理先頭に追加してください。

　　If ((Minute(dtS) Mod 15) <> 0) Then
　　　　dtS = Int(dtS) _
　　　　　　+ TimeSerial(Hour(dtS) _
　　　　　　　　　　　　, Choose(Minute(dtS) \ 15 + 1, 15, 30, 45, 60) _
　　　　　　　　　　　　, 0)
　　End If
　　If ((Minute(dtE) Mod 15) <> 0) Then
　　　　dtE = Int(dtE) _
　　　　　　+ TimeSerial(Hour(dtE) _
　　　　　　　　　　　　, Choose(Minute(dtE) \ 15 + 1, 0, 15, 30, 45) _
　　　　　　　　　　　　, 0)
　　End If

※ たぶん上記で動くと思いますが、不都合あれば修正してください。

No.2 回答者： hatena1989 回答日時： 2013/10/13 12:19

出社、退社 のフィールドのデータ型はなんですか。

日付/時刻型だと、29:10 というような24:00以降の表現はできませんので、テキスト型でしょうか。
それが明確にならないと回答できないです。

あと、調べた時間は、月で集計するということですね。
それを考慮すると、計算結果は数値型になるようにすべきですね。
例えば、３時間１５分なら、3.25 にするとか。
テキスト型だと、集計できません。

この回答への補足

大変申し訳ございません。
データ型は日付時刻型です。
分かりやすくしようと思い、25時などと表現してしまいましたが、

翌日の朝5時や朝1時の事です。

どうぞよろしくお願いいたします。

補足日時：2013/10/13 21:15

No.1 回答者： mshr1962 回答日時： 2013/10/13 11:36

FLOOR関数：基準値で該当する値を切り捨てる関数

MAX関数：与えられた数値の中の最大値を選択する関数
MIN関数：与えられた数値の中の最小値を選択する関数

■全体の勤務時間
15分単位の切り捨てなのでFLOOR関数を使います。
=FLOOR(退社-出社,TIME(0,15,0))

■22時から29時の勤務時間
退社時間が22時～29時の間、出社時間が22時以降で計算すればいいので
=FLOOR(MAX(MIN(退社,29/24),22/24)-MIN(MAX(出社,22/24),29/24),TIME(0,15,0))

上記数式で
退社が29時～30時の場合は、29時になります。(MIN関数)
退社が22時以前の場合は、22時になります。(MAX関数)
退社が22時～29時の場合は、そのままの時刻になります。

出社が22時以前の場合は、22時になります。(MAX関数)
出社が29時～30時の場合は、29時になります。(MIN関数)
出社が22時～29時の場合は、そのままの時刻になります。

退社－出社で22時～29時の勤務時間が計算できます。

後は15分で切り捨てを行います。（FLOOR関数）