You can translate the question and the replies:

Setting a date as a parameter to speed up results

I have a query that has a where statement that look at between two different date ranges. These date ranges move depending on todays date. In MSSQL I would define 4 variables and then use them in the where statement. The current solution I have looked something like this: ``` (maintable.DDate BETWEEN (ADDDAY(ADDWEEK((CASE WHEN -- If there is less than 1 week passed from the start of the month (GETDAYSBETWEEN(FIRSTDAYOFMONTH(CURRENT_DATE()),CURRENT_DATE())) < 7 THEN -- get the last day of the prior month LASTDAYOFMONTH(ADDDAY(CURRENT_DATE(),-10)) ELSE -- else work out how many weeks hhave passed and add it to the start of the month to get the final week in the report view ADDDAY(ADDWEEK(FIRSTDAYOFMONTH(CURRENT_DATE()),(GETDAYSBETWEEN(FIRSTDAYOFMONTH(CURRENT_DATE()),CURRENT_DATE())/7)),-1) END),-10),1)) AND (CASE WHEN -- If there is less than 1 week passed from the start of the month (GETDAYSBETWEEN(FIRSTDAYOFMONTH(CURRENT_DATE()),CURRENT_DATE())) < 7 --<--WHERE THE CURRENT DATE IS STORED THEN -- get the last day of the prior month LASTDAYOFMONTH(ADDDAY(CURRENT_DATE(),-10)) ELSE -- else work out how many weeks hhave passed and add it to the start of the month to get the final week in the report view ADDDAY(ADDWEEK(FIRSTDAYOFMONTH(CURRENT_DATE()),(GETDAYSBETWEEN(FIRSTDAYOFMONTH(CURRENT_DATE()),CURRENT_DATE())/7)),-1) END) OR maintable.CPD_DT BETWEEN --This finds the week to get to for the prior period (ADDYEAR(ADDDAY(ADDWEEK((CASE WHEN -- If there is less than 1 week passed from the start of the month (GETDAYSBETWEEN(FIRSTDAYOFMONTH(CURRENT_DATE()),CURRENT_DATE())) < 7 THEN -- get the last day of the prior month LASTDAYOFMONTH(ADDDAY(CURRENT_DATE(),-10)) ELSE -- else work out how many weeks hhave passed and add it to the start of the month to get the final week in the report view ADDDAY(ADDWEEK(FIRSTDAYOFMONTH(CURRENT_DATE()),(GETDAYSBETWEEN(FIRSTDAYOFMONTH(CURRENT_DATE()),CURRENT_DATE())/7)),-1) END),-10),1),-1)) AND --This finds the week to get to for the prior period (ADDYEAR(CASE WHEN -- If there is less than 1 week passed from the start of the month (GETDAYSBETWEEN(FIRSTDAYOFMONTH(CURRENT_DATE()),CURRENT_DATE())) < 7 THEN -- get the last day of the prior month LASTDAYOFMONTH(ADDDAY(CURRENT_DATE(),-10)) ELSE -- else work out how many weeks hhave passed and add it to the start of the month to get the final week in the report view ADDDAY(ADDWEEK(FIRSTDAYOFMONTH(CURRENT_DATE()),(GETDAYSBETWEEN(FIRSTDAYOFMONTH(CURRENT_DATE()),CURRENT_DATE())/7)),-1) END,-1)) ) ``` This seems grossly inefficient as I believe it does this calculation for every row it looks at?? Is there a way that I can define date1, date2, date3 and date4 using the above and then pass it to speed it up?
user
21-02-2021 18:06:13 -0500

2 Answers

Hi, I would use the [View Parameters](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/creating_derived_views/creating_selection_views/creating_selection_views#parameters-of-derived-views) in the Virtual DataPort in order to filter the data based on the values obtained during runtime. For instance, I would use the View parameters option under the model tab of a derived view for adding parameters. Further, I would set this parameter in the Where condition tab as below example format: ``` [fieldname] <operator> [parameter] ``` You can refer to the similar [Community Question](https://community.denodo.com/answers/question/details?questionId=9064u000000CelUAAS&title=Question+regarding+how+user+can+enter+value+to+see+the+specific+result) for more information Hope this helps !
Denodo Team
22-02-2021 07:24:04 -0500
It does not look like you could pass a number of parameters as ive set out in my query above. Im not sure this will work as intended.
user
 Edited on: 22-02-2021 07:33:43 -0500
You must sign in to add an answer. If you do not have an account, you can register here