Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Smearing start-end date data
Imagine you have some start-end data:
create table PRD_CAA_CRE_DDWSP_PI6_DPOL.TBL_DATA_1 as (
select ACCT_ID
, START_DATE
, END_DATE
, CURR_CRDT_LIM_AMT
from PRD_ADS_IL_VR.VR_S_ACCT_CRDT_CRD_RAW
sample 1000)
with data;
Step 1: create a calendar table that has one record for every date you want:
create table PRD_CAA_CRE_DDWSP_PI6_DPOL.T_CALENDAR as
(SELECT calendar_date as DAY_VAL
FROM SYS_CALENDAR.Calendar
where calendar_date > to_date('2016-09-01')
) with data;
Step 2: do a Cartesian join to “smear” the start-end formatted data
SELECT ACCT_ID
, DAY_VAL
, CURR_CRDT_LIM_AMT
FROM ( SELECT DAY_VAL FROM PRD_CAA_CRE_DDWSP_PI6_DPOL.T_CALENDAR ) A
, ( SELECT ACCT_ID, CURR_CRDT_LIM_AMT, START_DATE, END_DATE
FROM PRD_CAA_CRE_DDWSP_PI6_DPOL.TBL_DATA_1) B
WHERE DAY_VAL between START_DATE and END_DATE
-- order by ACCT_ID, DAY_VAL
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.