作者:最低调的鹌鹑 | 来源:互联网 | 2022-12-02 17:14
以下帐户于2014年1月19日关闭,然后在2/27/2018重新开放,然后在3/26/2018再次关闭.如何在打开和关闭此帐户时编写要捕获的sql.closerestrictind ='C'是帐户关闭的时间.
我们正在数据仓库中工作,每天都会加载数据以捕获所有历史记录.
对于acct 1234,它应该是这样的:
closed on 1/19/2018
re-opened on 2/27/2018
closed on 3/26/2018
谢谢!
1> Gordon Linof..:
你可以使用lag()
:
select acctnbr, effectivedate,
(case when closerestrictedind = 'C' then 'closed' else 'opened' end) as action
from (select t.*, lag(closerestrictedind) over (partition by acctnbr order by effectivedate) as prev_cr
from t
) t
where prev_cr <> closerestrictedind;