Populate Dates Between Start & End Dates

Requirement

This blog is intended to show how we can fill the dates if dates are saved in two columns as StartDate or EndDate ( Refer Sample Data of Date Rang Table Screenshot).

We have one Transaction table which is having data for each day (Example: PGI_DATE).

In another table, data is entered by date range.

Sample Data of Date Range Table

Sample Data From Transaction Table

Now requirement is to populate PERIODID in the output if our transaction date falls under the given date range of any PERIODID.

Example –

1. If PGI_DATE is 01-Dec-2019 then Pick Period ID 53119

2. If PGI_DATE is 02-Dec-2019  or 03-Dec-2019 then Pick Period ID 53120

Solution 1 – Using Cursor in Table Function

We can create table function and use cursor in the table function. Here is the code.

FUNCTION "AP_CUSTOM"."KCC.AP_CUSTOM.IBP::TF_IBP_DATE_PERIOD_RANGE" ( )

       RETURNS TABLE (PERIODSTART DATE, PERIODEND DATE, PERIODID VARCHAR (5), DAY_DATE DATE)

       LANGUAGE SQLSCRIPT

       SQL SECURITY INVOKER AS

BEGIN

/*****************************

       Write your function logic

******************************/

DECLARE v_StartDT DATE;

DECLARE v_EndDT DATE;

DECLARE v_PeriodID VARCHAR (5);

DECLARE v_FinalResult TABLE (PERIODSTART DATE, PERIODEND DATE, PERIODID VARCHAR (5), DAY_DATE DATE);

-- Declare Cursor to Fetch Records from Date Range Table

DECLARE CURSOR c_Date FOR SELECT PERIODSTART,PERIODEND,PERIODID FROM AP_CUSTOM.AP_IBP_CT_PERIOD_ID;

-- Read Cursor Row One by One. 

FOR cur_row as c_Date DO

-- Select PERIODSTART & PERIODEND From Date Range Table which coming in Each Cursor Row

SELECT PERIODSTART, PERIODEND, PERIODID INTO v_StartDT, v_EndDT, v_PeriodID FROM AP_CUSTOM.AP_IBP_CT_PERIOD_ID WHERE PERIODID = cur_row.PERIODID;

-- Select Date from Time Dimension Table by Adding Filter on DATE_SQL Column

v_TblOut = SELECT v_StartDT AS "PERIODSTART", v_EndDT AS "PERIODEND", v_PeriodID AS "PERIODID", DATE_SQL AS "DAY_DATE" FROM _SYS_BI.M_TIME_DIMENSION WHERE DATE_SQL >= v_StartDT AND DATE_SQL <= v_EndDT;

-- Insert Output Into Table Variable 

INSERT INTO :v_FinalResult SELECT PERIODSTART, PERIODEND, PERIODID, DAY_DATE FROM :v_TblOut;

END FOR;

-- Return Final Result From Table Variable (:v_FinalResult) as Output

RETURN SELECT PERIODSTART, PERIODEND, PERIODID, DAY_DATE FROM :v_FinalResult;

END;

Solution 2 – Using Select Statement in Table Function

Another option is to use Select Statement. But this approach has one drawback that it will Create Cross Join between Time Dimension and Date Range table.

Also, Performance will not be optimal in this as compared to approach 1.

But cursor will execute number of times same as the records available in Date Range Table.

Example:

Period in Date Range Table 10.

Data Pulled from TIME_DIMENSION Table for 1 Year (365 Day)

Total Number of Rows will be processed by Query will be – 10 * 365 = 3650

Here is the Sql Statement

-- Use this Code In Table Function

SELECT AA."PERIODSTART",AA."PERIODEND",AA."PERIODID",TT.DATE_SQL AS "DAY_DATE"

FROM XX.DATE_RANGE_TABLE AA

INNER JOIN  _SYS_BI.M_TIME_DIMENSION TT

ON (TT.DATE_SQL >= AA."PERIODSTART" AND TT.DATE_SQL <= AA."PERIODEND")

ORDER BY TT.DATE_SQL;

Join above result with Transaction table (PGI_DATE Left Outer Join On DATE_SQL) and fetch PERIODID in the output.