Hello! I have a SQL scipt that filters results based on a Date range that is also a column heading. At the moment the SQL has a hard coded value that it filters by, and isnt done dynamically, meaning that only way to change the range would be to manually edit the date range within the script, how can this be changed dynamically?
If anyone could share some of the best practices or principles for this that would be great, thanks!Current where filter
Hello,
You can create input parameter to your Advanced SQL of data type date and use it in your SQL like screenshot.
You can pass any dynamic date to your SQL and it will be used to filter
The table that the filter is running on has a table column called "RequestDateTime", being a DateTime Data type, the endgame goal of this SQL would be to run and report all entries within each month, so each month the SQL will run and pull all results based on month 1, 2, 3 etc on a per month basis. Would this still function the same way? example results in the table.
Yes, this way will work for your requirements, I am just showing example by screenshot I shared.
If this report runs monthly I assume you are using timer that running this SQL script so you can have local variable into you action of type date time to calculate each month start date and pass it to Advanced SQL.
You can use NewDate OutSystems function to create your dynamic date. You can see details and example here:
https://success.outsystems.com/documentation/11/reference/outsystems_language/logic/built_in_functions/date_and_time/#NewDate
Yes that is the plan, but i have yet to work with timers and am reviewing the material. I have done as you suggested but how would i know what value is to be put into the DateFilter query parameter on the advanced SQL prompt?
Here you need to put date that you want to filter based on it. This can be implemented by creating local variable into your action then use assignment widget to set value of this local variable for example by NewDate function and last step to pass this local variable to DateFilter parameter. Please check screenshots
Understood, i think i followed all your instructions but when i use a test value for the DateFilter parameter i get hit with an error on my data formatting:
Please ignore, i have since seen that you need a # around the test values, it now works as intended, thank you for all your help! Now i just need to work out how to get it to work on the frontend!
Hi @Morgan Kemp,
If the date value is based on some logics in the system store the value in a local variable and pass them as input parameter or If the Date should be configured please make us of site properties so that it can be updated via service centre and can be passed as input parameter to SQL.
Thanks,Vignesh Prakash.