Jump to content

Help with ExecuteSQL Script Step, Date Range


This topic is 2580 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

This topic is 2580 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.