Propagate row data until next date is encountered

Database
Enthusiast

Propagate row data until next date is encountered

We don't have the ability to create stored procedures here so looking for an SQL solution to the following.  Given the following data, how can I propagate the noted rows for the date values until the next row is encountered ( dumbed down data ).  Alternatively, adding an END DATE to each data row would also suffice.  TIA

Name        Date            Amt

George    2013-08-01    12

George    2013-08-05    15

George    2013-08-10    8

Susan    2013-07-29    22

Susan    2013-08-03    90




Desired Result:

George    2013-08-01    12

George    2013-08-02    12 - generated row

George    2013-08-03    12 - generated row

George    2013-08-04    12 - generated row

George    2013-08-05    15

George    2013-08-06    15 - generated row

George    2013-08-07    15 - generated row

George    2013-08-08    15 - generated row

George    2013-08-09    15 - generated row





George    2013-08-10    8

George    2013-08-11    8 - generated row

George    .... until current date    8 - generated row






Susan    2013-07-29    22

Susan    2013-07-30    22 - generated row

Susan    2013-07-31    22 - generated row

Susan    2013-08-01    22 - generated row

Susan    2013-08-02    22 - generated row





Susan    2013-08-03    90

Susan    .... until current date     90  - generated row

8 REPLIES
Enthusiast

Re: Propagate row data until next date is encountered

The below post will give you some idea as how to start with...

http://forums.teradata.com/forum/database/handling-missing-dates

Senior Apprentice

Re: Propagate row data until next date is encountered

Hi Harry,

when you're on TD13.10 you can use EXPAND ON:

SELECT name, BEGIN(pd2) AS newdate, amt 
FROM
(
SELECT
name, datecol, amt,
PERIOD(datecol,
COALESCE(MIN(datecol)
OVER (PARTITION BY name
ORDER BY datecol
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), CURRENT_DATE)) AS pd
FROM tab
) AS dt
EXPAND ON pd AS pd2 BY INTERVAL '1' DAY
ORDER BY 1,2

 Dieter

Enthusiast

Re: Propagate row data until next date is encountered

Dieter:

I'd not heard of the PERIOD data type or the EXPAND ON, that's beautiful, thank you very much.  I'll decompose this and run it in bits to understand what it's doing.

Thanks.

Enthusiast

Re: Propagate row data until next date is encountered

This was working, but I've managed to break it.  Now generating the following error when creating the last table, TRAVEL_DAILY_OS_RESERVES.

Invalid Period value constructor. The beginning bound must be less than the ending bound.

I've dumped the TRAVEL_CUM_OS_RESERVES and no event_dt value > current_date.  Any ideas how to further debug this to determine why the PERIOD function is throwing this error?

The Teradata syntax is wrapped in SAS code since we access the DB through our SAS server.  The stuff in the execute() clause is native Teradata syntax.

Thanks

proc sql;

connect to teradata ( &password database = ddwv04i connection=global);

/* Create a temp table to house cumulative reserves by date */

execute(
create volatile table travel_cum_os_reserves as (

select r.clm_bnft_cse_id
, e.fncl_evnt_perd_strt_dt as event_dt

/* 2nd and subsequent parms order the detail data as it comes into the CSUM function */

, csum(case when txn_typ_cd = 'PMNT' then evnt_amt * -1 else evnt_amt end
, r.clm_bnft_cse_id
, e.fncl_evnt_perd_strt_dt
, e.travel_cse_prod_txt
, e.rsk_cd
, e.seq_no
) as os_reserve_amt

from ddwv04i.ins_clm_fncl_evnt e,
ddwv04i.ins_clm_bnft_cse_evnt_reltn r,
ddwv04i.ins_clm_bnft_cse c

where e.evnt_sys_src_id = 75
and e.evnt_sys_src_id = r.evnt_sys_src_id
and e.evnt_key_id = r.evnt_key_id
and e.txn_typ_cd in ( 'PMNT', 'ADJ' )
and e.txn_sub_typ_cd not in ( 'SUB' )
and e.fncl_evnt_perd_strt_dt <= cast ('2013-08-26' as date)

and c.clm_bnft_cse_id = r.clm_bnft_cse_id
and c.clm_bnft_cse_sys_src_id = r.evnt_sys_src_id
and c.snap_dt = '2013-08-26'
and c.clm_cse_ctr_cd = e.clm_cse_ctr_cd

group by r.clm_bnft_cse_id

/* We only want to keep the last row we've accumulated for this date */

qualify row_number() over
( partition by r.clm_bnft_cse_id
, e.fncl_evnt_perd_strt_dt
, e.travel_cse_prod_txt
, e.rsk_cd
order by e.seq_no desc
) = 1
)
with data primary index ( clm_bnft_cse_id )

on commit preserve rows
) by teradata;

execute ( commit work ) by teradata;

/* Expand the cumulative reserves table to include all dates */

execute(
create volatile table travel_daily_os_reserves as (

select clm_bnft_cse_id
, begin(event_dt2) as event_dt
, os_reserve_amt
from (
select clm_bnft_cse_id
, os_reserve_amt
, period(event_dt,
coalesce( min(event_dt)
over ( partition by clm_bnft_cse_id
order by event_dt
rows between 1 following and 1 following), current_date )) as period_dt
from travel_cum_os_reserves
) as dt
expand on period_dt as event_dt2 by interval '1' day

)
with data primary index ( clm_bnft_cse_id )

on commit preserve rows
) by teradata;

execute ( commit work ) by teradata;

quit;
Senior Apprentice

Re: Propagate row data until next date is encountered

Hi Harry,

there's an event_dt equal to current_date and the basic rule for periods is: end > begin.

You might have to change the COALESCE to CURRENT_DATE + 1.

Dieter

Enthusiast

Re: Propagate row data until next date is encountered

Thanks Dieter, but I've checked that, max(event_dt) is 2013-08-26, i.e. one day before current_date.

Still perplexed....

Enthusiast

Re: Propagate row data until next date is encountered

And there's no null event_dt values.  I tried current_date+1000000 and still got the 9103 error.  Could it be a data type issue, timestamp vs date or something weird ?  

I've since run this in SQL Assistant creating perm tables, travel_cum_os_reserves.event_dt is DATE, values from 1999/12/22 to 2013/08/26.

Enthusiast

Re: Propagate row data until next date is encountered

Found it.  Table travel_cum_os_reserves had some duplicate event_dt values within clm_bnft_cse_id due to the way the incoming detail data was ordered. Gave up on figuring out the order and added a sub-query to summarize by clm_bnft_cse_id & event_dt before doing the cumulative sum / expand.

Added the FOR clause to the EXPAND ON to limit the data expanded.  Very cool functionality, thanks Dieter.

create table dl_trav_data.hd_travel_cum_os_reserves as (

/* rows unbounded preceding creates cumulative sum by Case and date */
select clm_bnft_cse_id
, event_dt
, sum(evnt_amt)
over ( partition by clm_bnft_cse_id order by event_dt
rows unbounded preceding ) as os_reserve_amt
from (

/* Reserves are set at product / risk level, must first summarize by date for Case level outstanding reserves */

select r.clm_bnft_cse_id
, e.fncl_evnt_perd_strt_dt as event_dt
, sum(case when txn_typ_cd = 'PMNT' then evnt_amt * -1 else evnt_amt end) as evnt_amt

from dl_trav_data.hd_travel_case_active a,
ddwv04i.ins_clm_fncl_evnt e,
ddwv04i.ins_clm_bnft_cse_evnt_reltn r,
ddwv04i.ins_clm_bnft_cse c

where e.evnt_sys_src_id = 75
and e.evnt_sys_src_id = r.evnt_sys_src_id
and e.evnt_key_id = r.evnt_key_id
and e.txn_typ_cd in ( 'PMNT', 'ADJ' )
and e.txn_sub_typ_cd not in ( 'SUB' )
and e.fncl_evnt_perd_strt_dt <= '2013-08-27'

and a.clm_bnft_cse_id = r.clm_bnft_cse_id
and a.clm_bnft_cse_id = c.clm_bnft_cse_id

and c.clm_bnft_cse_id = r.clm_bnft_cse_id
and c.clm_bnft_cse_sys_src_id = r.evnt_sys_src_id
and c.snap_dt = '2013-08-27'
and c.clm_cse_ctr_cd = e.clm_cse_ctr_cd

group by 1,2
) as a
)
with data primary index ( clm_bnft_cse_id );

drop table dl_trav_data.hd_travel_daily_os_reserves ;

create table dl_trav_data.hd_travel_daily_os_reserves as (

select clm_bnft_cse_id
, os_reserve_amt
, begin(event_dt2) as event_dt
from (
select clm_bnft_cse_id
, os_reserve_amt
, period(event_dt,
coalesce( min(event_dt)
over ( partition by clm_bnft_cse_id
order by event_dt
rows between 1 following and 1 following), CURRENT_DATE)) as period_dt
from dl_trav_data.hd_travel_cum_os_reserves
) as dt
expand on period_dt as event_dt2 by interval '1' day for period(cast ('2011-10-30' as date),cast('2013-08-27' as date))

)
with data primary index ( clm_bnft_cse_id )