cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • Image to Table JMP extension imports single or batch images as data tables. Useful to extract curves from pictures or convert image data to curve data to use in analyses like Functional Data Explorer. Download and install the extension.

Discussions

Solve problems, and share tips and tricks with other JMP users.
Choose Language Hide Translation Bar
UserID16644
Level V

How to loop when querying date?

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" );

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: How to loop when querying date?

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" );

); 

 

Jim

View solution in original post

2 REPLIES 2
txnelson
Super User

Re: How to loop when querying date?

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" );

); 

 

Jim
jthi
Super User

Re: How to loop when querying date?

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).

-Jarmo

Recommended Articles