早速ですが、よろしくお願いします。
人、役職、部門で人の所属歴を、
重複している期間は合算（最新をベース）して、出戻りも含めてリスト化したいというのが、やりたいことです。
上記の3つのキー項目が連続する期日の最小値、最大値を特定するため、最大フラグ、最小フラグを設定しました。そして、最大フラグのデータをベースに、最小値フラグの開始日を参照するという仕様としました。
これをキーブレイク処理でやろうとしていまして、一応は、なんとかできてきますが、ロジックが明らかにお粗末だなと思っています。
問題は、最大フラグを付すロジックにありまして。現状、対象テーブルのレコードセットを取得してオートナンバー順にルーフを回し、グループ内連番に加えて最小値転記とフラグを付した後、もう一度ルーフを回し、振ったグループ内連番をみながら、最大フラグを付しています。（次のオートナンバーのグループ内連番を参照しカレントが最大値稼働か判断、あるいは、あるグループの最小値の一つ前が前のグループの最大値など）いい案が思い浮かばずご相談させていただいております。これが一度にできないかと。
autoNo 人 役職 部門 日付（期間）最小 最大
——————————————————
1 aaa 123 あああ 4/1 5/31 true true
2 aaa 123 あいあ 6/1 8/31 true false
3 aaa 123 あいあ 9/1 12/31 false true
4 aaa 123 あああ 1/1 3/31 true true
5 bbb 568 いいい 4/1 5/31 true false
6 bbb 568 いいい 6/1 3/31 false true
7 ccc 432 ななな 4/1 3/31 true true
8 aaa 123 あああ 1/1 3/31 true true
※日付は年度は省略してあります。
上のデータを下のようにしたい。
1 aaa 123 あああ 4/1 5/31 true true
3 aaa 123 あいあ 6/1 12/31 false true
4 aaa 123 あああ 1/1 3/31 true true
6 bbb 568 いいい 4/1 3/31 false true
7 ccc 432 ななな 4/1 3/31 true true
8 aaa 123 あああ 1/1 3/31 true true
グループで連番を振ったらあとは選択クエリでグループ化して開始の日付は最小、終了の日付は最大を取ったらいいんじゃないでしょうか
フラグを付けること自体が目的なら、最小とは前の値がないこと、最大とは次の値がないことなので2重のチェックが必要なのは仕方がないですね
最大も同じ考え方でできますよ。レコードの最初から見てグループが変わったら最小と同様にレコードの最終から見てグループが変わったら最大です
ループ処理で
rs.eofになっていると思うのでそのまま逆にrs.MovePreviousしながらrs.bofまでチェックすればいいです何故レコードが追加されているのでしょうか？役職変更や部門移動イベントが発生していないのに。
とりあえず、終了日を99/3/31にしておくのはどうでしょう。
5 bbb 568 いいい 4/1 99/3/31
役職変更や部門移動イベントが起きた時に、終了日の更新(確定)と新規レコードの追加をやればいいんじゃない？
Accessデータベースに連番の概念は合わないと思います。連番を作らずに、日付並び替えや日付範囲抽出で理想を実現出来ないでしょうか？
( SQL ビュー)
SELECT [Q1].[autoNo_Min] AS [autoNo],
[所属歴テーブル].[人],
[所属歴テーブル].[役職],
[所属歴テーブル].[部門],
Min([所属歴テーブル].[在籍開始日]) AS [在籍開始日],
Max([所属歴テーブル].[在籍終了日]) AS [在籍終了日]
FROM [所属歴テーブル],
(SELECT [所属歴テーブル].[autoNo] AS [autoNo_Min],
Nz((SELECT Min(tmp2.[autoNo]) - 1
FROM [所属歴テーブル] tmp2
WHERE tmp2.[autoNo] > [所属歴テーブル].[autoNo]
AND ( tmp2.[人] <> [所属歴テーブル].[人]
OR tmp2.[役職] <> [所属歴テーブル].[役職]
OR tmp2.[部門] <> [所属歴テーブル].[部門])),
[所属歴テーブル].[autoNo]) AS [autoNo_Max]
FROM [所属歴テーブル]
WHERE NOT EXISTS (SELECT tmp.[autoNo]
FROM [所属歴テーブル] tmp
WHERE tmp.[autoNo] = [所属歴テーブル].[autoNo] - 1
AND tmp.[人] = [所属歴テーブル].[人]
AND tmp.[役職] = [所属歴テーブル].[役職]
AND tmp.[部門] = [所属歴テーブル].[部門])) [Q1]
WHERE [所属歴テーブル].[autoNo] Between [Q1].[autoNo_Min] And [Q1].[autoNo_Max]
GROUP BY [Q1].[autoNo_Min],
[所属歴テーブル].[人],
[所属歴テーブル].[役職],
[所属歴テーブル].[部門]
ORDER BY [Q1].[autoNo_Min];
以上のクエリのような結果を得たい、ということでしょうか。
みなさま
いろいろご意見ありがとうございます。
お返事が遅くなり申し訳ありません。
まずは確認させていただきます。
よろしくお願いいたします。
この考え方、思いつきませんでした。
>レコードの最初から見てグループが変わったら最小と同様にレコードの最終から見てグループが変わったら最大です
目から鱗でした。こちらで実装いたしました。
SQLでここまでできるのかと感嘆しております。
こちらもじっくり勉強させていただきます。
りんご様のご指摘もっともです。
すみません、そちらは記載ミスです。
よろしくお願いいたします。