C# と VB.NET の質問掲示板

開発環境
OraceleServer 12c Release 12.1.0.1.0
OracleClient  11g Release 11.2.0.1.0
Windows7 32bit

以下のようなテーブルがあり、
同一の社員、作業日で開始時刻〜終了時刻の範囲が被るデータを検索したいのですが、
日付をまたいでいることもあり、方法が分かりませんでした。
どなたかアドバイスをお願い致します。

テーブル名（T_KINTAI）IDはNUMBER型、それ以外はVARCHAR2型となっています。
ID | 社員CODE | 作業日  |開始時間|終了時間
  1|A001      |20180301 |0100    |0800
  2|A001      |20180301 |2200    |0800
  3|A001      |20180301 |2300    |0100
  4|A002      |20180301 |0845    |1715
  5|A002      |20180301 |1710    |2200
  6|A003      |20180301 |0845    |1830
  7|A003      |20180301 |2200    |0400

検索結果
ID | 社員CODE | 作業日  |開始時間|終了時間
  2|A001      |20180301 |2200    |0800
  3|A001      |20180301 |2300    |0100
  4|A002      |20180301 |0845    |1715
  5|A002      |20180301 |1710    |2200

ID3の開始時間、終了時間共にID2の開始時間、終了時間の範囲内なので出力対象
ID5の開始時間がID4の開始時間、終了時間の範囲内なので出力対象

■No86776 (TT_R25 さん) に返信
> ID | 社員CODE | 作業日  |開始時間|終了時間
>   1|A001      |20180301 |0100    |0800

0100 〜 0800 というデータが意味するところが
01:00:00〜08:00:59 なのか
01:00:00〜08:00:00 なのか
01:00:00〜07:59:59 なのか
いわゆる以上/超過、以下/未満の判断が曖昧ですが、
この辺は後で考えて頂くとして。


> 日付をまたいでいることもあり、方法が分かりませんでした。
開始終了いずれかが NULL となることがあるのか？
開始時間と終了時間が同一の場合に、0分扱いとするのか 24時間扱いとするのか？
そもそも48時間超過は無いのか？

…などなど、細かい仕様が曖昧だったりしますが、今回はとりあえず
「開始時間≧終了時間」なら翌日終了扱いということで考えてみました。


WITH KINTAI1 AS (
  SELECT
    T_KINTAI.*,
    TO_DATE(作業日 || 開始時間, 'YYYYMMDDHH24MISS') AS 開始日時,
    TO_DATE(作業日 || 終了時間, 'YYYYMMDDHH24MISS')
    + CASE WHEN 開始時間 >= 終了時間 THEN 1 ELSE 0 END AS 終了日時
  FROM
    T_KINTAI
), KINTAI2 AS (
  SELECT
    KINTAI1.*,
    LAG (終了日時, 1, NULL) OVER (PARTITION BY 社員CODE, 作業日 ORDER BY 開始時間, 終了時間, ID) AS 直前の終了日時,
    LEAD(開始日時, 1, NULL) OVER (PARTITION BY 社員CODE, 作業日 ORDER BY 開始時間, 終了時間, ID) AS 直後の開始日時
  FROM
    KINTAI1
) SELECT ID, 社員CODE, 作業日, 終了時間 FROM KINTAI2
WHERE 開始日時 < 直前の終了日時 OR 直後の開始日時 < 終了日時
ORDER BY ID

■No86777 (魔界の仮面弁士 さん) に返信

魔界の仮面弁士さん、勉強になります。

TT_R25さん
解決済みですが、同じ日に３件以上あった場合にも
対応できるSQLを考えてみました。

ID | 社員CODE | 作業日  |開始時間|終了時間
 11|A011      |20180301 |1000    |1600
 12|A011      |20180301 |1100    |1200
 13|A011      |20180301 |1300    |1400

WITH KINTAI1 AS (
  SELECT
    T_KINTAI.*,
    TO_DATE(作業日 || 開始時間, 'YYYYMMDDHH24MISS') AS 開始日時,
    TO_DATE(作業日 || 終了時間, 'YYYYMMDDHH24MISS')
    + CASE WHEN 開始時間 >= 終了時間 THEN 1 ELSE 0 END AS 終了日時
  FROM
    T_KINTAI
)
SELECT ID, 社員CODE, 作業日, 開始時間, 終了時間
FROM KINTAI1 K
WHERE exists (
    select * from KINTAI1 M
    where K.ID <> M.ID and
          K.社員CODE = M.社員CODE and 
          K.作業日 = M.作業日 and
          K.開始日時 <= M.終了日時 and K.終了日時 >= M.開始日時)
order by ID

先の SQL に問題が見つかったので、
あえて 解決済み フラグを外しておきます。


■No86782 (TT_R25 さん) に返信
> 数々の説明不足の中、回答いただきありがとうございました。

重複期間の検出にあたり、
　2018/03/01 05:00〜同12:00
　2018/03/01 12:00〜同15:30
のようなパターンを拾うべきかどうかが分からなかったです。



■No86783 (furu さん) に返信
> 解決済みですが、同じ日に３件以上あった場合にも
> 対応できるSQLを考えてみました。

おぉ、ご指摘感謝。

再考してみたところ、作業日 は繋ぐべきではない気がしてきました。
下記の ID=10 のような、日またぎの重複も検出するべきかと思うので。

ID|社員CODE| 作業日 |開始時間|終了時間
 1|A001    |20180301|0100    |0800
 2|A001    |20180301|2200    |0800
 3|A001    |20180301|2300    |0100
 4|A002    |20180301|0845    |1715
 5|A002    |20180301|1710    |2200
 6|A003    |20180301|0845    |1830
 7|A003    |20180301|2200    |0400
 8|A003    |20180302|0400    |0600
 9|A010    |20180302|0400    |0600
10|A011    |20180228|2000    |1030
11|A011    |20180301|1000    |1600
12|A011    |20180301|1100    |1200
13|A011    |20180301|1300    |1400
14|A011    |20180302|1100    |1500
15|A011    |20180302|1530    |1900


上記では、 ID=7 の 終了と ID=8 の開始が同時刻ですが
これは重複とみなさない方針として抽出しています。

これも重複と見做したい場合は、日時比較部の
< 演算子を <= 演算子に変更してみてください。


WITH KINTAI AS (
  SELECT
    T_KINTAI.*,
    TO_DATE(作業日 || 開始時間, 'YYYYMMDDHH24MISS') AS 開始日時,
    TO_DATE(作業日 || 終了時間, 'YYYYMMDDHH24MISS')
    + CASE WHEN 開始時間 >= 終了時間 THEN 1 ELSE 0 END AS 終了日時
  FROM
    T_KINTAI
)
SELECT ID, 社員CODE, 作業日, 開始時間, 終了時間
FROM KINTAI K
WHERE EXISTS (
    SELECT * FROM KINTAI M
    WHERE K.ID <> M.ID
      AND K.社員CODE = M.社員CODE
   /* AND K.作業日 = M.作業日 */
      AND K.開始日時 < M.終了日時
      AND M.開始日時 < K.終了日時
) ORDER BY ID