topic Re: Simple data range question in Data Movement & Streaming

Simple data range question

karvagear — Wed, 19 Mar 2025 22:43:16 GMT

Hi guys,

i'm stucking ni this simple situation where i need ta have bars from a date range (2014-now) but not all date have data in them, how do i generate a local autogenareted table with a date range?

in Oracle i would do someting like this:

SELECT

(to_date(sysdate,'DD-MM-YYYY') - level + 1) AS day

FROM

dual

CONNECT BY LEVEL <= (to_date(sysdate,'DD-MM-YYYY') - to_date('01-01-2014','DD-MM-YYYY') + 1);

Re: Simple data range question

PrashantSangle — Sat, 02 Dec 2023 09:22:37 GMT

Let vMinDate = Date(Date#('01-01-2014','DD-MM-YYYY'));

Let vMaxDate = Today();

Load

Date($(vMinDate) + IterNo() - 1) as Date_Field

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Regards,

Prashant Sangle

Re: Simple data range question

sidhiq91 — Sat, 02 Dec 2023 13:38:11 GMT

@karvagear You can use the below script too.

Let Vmin = Date(Date#('01/01/2014','MM/DD/YYYY'));
Let Vmax= Today();

Trace >>>> $(Vmin);
Trace >>>> $(Vmax);

Temp:
Load

Num('$(Vmin)'+IterNo()-1) as Date_Num
AutoGenerate 1
while Num('$(Vmin)'+Iterno()-1)<=Num('$(Vmax)');

Date:
Load Date(Date_Num,'MM/DD/YYYY') as Date
Resident Temp;

Drop table Temp;

Exit Script;