スポンサーサイト 


上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

--/--/--

Category: スポンサー広告

TB: --  /  CM: --

top △

重複時間帯を求めてみる 


以下のようなデータがあった場合、
氏名入室時刻退室時刻
aaa2011/10/01 11:002011/10/01 17:00
bbb2011/10/01 13:002011/10/01 15:00
ccc2011/10/01 14:002011/10/01 15:30
ddd2011/10/01 17:002011/10/01 19:00
eee2011/10/01 9:002011/10/01 11:00
fff2011/10/01 19:002011/10/01 20:30

入室時刻、退室時刻内で他の人と重複していた時間帯を以下のように求めたい。
氏名重複入室重複退室
aaa2011/10/01 13:002011/10/01 15:30
bbb2011/10/01 13:002011/10/01 15:00
ccc2011/10/01 14:002011/10/01 15:30

この元のデータをテーブル「T1A」に作ったとして
SELECT Q1.*, Q2.*
FROM T1A AS Q1, T1A AS Q2
WHERE Q1.氏名<>Q2.氏名 AND Q1.入室時刻<Q2.退室時刻 AND Q1.退室時刻>Q2.入室時刻;
とすれば、誰に対して誰が重複しているか判別できますね。
この表示結果は以下になります。
Q1.氏名Q1.入室時刻Q1.退室時刻Q2.氏名Q2.入室時刻Q2.退室時刻
aaa2011/10/01 11:002011/10/01 17:00bbb2011/10/01 13:002011/10/01 15:00
aaa2011/10/01 11:002011/10/01 17:00ccc2011/10/01 14:002011/10/01 15:30
bbb2011/10/01 13:002011/10/01 15:00aaa2011/10/01 11:002011/10/01 17:00
bbb2011/10/01 13:002011/10/01 15:00ccc2011/10/01 14:002011/10/01 15:30
ccc2011/10/01 14:002011/10/01 15:30aaa2011/10/01 11:002011/10/01 17:00
ccc2011/10/01 14:002011/10/01 15:30bbb2011/10/01 13:002011/10/01 15:00

この結果を元に、Q1、Q2 の入室時刻、Q1、Q2 の退室時刻をいじれば求まりますね。
 
SELECT Q1.氏名, 
Min(IIf(Q1.入室時刻>Q2.入室時刻,Q1.入室時刻,Q2.入室時刻)) AS 重複入室, 
Max(IIf(Q1.退室時刻<Q2.退室時刻,Q1.退室時刻,Q2.退室時刻)) AS 重複退室
FROM T1A AS Q1, T1A AS Q2
WHERE Q1.氏名<>Q2.氏名 AND Q1.入室時刻<Q2.退室時刻 AND Q1.退室時刻>Q2.入室時刻
GROUP BY Q1.氏名;
とすると
氏名重複入室重複退室
aaa2011/10/01 13:002011/10/01 15:30
bbb2011/10/01 13:002011/10/01 15:00
ccc2011/10/01 14:002011/10/01 15:30

が求まります。
で、これを元のデータと並べて見ようとして
SELECT Q3.*, Q4.重複入室, Q4.重複退室
FROM T1A AS Q3 LEFT JOIN 
(SELECT Q1.氏名, 
Min(IIf(Q1.入室時刻>Q2.入室時刻,Q1.入室時刻,Q2.入室時刻)) AS 重複入室, 
Max(IIf(Q1.退室時刻<Q2.退室時刻,Q1.退室時刻,Q2.退室時刻)) AS 重複退室 
FROM T1A AS Q1, T1A AS Q2 
WHERE Q1.氏名<>Q2.氏名 And Q1.入室時刻<Q2.退室時刻 And Q1.退室時刻>Q2.入室時刻 
GROUP BY Q1.氏名
)  AS Q4 ON Q3.氏名 = Q4.氏名
ORDER BY Q3.氏名;
(色つき部分は一つ前のクエリ)
この結果は
氏名入室時刻退室時刻重複入室重複退室
aaa2011/10/01 11:002011/10/01 17:002011/10/01 13:002011/10/01 15:30
bbb2011/10/01 13:002011/10/01 15:002011/10/01 13:002011/10/01 15:00
ccc2011/10/01 14:002011/10/01 15:302011/10/01 14:002011/10/01 15:30
ddd2011/10/01 17:002011/10/01 19:00  
eee2011/10/01 9:002011/10/01 11:00  
fff2011/10/01 19:002011/10/01 20:30  

となります。

では、複数の日付のあるデータ(テーブル「T2A」)とした場合、
氏名入室時刻退室時刻
aaa2011/10/01 11:002011/10/01 17:00
aaa2011/10/02 12:302011/10/02 19:00
aaa2011/10/03 9:302011/10/03 17:30
bbb2011/10/01 13:002011/10/01 15:00
bbb2011/10/02 14:302011/10/02 17:00
bbb2011/10/03 11:302011/10/03 15:30
ccc2011/10/01 14:002011/10/01 15:30
ccc2011/10/02 15:302011/10/02 17:30
ccc2011/10/03 12:302011/10/03 16:00
ddd2011/10/01 17:002011/10/01 19:00
ddd2011/10/02 18:302011/10/02 21:00
ddd2011/10/03 15:302011/10/03 19:30
eee2011/10/01 9:002011/10/01 11:00
eee2011/10/02 10:302011/10/02 13:00
eee2011/10/03 7:302011/10/03 11:30
fff2011/10/01 19:002011/10/01 20:30
fff2011/10/02 20:302011/10/02 22:30
fff2011/10/03 17:302011/10/03 21:00

単に前のクエリを実行すると
氏名入室時刻退室時刻重複入室重複退室
aaa2011/10/02 12:302011/10/02 19:002011/10/01 13:002011/10/03 17:30
aaa2011/10/03 9:302011/10/03 17:302011/10/01 13:002011/10/03 17:30
aaa2011/10/01 11:002011/10/01 17:002011/10/01 13:002011/10/03 17:30
bbb2011/10/01 13:002011/10/01 15:002011/10/01 13:002011/10/03 15:30
bbb2011/10/02 14:302011/10/02 17:002011/10/01 13:002011/10/03 15:30
bbb2011/10/03 11:302011/10/03 15:302011/10/01 13:002011/10/03 15:30
ccc2011/10/03 12:302011/10/03 16:002011/10/01 14:002011/10/03 16:00
ccc2011/10/01 14:002011/10/01 15:302011/10/01 14:002011/10/03 16:00
ccc2011/10/02 15:302011/10/02 17:302011/10/01 14:002011/10/03 16:00
ddd2011/10/02 18:302011/10/02 21:002011/10/02 18:302011/10/03 19:30
ddd2011/10/03 15:302011/10/03 19:302011/10/02 18:302011/10/03 19:30
ddd2011/10/01 17:002011/10/01 19:002011/10/02 18:302011/10/03 19:30
eee2011/10/01 9:002011/10/01 11:002011/10/02 12:302011/10/03 11:30
eee2011/10/02 10:302011/10/02 13:002011/10/02 12:302011/10/03 11:30
eee2011/10/03 7:302011/10/03 11:302011/10/02 12:302011/10/03 11:30
fff2011/10/03 17:302011/10/03 21:002011/10/02 20:302011/10/03 19:30
fff2011/10/01 19:002011/10/01 20:302011/10/02 20:302011/10/03 19:30
fff2011/10/02 20:302011/10/02 22:302011/10/02 20:302011/10/03 19:30

のように訳がわからなくなるので、日にち部分をパラメータ入力するように変更します。
SELECT Q3.*, Q4.重複入室, Q4.重複退室
FROM T2A AS Q3 LEFT JOIN 
(SELECT Q1.氏名, 
Min(IIf(Q1.入室時刻>Q2.入室時刻,Q1.入室時刻,Q2.入室時刻)) AS 重複入室, 
Max(IIf(Q1.退室時刻<Q2.退室時刻,Q1.退室時刻,Q2.退室時刻)) AS 重複退室 
FROM 
(SELECT * FROM T2A WHERE 入室時刻>=CDate([年月日?]) And 入室時刻<DateAdd("d",1,[年月日?]))  AS Q1, 
(SELECT * FROM T2A WHERE 入室時刻>=CDate([年月日?]) And 入室時刻<DateAdd("d",1,[年月日?]))  AS Q2 
WHERE Q1.氏名<>Q2.氏名 And Q1.入室時刻<Q2.退室時刻 And Q1.退室時刻>Q2.入室時刻 
GROUP BY Q1.氏名)  AS Q4 ON Q3.氏名=Q4.氏名
WHERE Q3.入室時刻>=CDate([年月日?]) And Q3.入室時刻<DateAdd("d",1,[年月日?])
ORDER BY Q3.氏名;
のように入室時刻を限定してみます。
10/1 限定では
氏名入室時刻退室時刻重複入室重複退室
aaa2011/10/01 11:002011/10/01 17:002011/10/01 13:002011/10/01 15:30
bbb2011/10/01 13:002011/10/01 15:002011/10/01 13:002011/10/01 15:00
ccc2011/10/01 14:002011/10/01 15:302011/10/01 14:002011/10/01 15:30
ddd2011/10/01 17:002011/10/01 19:00  
eee2011/10/01 9:002011/10/01 11:00  
fff2011/10/01 19:002011/10/01 20:30  

10/2 限定では
氏名入室時刻退室時刻重複入室重複退室
aaa2011/10/02 12:302011/10/02 19:002011/10/02 12:302011/10/02 19:00
bbb2011/10/02 14:302011/10/02 17:002011/10/02 14:302011/10/02 17:00
ccc2011/10/02 15:302011/10/02 17:302011/10/02 15:302011/10/02 17:30
ddd2011/10/02 18:302011/10/02 21:002011/10/02 18:302011/10/02 21:00
eee2011/10/02 10:302011/10/02 13:002011/10/02 12:302011/10/02 13:00
fff2011/10/02 20:302011/10/02 22:302011/10/02 20:302011/10/02 21:00

のようになります。

※ ただし、
上記では指定した日にちのデータに同一の人のデータが1つしかない。
これが前提条件にあります。
複数あるのなら、オートナンバーとかレコードを特定できるフィールドを追加して
Q1.氏名<>Q2.氏名 部分をそのフィールドにするとか・・・・

※ また、以下のように 13:30 ~ 15:00 の間重複していなくても、
氏名入室時刻退室時刻
aaa2011/10/01 11:002011/10/01 17:00
bbb2011/10/01 13:002011/10/01 13:30
ccc2011/10/01 15:002011/10/01 18:30
 重複した人の中での最小/最大で求めています。
氏名重複入室重複退室
aaa2011/10/01 13:002011/10/01 17:00
bbb2011/10/01 13:002011/10/01 13:30
ccc2011/10/01 15:002011/10/01 17:00
 厳密にするには考え直さないと・・・・

※ 似たような感じで、その人の時間調整が出来ますね

例えば、テーブル「T3A」が以下のような感じだった時
an日付勤務地開始時間終了時間担当者
12011/10/09東京10:00:0012:00:00Aさん
22011/10/09東京13:00:0014:00:00Aさん
32011/10/09神田12:30:0014:00:00Bさん
42011/10/09神田9:00:0011:00:00Aさん
52011/10/09渋谷10:30:0011:30:00Aさん

SELECT * FROM T3A AS Q1
WHERE
EXISTS (
SELECT 1 FROM T3A AS Q2
WHERE
Q1.an <> Q2.an
AND Q1.日付 = Q2.日付 
AND Q1.担当者 = Q2.担当者 
AND Q2.開始時間 < Q1.終了時間 
AND Q2.終了時間 > Q1.開始時間
)
ORDER BY Q1.an;
とすれば
an日付勤務地開始時間終了時間担当者
12011/10/09東京10:00:0012:00:00Aさん
42011/10/09神田9:00:0011:00:00Aさん
52011/10/09渋谷10:30:0011:30:00Aさん

となって、重複を確認できますね・・・・


※ 時間帯が重複しているかは、Q1.開始<Q2.終了 And Q1.終了>Q2.開始 を覚えておくと便利


今回、サンプルファイルはありません。
でも、確認は簡単にできます。
表示されているテーブル内容をコピーし、
過去記事サンプルデータを html の Table 形式に展開等するツール を使い取り込むと
テーブルデータとして簡単に作成できます。
また、クエリ部分の記述は、クエリのSQLビューにコピー&貼り付けすれば・・・・
関連記事

2011/10/09

Category: クエリ

TB: 0  /  CM: 0

top △

この記事に対するコメント

top △

コメントの投稿

Secret

top △

トラックバック

トラックバックURL
→http://kikutips.blog13.fc2.com/tb.php/99-7abd9e41
この記事にトラックバックする(FC2ブログユーザー)

top △


上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。