如果30天内有新记录使用相同的POS订单ID，我需要写一个查询，将以前的记录标记为"不合格"

Question

我需要编写一个查询，以检索表中具有POS_ORDER_ID的记录，该记录具有相同的POS_ORDER_ID，在30天内作为状态为"已取消"、"已终止"的新记录出现，并且需要将以前的POS_ORDER_ID记录标记为不合格

表列：

POS_ORDER_ ID，地位订单日期，错误description

Answer 1

包含MAX()和ROW_NUMBER()分析函数的查询可能会对您有所帮助，例如：

with t as
(
select t.*,
       row_number() over (partition by pos_order_id order by Order_date desc ) as rn,
       max(Order_date) over (partition by pos_order_id) as mx
  from tab t -- your original table
)
select pos_order_id, Status, Order_date, Error_description, 
       case when rn >1 
              and t.status in ('Canceled','Discontinued') 
              and mx - t.Order_date <= 30 
             then 
                'Not eligible' 
             end as "Extra Status"
  from t

演示

Answer 2

请使用以下查询，

选择并验证

select POS_ORDER_ID, Status, Order_date, Error_description, row_number() 
over(partition by POS_ORDER_ID order by Order_date desc)
from table_name;

更新查询

merge into table_name t1
using
(select row_id, POS_ORDER_ID, Status, Order_date, Error_description, 
row_number() over(partition by POS_ORDER_ID order by Order_date desc) as rnk
from table_name) t2
on (t1.POS_ORDER_ID = t2.POS_ORDER_ID and t1.row_id = t2.row_id)
when matched then
update
set
case when t2.rnk = 1 then 'Canceled' else 'Not Eligible';