Dimensional table from Daily incremental table ( Pick start date and End Dates )

[steelers0928]

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

[dnoeth · Accepted 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

[Fred]

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

[steelers0928]

Thank you so much Dieter!  Awesome! 

[steelers0928]

Thanks Fred! Both the solutions are working as expected!