Using Max Date to Query

[#1]

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.

[#2]

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

[#3]

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

[#4 · Accepted solution]

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.

[#5]

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.

[#6]

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

[#7]

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

[#8]

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

[#9]

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

[#10]

@pmroz 

Thanks pmroz. Plain and easy code. Very helpful.