我有一个请求,要在Oracle表中生成一个包含以下数据的报告:只是一个成员的示例。
MEMBER_ID START_DATE END_DATE ADDRESS1 ADDRESS2 CITY STATE LAST_UPDATED
12345 1/1/2019 12/31/9999 1 Test Ave Apt 111 City AA 3/4/2020
12345 1/1/2019 12/31/9999 2 Test Dr Apt 222 City AA 9/5/2019
12345 1/1/2019 12/31/9999 1 Test Ave APT 111 City AA 6/3/2019
12345 1/1/2019 12/31/9999 3 Test TRL City AA 3/3/2019
我希望这是我从上面的数据报告的输出:
MEMBER_ID START_DATE END_DATE ADDRESS1 ADDRESS2 CITY STATE LAST_UPDATED
12345 10/1/2019 12/31/9999 1 Test Ave Apt 111 City AA 3/4/2020
12345 7/1/2019 9/30/2019 2 Test Dr Apt 222 City AA 9/5/2019
12345 4/1/2019 6/31/2019 1 Test Ave APT 111 City AA 6/3/2019
12345 1/1/2019 3/31/2019 3 Test TRL City AA 3/3/2019
有人能帮忙吗?我试过Dense_rank,但就是想不出一个正确的逻辑。就像如果一位成员再次更改地址一样,我也需要在报告中加入最新的更改。
您似乎希望记录在last_updated
列的月份的最后一天结束。然后第二天开始。
使用lag()
:可以轻松处理
select t.*,
( lag(last_day(last_updated)) over (partition by member_id order by last_updated) +
interval '1' day
) as new_start_date,
last_day(last_updated) as new_end_date
from t;
我认为您需要一个上一次更新日期的季度开始和结束日期作为开始和结束时间。
Select member_id,
Trunc(last_updated,'Q') as start_date,
case
when extract(month from Trunc(last_updated,'Q')) = 12
then end_date
else Add_months(Trunc(last_updated,'Q'), 3) - 1
end as end_date,
.....
From your_table