Help with ExecuteSQL Script Step, Date Range

[William Block]

Hi,

 

I'm having trouble using the WHERE command to allow a User to dynamically select a date range. The process works well for a single date. But not when I try to establish a date start & date end. The scripts are below. The first one works, second doesn't. Any help would be greatly appreciated!

 

(Note: there is one additional script that kind of "kick starts" the main script. It contains an optional script parameter: INVOICE::virtual_salesboard_date_start)

 

 

 

(first line in the script is Set Variable [$date;Value:Get(ScriptParameter)

// ------------  BEGIN EXECUTESQL BLOCK ------------  

Let ( [

ReturnSub = "n" ; // We need to swap out carriage returns in your results with a different character, so specify the character here.  n is the default.
SQLResult = ExecuteSQL (

// ------------  BEGIN QUERY ------------

"SELECT a."Invoice By NEW", COUNT (DISTINCT a."virtual_salesboard_order_count"), SUM(a."virtual_salesboard_totalSold"), SUM(a."virtual_salesboard_totalSold_color")
FROM "VSB_DATA" a
WHERE a."Invoice Date"= ?
GROUP BY a."Invoice By NEW"
" ;

// ------------  END QUERY ------ ------

// ------------  BEGIN FIELD AND ROW SEPARATORS ------------

"|" ; "|*|" ;

// ------------  END FIELD AND ROW SEPARATORS ------------

// ------------  BEGIN ARGUMENTS ------------  
// ------------  These arguments are pulled from the values you entered when running your query.  You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------

$date

// ------------  END ARGUMENTS ------------  

 ) ] ;

// ------------  BEGIN CARRIAGE RETURN SUBSTITUTIONS ------------  

 Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )

// ------------  END CARRIAGE RETURN SUBSTITUTIONS ------------  

)

// Compliments of SeedCode… Cheers!

// ------------  END EXECUTESQL BLOCK ------------

 

 

 

#2

 

there are two Set variable steps at the beginning:

Set Variable[$date1;Value:Invoice::virtual_salesboard_date_start]

Set Variable[$date1;Value:Invoice::virtual_salesboard_date_end]

// ------------  BEGIN EXECUTESQL BLOCK ------------  

Let ( [

ReturnSub = "n" ; // We need to swap out carriage returns in your results with a different character, so specify the character here.  n is the default.
SQLResult = ExecuteSQL (

// ------------  BEGIN QUERY ------------

"SELECT a."Invoice By NEW", COUNT (DISTINCT a."virtual_salesboard_order_count"), SUM(a."virtual_salesboard_totalSold"), SUM(a."virtual_salesboard_totalSold_color")
FROM "VSB_DATA" a
WHERE a."Invoice Date"  BETWEEN ? AND ?
GROUP BY a."Invoice By NEW"
" ;

// ------------  END QUERY ------ ------

// ------------  BEGIN FIELD AND ROW SEPARATORS ------------

"|" ; "|*|" ;

// ------------  END FIELD AND ROW SEPARATORS ------------

// ------------  BEGIN ARGUMENTS ------------  
// ------------  These arguments are pulled from the values you entered when running your query.  You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------

$date1;$date2

// ------------  END ARGUMENTS ------------  

 ) ] ;

// ------------  BEGIN CARRIAGE RETURN SUBSTITUTIONS ------------  

 Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )

// ------------  END CARRIAGE RETURN SUBSTITUTIONS ------------  

)

// Compliments of SeedCode… Cheers!

// ------------  END EXECUTESQL BLOCK ------------

[Ocean West]

Hi I see you are using the SQL Explorer does this work when you run the query in the tool?

[bruceR]

Set Variable[$date1;Value:Invoice::virtual_salesboard_date_start]

Set Variable[$date1;Value:Invoice::virtual_salesboard_date_end]

 

Looks like you are setting $date1 twice.

Shouldn't that be:

 

Set Variable[$date1;Value:Invoice::virtual_salesboard_date_start]

Set Variable[$date2;Value:Invoice::virtual_salesboard_date_end]

[jlamprecht]

In addition to what BruceR said, I would change:

 

 BETWEEN ? AND ? to

 

a."Invoice Date" <= ? AND a."Invoice Date" >= ?

 

Hope this helps