oracleで勤務時間の計算

Question

お世話になります。

下記のようなテーブルから、その月の勤務時間を計算したいです。
タイプIが出勤 タイプOが退勤のレコードです。
また、打刻時間はdate型です。

ユーザーID	打刻時間	タイプ
admin	2017-02-01 09:00:00	I
admin	2017-02-01 18:00:00	O
user	2017-02-01 09:00:00	I
user	2017-02-01 18:00:00	O
admin	2017-02-02 09:04:00	I
admin	2017-02-02 18:12:00	O

現在のSQL文は下記のような感じです。
宜しくお願いします。

SELECT
  TO_CHAR(TO_TIMESTAMP(o.打刻時間) - TO_TIMESTAMP(i.打刻時間), 'HH24MI') AS work_time
FROM
  表 i
  INNER JOIN
  表 o
  ON i.ユーザーID = o.ユーザーID
  AND TO_CHAR(i.打刻時間, 'YYYY/MM/DD') = TO_CHAR(o.打刻時間, 'YYYY/MM/DD')
WHERE
  i.user_id = 'admin'	
  AND i.タイプ= 'I'
  AND o.タイプ = 'O'

Answer 1

環境がoracleではないので、未検証ですが下記の様なSQLでいけないでしょうか？

Select
ユーザID,sum(o.打刻時間 - i.打刻時間)
from
(select ユーザID,打刻時刻 from 勤怠 where タイプ = 'I') as i
inner join
(select ユーザID,打刻時刻 from 勤怠 where タイプ = 'O') as o
On i.ユーザID = o.ユーザID
/*Group by を追記*/
Group by ユーザID

Comments

[grifet]

ご回答ありがとうございます。
書き損じていたのですが、その月の勤務時間の合計を、
ユーザーID毎に合計して出したいのです。
現在のSQLですと、複数結果(?)でてしまうので･･

また、実行してみたところ、
-.625
.375
.375
.375
1.375
のようになってしまい、よくわかりません･･

[motuo]

であれば、group by ユーザIDとすれば良いのではないでしょうか？(回答修正します)

[grifet]

重ね重ねすみません。
実行結果が
admin 1.5
user .375
のようになるのですが、
何時間、何分といったような表示にするには、
どのようにすれば良いのでしょうか。
宜しくお願いします。

[motuo]

date型は1/24で1時間を管理しています。なので、逆に24をかけてあげると時間になります。
さらに60をかけると分になります。1440をかけると分単位で出力できます。
結果、adminは2140分(36時間)、userは540分(9時間)になっている様に見えます。

よって、to_char(trunc(算出した分/60),'fm00')||':'||to_char(mod(算出した分,60),'fm00')とすれば良いのではないでしょうか。

[grifet]

なるほど…。
ありがとうございます。
しかし、adminは18時間しか働いていないはずなので、
i.ユーザーID, SUM(o.打刻時間- i.打刻時間)*24
ON i.ユーザーID = o.ユーザーID
AND TO_CHAR(i.打刻時間, 'YYYY/MM/DD') = TO_CHAR(o.打刻時間, 'YYYY/MM/DD')
としたところ、admin18,user9
となったのですが、
こういう書き方(?)で良いのでしょうか？

[motuo]

そうですね。確かに日付で結合する必要がありましたね。その書き方でよろしいかと思います。

[grifet]

ありがとうございます。
長々とお付き合いくださりありがとうございました。
また私の質問を見かけた際は宜しくお願い致します。