【OracleSQL】本来あるべきデータが存在しない場合にSQL文で対象を抽出したい

Question

前提・実現したいこと

下記のBALANCEテーブルからデータ抽出をしたいのですが、なかなかうまくいかないのでご教示ください。

• レコードを新規作成した場合、UP_DATEは空です。
• データ更新した場合、同一NO内の最新レコードのAMOUNTと同額のマイナス数値をAMOUNTにセット、PM_CDに2をセット、UP_DATEに更新を行った当日日付がセットされます。

一言でまとめると、最新レコードの金額を打ち消すレコードが挿入されます。(データB ～ Cなど)

• その後、データ更新したレコードが別途入ります。(データDなど)
• この時、SEQに打ち消し対象になったレコード(データB)と打ち消し用のレコード(データC)が紐づく番号がセットされます。
• 別途データ更新したレコード(データD)には次のSEQ番号がセットされます。(データB ～ Dなど)
• データ削除の場合、打ち消しデータのみレコード挿入され、金額更新するレコードは入りません。(データI ～ L)

||NO|PM_CD|AMOUNT|SEQ|UP_DATE|
|:--|:--:|--:|
|A|10000|1|500|1||
|B|12345|1|1000|1||
|C|12345|2|-1000|1|2020/2/18|
|D|12345|1|2000|2|2020/2/18|
|E|55555|1|3000|1||
|||||||
|F|55555|1|4000|2|2020/2/18|
|G|55555|2|-4000|2|2020/2/18|
|H|55555|1|5000|3|2020/2/18|
|I|77777|1|6000|1||
|J|77777|1|6000|1||
|K|77777|2|-6000|1|2020/2/18|
|L|77777|2|-6000|1|2020/2/18|
|M|98765|1|7000|1||
|||||||
|N|98765|1|8000|2|2020/2/18|

このような状態で、EとFの間にEを打ち消すためのAMOUNT-3000のレコードが存在していない場合、
該当のNOを抽出するSQLを作成したいと考えています。

レコード抽出の流れとしては下記で考えていますが、うまく抽出出来ていません。

①『UP_DATE = システム日付』のレコードを参照
⇒C , D , F , G , H , K , L , N

② ①で取得したデータの『同一NOのレコードを参照し、同一SEQの金額の合計が0ではない場合』データを取得
ただし『同一NO内でのSEQがMAX値のデータは対象外』とする
⇒E , M

抽出結果：55555 , 98765

一番左のA～Nは説明する上で振ったものなので、実際には無視してください。
また、EとF及びMとNの間は空行が入っているわけではなく、実際には詰まっています(視認性の考慮なので特に意味はありません)。

使用しているバージョンはOracle12cです。

試したこと

①はSYSDATEを参照すれば良いのですが、②は色々試したものの形になりませんでした。

select * from BALANCE
where trunc(SYSDATE) = trunc(UP_DATE)

そもそもあまりSQLでやらせる内容ではないと思ってはいますが。。。
よろしくお願い致します。

Comments

[Orlofsky]

テーブル定義は CREATE TABLE文に修正してください。

[Orlofsky]

>レコードを新規作成した場合、UP_DATEは空です。
質問にその時のINSERT文を提示してしてください。

Answer 1

あまり検証はしていませんが、サンプルデータからは希望の結果にはなります。
最大のSEQ以外で同じNoとSEQを合算して0以外を抽出しています。

select  No, SEQ
from  BALANCE t1
where exists( --最大のSEQではないもの
        select 1 from BALANCE where No=t1.NO and seq>t1.seq
      )
group by  No, SEQ
having  sum(AMOUNT)!=0

※UP_DATEがどんな組み合わせであれ、NoとSEQの組み合わせの方が優先されるものとして、敢えてUP_DATEの条件は入れていません。

Comments

[manymany]

回答ありがとうございます。
条件で言及していませんでしたが
本テーブルのデータ数が多く、その日にデータ更新を行ったレコードのNOを参照したかったため
UP_DATEで絞り込みたかったものになります。

ご教示頂いたSQLに日付の絞り込みを加えることで想定通りの結果が得られました。

select No, SEQ
from BALANCE t1
where exists(
select 1 from BALANCE where No=t1.NO and seq>t1.seq and trunc(SYSDATE) = trunc(UP_DATE)
)
group by No, SEQ
having sum(AMOUNT)!=0

ありがとうございました。