We have a requirement to produce a stacked column chart that displays a count of completed requests split by how many working days they missed the SLA date by (e.g. completed within SLA, competed 1-2 days after SLA, completed 3-5 days after SLA, completed >5 days after SLA). Our table stores both the SLA date and the completed date so we have the data but there doesn't appear to be an easy way to calculate the number of working days between the two (so ignoring weekends and bank holidays).
We had initial created a view and used the CASE operator to define a column that categorised each request as Within SLA, 1-2 days after SLA, etc., but this does not take account of non-working days so is not acceptable to the business. A search has discovered several database solutions that rely on creating and populating a calendar table that includes all the days of the year and whether they are a working day or not, but that solution relies on that table being maintained and updated with all the bank holidays each year (which we might be able to do with an Appian process and the calendar used in Appian to identify working days).
The other option is to retrieve the individual rows into Appian and run an expression on them before aggregating the data for the report, but we usually try to move as much of the data manipulation and aggregation to the database where possible.
Has anyone had a similar requirement and if so how did you resolve it?
Does anyone have any other ideas or suggestions?
Or is the simplest solution to calculate the difference in working days at the time the request is completed and to save that into the database table when the data is saved (although this is already live with 35,000+ rows of data that would need that field populated)?
Discussion posts and replies are publicly visible
Have you thought of a materialized view? Call a Stored Proc and pass what you deem to be working days (which you can derive from whatever Appian calendar you need to align to) and load all of the data into a table based upon the parameters you send. You'd need to truncate and load daily (assuming an "as of close of play yesterday" is deemed acceptable).
Thanks Stewart, no we hadn't thought of that so thanks for the suggestion.
I think our current thinking is the last of my options, calculating the working days at the time the request is completed and storing it in a new field in the table (and having a temporary process to populate that field for the existing requests)