我在Redshift/PostgreSQL中有一个报告交易的表,我不时上传一个报告,其中包含发票及其到期和付款日期,如下所示:
<表类>
invoice_Id
report_date
due_date
paid_date
tbody><<tr>A1 08-26-2021 09-02-2021 08-25-2021 A2 08-26-2021 08-29-2021 A3 08-26-2021 09-05-2021 A4 08-26-2021 08-30-2021 A108-28-2021 09-02-2021 08-25-2021 A2 08-28-2021 08-29-2021 A3 08-28-2021 09-05-2021 08-27-2021 A4 08-28-2021 08-30-2021 A108-30-2021 09-02-2021 08-25-2021 A2 08-30-2021 08-29-2021 A3 08-30-2021 09-05-2021 08-27-2021 A4 08-30-2021 08-30-2021 表类>
你能试试吗?
-
第一步是基于日期创建排名。
-
然后获得最大排名,然后将每个id的最大排名与我们在第一个CTE中创建的每个排名列进行比较
-
如果rank与max rank相同,则执行当前数据条件,否则执行正常条件
WITH ranked_date as (
select
*,
row_number() over(partition by invoice_id order by report_date) as ranked_date
from [table name]
),
max_rank as (
select
invoice_id,
max(ranked_date) as max_ranked_date
from ranked_date
group by 1
)
select
ranked_date.invoice_id,
ranked_date.ranked_date,
CASE
WHEN ranked_date.ranked_date = max_rank.max_ranked_date THEN
CASE
WHEN get_date() > due_date THEN 'PAST_DUE'
WHEN get_date() <= due_date THEN 'PENDING'
END
WHEN ranked_date.ranked_date != max_rank.max_ranked_date THEN
CASE
WHEN report_date > due_date AND paid_date is null THEN 'PAST_DUE'
WHEN report_date <= due_date THEN 'PENDING'
END
ELSE 'NO CONDITION SPECIFIED'
END AS new_status
FROM ranked_date
INNER JOIN max_rank
ON ranked_date.invoice_id = max_rank.invoice_id