topic Re: Interactive date selection and filtering by SQL in Discussions

Interactive date selection and filtering by SQL

AT — Mon, 18 Dec 2017 22:24:15 GMT

I like to use JMP interative to get input start and end time and then use SQL to filter the data table. The parameter dateb and datee if defined as for instance '2017-10-10', '2017-11-11' works fine but when use assigned varaible dateb and datee, I don't get the correct filtering (empty tables).

I appreciate any help you can provide. Thanks.

------------------------------------

defuplim = Today();

deflowlim = Today() - In Days( 730 );

ww = New Window( "Pick Your Dates in yyyy-mm-dd format",

<<Modal,

<<return result,

V List Box(

Text Box( "Start Date:" ),

str1 = Number Edit Box(

deflowlim,

10,

<<SetFunction(

Function( {that},

lowlim = str1 << get

)

Text Box( "End Date:" ),

str2 = Number Edit Box(

defuplim,

10,

<<SetFunction(

Function( {this},

uplim = str2 << get

)

str1 << set format( Format( "yyyy-mm-dd" ) ),

str2 << set format( Format( "yyyy-mm-dd" ) )

H List Box( Button Box( "OK" ), Button Box( "Cancel" ) ),

);

dateb = As date(ww["str1"]);

datee = As date(ww["str2"]);

dateb = format(dateb,"yyyy-mm-dd");

datee = format(datee,"yyyy-mm-dd");

dt1 = Open Database(

"DSN=XXX;UID=root;PWD=xxxxx;",

"Select * from xyz where date(starttime) between 'dateb' and 'datee'","TMP"

);

Re: Interactive date selection and filtering by SQL

pmroz — Mon, 18 Dec 2017 22:51:12 GMT

You need to convert your string dates back into dates for the database to understand them. If this is Oracle you need a statement like:

sql_statement = evalinsert(
"Select * 
   from xyz 
  where date(starttime) between to_date('^dateb^', 'YYYY-MM-DD')
                            and to_date('^datee^', 'YYYY-MM-DD') ");

dt1 = Open Database("DSN=XXX;UID=root;PWD=xxxxx;", sql_statement);

Re: Interactive date selection and filtering by SQL

txnelson — Mon, 18 Dec 2017 23:00:44 GMT

See if this modification will work

defuplim = Today();
deflowlim = Today() - In Days( 730 );

ww = New Window( "Pick Your Dates in yyyy-mm-dd format", 
	<<Modal, 
	<<return result, 
	V List Box(
		Text Box( "Start Date:" ), 
		str1 = Number Edit Box(
			deflowlim, 
			10, 
			<<SetFunction(
				Function( {that}, 
					lowlim = str1 << get
				)
			)
		), 
		Text Box( "End Date:" ), 
		str2 = Number Edit Box(
			defuplim, 
			10, 
			<<SetFunction(
				Function( {this}, 
					uplim = str2 << get
				), 
			)
		), 
		str1 << set format( Format( "yyyy-mm-dd" ) ), 
		str2 << set format( Format( "yyyy-mm-dd" ) )
	), 
	H List Box( Button Box( "OK" ), Button Box( "Cancel" ) ),        
);

dateb = As Date( ww["str1"] );
datee = As Date( ww["str2"] );

dateb = Format( dateb, "yyyy-mm-dd" );
datee = Format( datee, "yyyy-mm-dd" );

Eval(
	Parse(
		"dt1 = Open Database(
	\!"DSN=XXX;UID=root;PWD=xxxxx;\!", 
	\!"Select * from xyz where date(starttime) between '"
		 || dateb || "' and '" || datee || "',\!"TMP\!");"
	)
);

Re: Interactive date selection and filtering by SQL

AT — Tue, 19 Dec 2017 00:34:30 GMT

Thanks so much Jim. It worked perfectley.