SQLでのデータ作成

Question

前提

SQL(Oracle)でデータを編集しています。
想定したデータにならず、お知恵をお借りできますと幸いです。
以下の私が考えていた以外の方法でも、やり方があればご教示いただきたく思います。
よろしくお願いします。

実現したいこと

• 下記のTable2を作成する

週次でシステムからCaseのデータを記録しており、以下のTable1のようなデータを作成しています。ですが、システム上クローズ後には翌週以降のデータが作成されなくなるため、Table2のようなデータを作ろうとしています(1/22にクローズすると1/29のデータができなくなる）。データの記録日は毎月1、8、15、22、29（2月は28日）です。

■Table1
Case No 　date　　　stage 　　last flag
Case1 　20220101 　ステージ1 　N
Case1 　20220108 　ステージ1 　N
Case1 　20220115 　ステージ2 　N
Case1 　20220122 　ステージ4 　N
Case1 　20220129 　ステージ5 　N
Case1 　20220201 　クローズ 　　Y

Case2 　20220108 　ステージ3 　N
Case2 　20220115 　ステージ4 　N
Case2 　20220122 　クローズ 　　Y

■Table2
Case No 　date　　　stage 　　last flag
Case1 　20220101 　ステージ1 　
Case1 　20220108 　ステージ1 　
Case1 　20220115 　ステージ2 　
Case1 　20220122 　ステージ4 　
Case1 　20220129 　ステージ5 　
Case1 　20220201 　クローズ 　　
Case1 　20220208 　クローズ 　　
以下続く

Case2 　20220108 　ステージ3 　
Case2 　20220115 　ステージ4 　
Case2 　20220122 　クローズ 　　
Case2 　20220129 　クローズ 　　
Case2 　20220201 　クローズ 　　
Case1 　20220208 　クローズ 　　
以下続く

該当のソースコード

SELECT
　dt.date
　tr.*
from 日付マスタ dt
   LEFT JOIN  Table1 tr
   ON dt.date = tr.date
; 

Create TABLE Table2

試したこと

日付マスタを作ってそれを左表にしてTable1と結合すれば、日付の右にNULLのセルができ、そこに、Table1のLast flagがついているデータを追加すればできるかと思っていたのですが、うまくいかずどうすればよいか困っているところです。上のSQＬで下のような表ができたらいいなと思っていまいた。

イメージ
Date 　　Case No 　date　　　stage 　　last flag

20220108 　Case2 　20220108 　ステージ3 　N
20220115 　Case2 　20220115 　ステージ4 　N
20220122 　Case2 　20220122 　クローズ 　　Y
20220129 　
20220201 　

Last flag　Yのみを集めたTableもあり、ここから上表のnullを埋めようとしていましたが、上表が作れず挫折しています。
Case1 　20220201 　クローズ 　　Y
Case2 　20220122 　クローズ 　　Y

よろしくお願いします。

Comments

[sazi]

> システム上クローズ後にはデータが作成されなくなる
Table1から削除されるという事ですか？

[Orlofsky]

質問は修正できます。
ここの掲示板ではコードやエラーメッセージはMarkdownの[コード]で提示してください。
https://teratail.com/help/question-tips#questionTips37

[nomuyuka]

> sazi san
ご質問ありがとうございます。わかりづらい記載ですいません。

CaseのステージがTable1に週次で記録され、ステージがクローズになるとクローズになった週だけそのデータができて、それ以降の週にはデータが作成されなくなります。

例えば、
1/15　ステージ3
1/22　クローズ
の場合、Caseのステージは翌週の1/29もクローズの状態ですが、1/29分の行は作成されず1/22までしか行が追加されません。また、1/22のデータは残ります。

以上で説明になっていますでしょうか。
よろしくお願いします。

[nomuyuka]

>Orlofsky san
ご指摘ありがとうございました。
修正しました。
どうぞよろしくお願いいたします。

Answer 1

日付マスタにはデータを記録する日付が格納されているということでよいでしょうか? やりかたはいろいろあると思いますが、たとえば以下のようなクエリで実現できるかと思います。

SELECT A.CaseNo, B.Dt, SUBSTR(MAX(TO_CHAR(A.Dt, 'YYYYMMDD') || '@' || A.Stage), 10) LastStage
FROM Table1 A
JOIN 日付マスタ B ON A.Dt <= B.Dt
GROUP BY A.CaseNo, B.Dt
ORDER BY A.CaseNo, B.Dt

Comments

[nomuyuka]

neko_the_shadowさま
ご回答ありがとうございました。また、返信とお礼が遅くなりまして失礼しました。
やってみたところ、SQL自体はうまく回ったのでデータの中身を検証しているところです。量が多くまだすべて見切れていないのですが、おおむねうまくいってそうです。

>日付マスタにはデータを記録する日付が格納されているということでよいでしょうか?
おっしゃる通り、日付マスタにはデータの記録日だけが格納されています。
2022/02/01,2022/02/08/,2022/02/15　みたいな感じです。

今回はありがとうございました。大変助かりました。

Answer 2

有効範囲の結合条件になるように考えます。
範囲を生成するには、集計関数よりコストの低い、Window関数のLead()を使用します。

SELECT dt.date, tr.*
from  日付マスタ dt 
      left join (
        select * 
             , lead(date, 1, 99991231) over(partition by CaseNo order by date) as LeadDate 
        from Table1
      ) tr
      ON dt.date >= tr.date and dt.date < tr.LeadDate

Comments

[nomuyuka]

sazi様
回答ありがとうございます。また、参考URLもいただきまして、大変助かります。
いただいた回答を基に、実際に動かしてみたのですが、以下のエラーが出てしまいうまくいきませんでした。
エラー
ORA-00923: FROM keyword not found where expected

自分でも、fromやかっこの位置やカンマなどを変えて試してみたのですが、うまくいかずにいます。
いただいたものをコピーするなどしたのですが、記載で間違いやすいところなどありますでしょうか。
よろしくお願いします。

[sazi]

記述のエラーなので、実際に記述したSQLを見ないとアドバイスは難しいです。

[nomuyuka]

saziさま
おっしゃる通りですね。もう一度やってみます。
Window関数など今まで使ったことがない知識もいただきましたので、改めて勉強したいと思います。
ありがとうございました。

[nomuyuka]

saziさま
その後、やり直してみてうまくいきました。私の方のデフォルトの日付形式の設定とあっていないのが原因でした。
ありがとうございました。