Iterate through range of dates

[cognosbadri]

Hello

We have requirement to loop through source JSON data & fetch for every 5 years data starting 1980-01-01 till yesterday. we currently loop it by passing static dates like below & run through it through muliple times

SQL SELECT
col1,

col2
FROM JSON (wrap on) "root"
WITH CONNECTION (
URL "http://<URL>/tagid?id=<EndPoint>/1%3FFromDate=1980-01-01%26ToDate=1985-12-31"
);

we want to convert into For Loop & increment the start date & end date once 1st iteration is done, can somebody help it out?

@sunny_talwar

@swuehl

@hic

@Gysbert_Wassenaar

@Clever_Anjos

@tresB

@marcus_sommer

[marcus_sommer]

Maybe something like this:

for i = 1980 to year(today()) step 5
   let vUrl = '"http://<URL>/tagid?id=<EndPoint>/1%3FFromDate=' & date(makedate($(i)), 'YYYY-MM-DD') & '%26ToDate=' & date(makedate($(i)+4, 12, 31), 'YYYY-MM-DD') & '"';

SQL SELECT
col1,

col2
FROM JSON (wrap on) "root"
WITH CONNECTION (
URL $(vUrl)
);

next

You might need to adjust a bit because your example-dates would return 6 years and not 5.

- Marcus