How to loop when querying date?

[#1]

Hi all

I am trying to query the database using JMP. In my query, I manually change the dateNow value from day 1 to 30 then run the script. But I needed to create another one that would loop all through the days.

How can I create a loop that queries every date starting from July 2023 to January 2024?

dateNow = "01/11/2024";

dt = Open Database( "DSN=databasename;UID=root;PWD=admin;APP=JMP;DATABASE=dbSample").
"select distinct name, age, sex, address
from db1
where date between '" || dateNow || " 00:00:00 AM' and '" || dateNow || " 11:59:59 PM'",  
"raw" );

[#2 · Accepted solution]

Here is my solution.  

Names Default To Here( 1 );


For( theDate = Informat( "01/07/2023", "d/m/y" ), theDate <= Informat( "31/1/2024", "d/m/y" ),
	theDate = theDate + In Days( 1 ),
	
	// Create the dateNow variable from the index variable "theDate"
	dateNow = Format( theDate, "d/m/y" );

dt = Open Database( "DSN=databasename;UID=root;PWD=admin;APP=JMP;DATABASE=dbSample").
"select distinct name, age, sex, address
from db1
where date between '" || dateNow || " 00:00:00 AM' and '" || dateNow || " 11:59:59 PM'",  
"raw" );

); 

[#3]

Depending on your sql you might be able to deduct index from the date (might require trunc / modifying your query a bit)

Names Default To Here(1);

start_date = 01Jul2023;
end_date = 31Jan2024;

datecount = Date Difference(start_date, end_date, "Day");

Show(As Date(start_date + In Days(datecount)));

sql_template = "\[select sysdate - ^i^ startdate, sysdate - ^i - 1^ enddate from dual]\";

For(i = 1, i <= datecount, i++,
	cur_sql = Eval Insert(sql_template);
	Show(cur_sql);
);

Also you most likely want to concatenate the query results inside the query and adding some simple print logging can be helpful (I usually print at least: current index/date which is being queried, how many rows you got back and how long the query took).