Skip to content

Instantly share code, notes, and snippets.

@standarderror

standarderror/smear.sql

Created August 29, 2017 23:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save standarderror/b83f4adf2b8aaaf5b24bec9ef418ed7d to your computer and use it in GitHub Desktop.
Save standarderror/b83f4adf2b8aaaf5b24bec9ef418ed7d to your computer and use it in GitHub Desktop.
Download ZIP
Smearing start-end date data
Raw
smear.sql
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