cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Sully
Level III

Using Max Date to Query

Hello,

I have a file that is updated weekly based on the Max Date in the date column. Each week I open the file, look at the Max Date currently and then query data that is after the Max Date. Would it be possible to just pull the Max Date out and have it inserted into the query that I am using?

 

Currently my file is updated to 8/15/2019 and I would like to query data that is > 8/15/2019 to update the file.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Using Max Date to Query

Using my previous example, if you continue with:

val = Column( 1 )[1];

show(val);

Show( As Date( val ) );

sql = eval insert("select * where purchase_date > ^As Date(val)^");

show(sql);

 

Then the log shows:

val = 3376598400;
As Date(val) = 31Dec2010;
sql = "select * where purchase_date > 31Dec2010";

 

so you could then run the resulting sql.

 

View solution in original post

9 REPLIES 9

Re: Using Max Date to Query

Without knowing the specifics, it seems like you should be able to keep this all within one query.  However, you can always use Query Builder to do a Max on the table in question with the dates, and then extract that value to substitute in another piece of SQL Script.  An example of the script from Query Builder working on the movie database, Rentals table where the date column has the date of rental, the script emitted would be like:

 

New SQL Query(
	Connection( "ODBC:DSN=SQLServer SQBTest;UID=sa;PWD=%_PWD_%;APP=JMP;WSID=machine;DATABASE=SQBTest;" ),
	QueryName( "g6_Rentals" ),
	Select(
		Column(
			"OrderDate",
			"t1",
			Alias( "Maximum-OrderDate" ),
			SavedJMPName( "OrderDate" ),
			Aggregation( "Maximum" ),
			JMP Name( "Maximum-OrderDate", 0 ),
			Numeric Format( "m/d/y", "-1", "NO", "" )
		)
	),
	From( Table( "g6_Rentals", Schema( "SQBTest" ), Alias( "t1" ) ) )
) << Run Foreground();

val = Column( 1 )[1];

Show( As Date( val ) );

 

 

Here was are just doing an Aggregate of Maximum on the date column.  The resulting table has one row and column, and we take the value out of that and show it to the log to make sure it is correct.

 

Brian Corcoran

JMP Development

Sully
Level III

Re: Using Max Date to Query

Would you be able to show how you would insert the Max Date into the next SQL from the first table?

The table I am using has a column labeled as Purchase_Date which is what I would do the maximum on in order to query new data from the database. An example of the query is below:

 

dbc = create database connection("DSN=PROD; UID="||IDC||" ;PWD= "||PASSS||" ;MODE=SHARE;DBALIAS=PROD");

data_sql = evalinsert("

Select CUST_NM, PURCHASE_AMT, PURCHASE_DATE, PURCHASE_TM, ORDER_NUM, PRODUCT_NM, PRODUCT_TYP, COUNTRY_CD, APPROVE_DECLINED

from PURCHASE_DATA

where PURCHASE_DATE > '2019-08-16'

and PRODUCT_TYP = 'Y'

and COUNTRY_CD in ('0840')

and APPROVE_DECLINED ='A'");

data_dt = execute sql (dbc, data_sql);

close database connection (dbc);

 

Re: Using Max Date to Query

Using my previous example, if you continue with:

val = Column( 1 )[1];

show(val);

Show( As Date( val ) );

sql = eval insert("select * where purchase_date > ^As Date(val)^");

show(sql);

 

Then the log shows:

val = 3376598400;
As Date(val) = 31Dec2010;
sql = "select * where purchase_date > 31Dec2010";

 

so you could then run the resulting sql.

 

Sully
Level III

Re: Using Max Date to Query

Thanks Brian, that works as expected. Is it possible to have the val reformatted in a numeric format (such as 12/31/2010, or 31/12/2010)? Having a character such as Dec in the date doesn't work for my query.

Re: Using Max Date to Query

Modifying my example from before, you could do:

 

val = Column( 1 )[1];

dv = As Date( val );
dvf = Format( dv, "m/d/y" );

Show( dvf );

sql = Eval Insert( "select * where purchase_date > ^dvf^" );

Show( sql );

 

Sully
Level III

Re: Using Max Date to Query

Hi Brian,

 

I want to add single quotes around the Date (needed to query from a particular DB). Would I just add the single quote directly to the eval?

 

Example:

sql = Eval Insert( "select * where purchase_date > '^dvf^'" );
Sully
Level III

Re: Using Max Date to Query

Hi Brian,

 

I was actually able to get it running by editing the lines you have already provided. I just added an extra line to the val portion to include the single quotes. Updated val with single quotes below, feel free to critique if needed.

 

val = Column( 1 )[1];

dv = As Date( val );

dvf = Format( dv, "yyyy-mm-dd" );

dvfs = ("'" || dvf || "'");

Show( dvfs );

sql = Eval Insert( "select * where purchase_date > ^dvfs^" );

 

pmroz
Super User

Re: Using Max Date to Query

If you're using Oracle you might consider using TO_DATE on your date field so that the date format is unambiguous.  If you're using SQL Server yyyy-mm-dd is the default format so no conversion is necessary.

dv = today();
dvf = Format( dv, "yyyy-mm-dd" );

sql = Eval Insert( "select * where purchase_date > TO_DATE('^dvf^', 'yyyy-mm-dd')" );
show(sql);
Thomas1
Level V

Re: Using Max Date to Query

@pmroz 

Thanks pmroz. Plain and easy code. Very helpful.