Hi Experts,
Can you help me with the below scenario?
Our table is daily load table and inserts the previous records whether there is a change or not.
I have below input and desired output. Can you suggest me with the Teradata SQL code? Thanks
INPUT :-
ACCT BAL_AMT START_TIME END_TIME
1234 50.00 2018-12-01 00:00:00 2018-12-01 23:59:00
1234 50.00 2018-12-02 00:00:00 2018-12-02 23:59:00
1234 50.00 2018-12-03 00:00:00 2018-12-03 23:59:00
1234 50.00 2018-12-04 00:00:00 2018-12-04 23:59:00
1234 30.00 2018-12-05 00:00:00 2018-12-05 23:59:00
1234 30.00 2018-12-06 00:00:00 2018-12-06 23:59:00
1234 0.00 2018-12-07 00:00:00 2018-12-07 23:59:00
1234 0.00 2018-12-08 00:00:00 2018-12-08 23:59:00
OUTPUT :-
ACCT BAL_AMT START_TIME END_TIME
1234 50.00 2018-12-01 00:00:00 2018-12-04 23:59:00
1234 30.00 2018-12-05 00:00:00 2018-12-06 23:59:00
1234 0.00 2018-12-07 00:00:00 2018-12-08 23:59:00
Solved! Go to Solution.
You can use NORMALIZE, but as it's based on Periods you must adjust the end date:
select ACCT, BAL_AMT, begin(pd), end(pd) - interval '1' minute from ( select normalize ACCT, BAL_AMT, period(START_TIME, END_TIME + interval '1' minute) as pd from tab ) as dt
You can use NORMALIZE, but as it's based on Periods you must adjust the end date:
select ACCT, BAL_AMT, begin(pd), end(pd) - interval '1' minute from ( select normalize ACCT, BAL_AMT, period(START_TIME, END_TIME + interval '1' minute) as pd from tab ) as dt
Same solution, but using NEXT and PRIOR functions to do the adjustment:
SELECT acct, bal_amt, BEGIN(pd) as start_time, PRIOR(END(pd)) as end_time FROM ( SELECT NORMALIZE acct, bal_amt, PERIOD(start_time, NEXT(end_time)) ) AS temp ORDER BY 3
Thank you so much Dieter! Awesome!
Thanks Fred! Both the solutions are working as expected!