| 氏名 | 会社 | 所属 | 開始日 | 終了日 |
|---|---|---|---|---|
| あああ | ｘｘｘｘｘ | 経理 | 2000/12/31 | |
| あああ | ｘｘｘｘｘ | 人事 | 2001/1/1 | 2010/3/31 |
| あああ | ｘｘｘｘｘ | 総務 | 2010/4/1 | 2018/8/31 |
| あああ | ｘｘｘｘｘ | 経理 | 2018/9/1 | 2020/3/31 |
| あああ | ｘｘｘｘｘ | 人事 | 2020/4/1 | |
| ・・・ | ||||
| いいい | ｘｘｘｘｘ | 営業 | 2010/4/1 | 2019/1/31 |
| いいい | ｘｘｘｘｘ | 経理 | 2019/2/1 | 2022/3/31 |
| いいい | ｘｘｘｘｘ | 人事 | 2022/4/1 | |
| ・・・ | ||||
| 最初の開始日は管理していたり、いなかったり（あるとは限らない） | ||||
| 現在継続している場合は、終了日がない |
サンプルでは、人ごとに会社ごとに日付がつながっている状態ですが、
抜けているレコードに対してフラグを立てるなど
日付がつながっていないようなエラーを発見するロジックを作成したいと思っています。
よろしくお願いいたします。
下記のSQLで 開始日の１日前の終了日が存在しない場合、チェックが True になります。
hatena様
なるほど、開始日の１日前の終了日が存在しないという条件で判定をするのですか。
どうもありがとうございます。明日、早速試してみます。
| 氏名 | 会社 | 所属 | 開始日 | 終了日 |
| ううう | ZZZZZ | 法務 | 2020/4/1 | 2021/3/31 |
| ううう | TTTTT | 法務 | 2021/4/1 | 2022/3/31 |
| ううう | EEEEE | 法務 | 2022/4/1 | NULL |
試したところ、なかなかうまく判定ができていません。
例えば、こんなケースもありまして、これはエラーではないと判断したいです。
出向等をして、1レコードで完結している場合など。
よろしくお願いいたします。
試していませんが、氏名と開始日で外部結合すれば、会社違いのエラー判定を回避できるのかも。
終了したら翌日には開始、空白が生じたらエラーという事でしょうか。例えば、終了日4/10日、次の開始日4/12日の場合、どちらをエラーと判定するのでしょう。正しくは、終了日4/11日、次の開始日4/12日、それとも、終了日4/10日、次の開始日4/11。あるいは、終了日と開始日、どちらもエラーなんてことも。
9999/12/31で登録しておくとか。
氏名と開始日に複合主キーを設定して、登録の仕組みを整えるといいかも。
アドレスありがとうございます。
サンプルはエラーの発生が無い想定でおだししています。
実データが、終了日4/10日、次の開始日4/12日だった場合、どらのレコードもエラーとしたいです。
正しいデータはいくつかの可能性がありますが、そこは気にせず、単につながりがないレコードを抽出し、
人間が確認をして、正しいデータに修正をすることで、結果、エラーが回避されれば良いと考えています。
運用面で、9999/12/31を使うとか、複合キーの設定も検討はしてみたいと思います。
「氏名」だけのチェックでいいのであれば
AND T1.会社=T2.会社を削除すればいいです。そもそも「氏名」「会社」の判定部分は本題とはずれた話ですね。同姓同名問題があるので破綻します。「個人」を特定するためにユニークなキーを設定しましょう日付で起こりうるエラー
エラーとはどのようなデータのことなのか明確にしてhatenaさんが提示したようなSQLを作ってつなげればいいと思いますよ
SQLでやると起こりうるエラー毎にテーブルの参照が必要になるんでhirotonならVBAでエラーレコードの拾い出しにしそうですけどね
とりあえず下記でどうでしょう。
出力結果例
ただし上記のクエリは更新不可になりますので、
この結果を見ながらテーブルを修正することになります。
hirotonさんが厳密なエラー例を出してますが、
上記のクエリは、このなかの、2. 3. 6. 7. はエラーになりません。
すべてのエラーに対応するなら、hirotonさん同様VBAを検討しますね。
みなさま
申し訳ございません。忙殺されていまして、確認が遅くなりました。
アドバイスを試してみて、後程フィードバックさせていただきます。
ひとまずご連絡まで。
厳密なチェックを要するものではありませんので、
hatenaさまのSQLを利用させていただいております。
1点だけ要件を加えさせていただいております。
T1からT2, T3に外部結合する際、氏名、出向元、出向先フィールで結合をするように修正し、
重複レコードが作られるので、Distinctをしております。
演算フィールドのDMin条件には、氏名、出向元、出向先が一対するものと修正いたしました。
データシートは表示できるのですが、あいまいな外部結合が含まれるため、SQLが表示されません。
元データ
| 氏名 | 出向元 |出向先 | 兼務率 |開始日 | 終了日 |
| -------- | -------- | -------- | -------- | -------- | -------- |
| サンプル | AAA | あああ | 50% | 2017/1/1 | 2022/3/31|
| サンプル | AAA | ううう | 50% | 2017/1/1 | 2022/3/31|
| サンプル | AAA | いいい | 0% | 2021/12/1| 2022/3/31|
| サンプル | AAA | ううう | 100% | 2022/4/1 | |
| サンプル | AAA | あああ | 0% | 2022/4/1 | |
SQL実行結果
| 氏名 | 出向元 |出向先 | 兼務率 |開始日 |終了日 | 開始フラグ |終了フラグ|
| -------- | -------- | --------| ---- | -------- | -------- | -------- | -------- |
| サンプル | AAA | あああ | 50% | 2017/1/1 | 2022/3/31 | 0 | 0 |
| サンプル | AAA | ううう | 50% | 2017/1/1 | 2022/3/31 | 0 | 0 |
| サンプル | AAA | いいい | 0% | 2021/12/1| 2022/3/31 | 0 | 0 |
| サンプル | AAA | ううう | 100% | 2022/4/1 | | 0 | 0 |
| サンプル | AAA | ううう | 100% | 2022/4/1 | | -1 | 0 |
| サンプル | AAA | あああ | 0% | 2022/4/1 | | 0 | 0 |
本来、エラーがでないはずなのですが、下から、2行目の重複レコードが生じ、開始日非連続にフラグが立ちます。
原因がよく分からず、よろしくお願いいたします。
すみません、クエリをデザインで開くと(T1.開始日-1=T2.終了日) この部分が消えてしまう？んですかね。
上記のあやまったフラグは出なくなりました。
細かいチェックがまだできていませんが、こちらで恐らく大丈夫なような気がします。
どうもありがとうございました。
>> 9
SQLは苦手です。メモさせて下さい。
原本と原本コピーがあり、原本の開始日をマイナス1、原本コピーの終了日と突合してみたり、原本の終了日をプラス1、原本コピーの開始日と突合してみたり。最終的に左外部結合。原本は一致レコードと不一致レコード、原本コピーは一致レコードとNullレコード。
そして「T2.終了日 Is Null」と「T3.開始日 Is Null」の部分で不一致クエリ。空欄、先頭の開始日、最後の終了日もひっくるめて判定。
氏名あああの場合、空欄の不一致判定を除外する為に、「And Not (T1.開始日 Is Null」と「And Not (T2.終了日 Is Null」が必要。
氏名いいいの場合、先頭の開始日と最後の終了日の不一致判定を除外する為に、「And Not (…T1.開始日 = DMin」と「And Not (…T2.終了日 = DMax」が必要。
>> 14
同姓同名の問題か、転籍等により出向元が変わる場合のことを仰られているのでしょうか。
転籍の場合、手続き上、退職をして入社をするので、氏名が同じでも社員コードが変わり別人格となります。
運用上は、同姓同名の問題もありますので、氏名ではく社員コードで管理をしますので、SQLは読み替えています。
ですので、上記フラグは0で良いです。
>> 13
>> 16
>> 17
しかし、よくよく見れば、DMinで対応出来ているのでそんな事にはならなかった。
すみません、視野が狭くなって勘違いしてました。