Posted: 4 Sep 2020 7:28 EDT Last activity: 29 Oct 2020 17:04 EDT
Date function in Materialized Views for SQL Server
We plan to leverage the materialized view for the end user portal that joins data from two tables.
Is there a way to use Date Time functions in the query of the materialized view to pull past week's data?
We are using SQL Server as DB.
There are certain restrictions around using GetDate() function in SQL Server, is there any other way to implement this from Pega perspective?
I'm not sure I understand your question. Are you asking about the query that populates the materialized view, or the query to retrieve data from the materialized view once it is populated?
The query to populate the view is external to Pega. It is under the control of your DB administrator.
If you are talking about pulling the past week's data from the materialized view once it is populated, you may be able to set the query limits using some of the OOTB date/time function aliases or combinations thereof. If none of them give you exactly what you need you can implement your own custom function aliases (note that you can call other function aliases from your unction alias, and thereby leverage existing FAs).
In order to give you more specific advice, I would need to know what exactly you mean by "past week's data" (e.g., "one week prior to the current date and time", or "one week ending at EOB yesterday", or "last week's data") and what restrictions you are encountering using the GetDate() function.