Question
ATOS
IN
Last activity: 2 Jun 2020 21:55 EDT
difference between dates (in minutes) in Report Definition based on business calendar
We have a requirement where we need to display the age of a case (Difference between CreateDateTime and CurentDateTime based on business calendar) in mins in a Report. There are functions available which can do this. since the report definition only can invoke Function alias rules and there are no OOTB function alias rules available to get this done.
I have gone through some of the posts in PDN. But couldn't find a solution. one of the link which describes same issue that we have is https://collaborate.pega.com/discussion/business-days-difference-report-definition-rule, but there is no solution in this link too.
Let me explain with an example, Let's say we have business hours mentioned in my calendar instance as 09:00AM to 05:00PM on all weekdays.
Lets assume a case has been created on 7th May 3PM and the case is with status Open and we are running the report at 11AM on 8th May. then the OOTB function alias (pxDifferenceInMinutes) will give the difference as 20 hours. But what we're actually looking for is, 3PM to 5PM (2 hours) + 9AM to 11AM (2 hours) = 4 hours. Is there a way to do it?
Blue Rose Technologies
DE
Hi,
In below link similar issue is discussed but for date difference based on business calendar. Whoever asked question updated that he went with a different solution using declare expression. Please go through and see if that approach helps.
Pegasystems Inc.
US
The business days requirement makes this a difficult issue. There are no OOTB functions or APIs in SQL or Java that will do this for you. The reason for this is that business calendars vary from locale to locale due to holidays.
There are services you can subscribe to that will tell you whether a given day is a business day for a given locale (and type of business if that's relevant). These are used by large companies in the financial services industry, for instance, since they need to do things like calculate the closing date of a transaction. These services cost money, so you probably don't want to go there.
In a previous life I had to deal with this issue for a startup that could not afford to subscribe to a service for this. Our solution was to keep a database table that contained the dates of holidays. We had to manually update the list of holidays to make sure it covered our business needs. It wasn't that hard to do. We knew what the holiday dates were for as many years as we wanted to enter. We just had to remember to update it to stay ahead and to account for any special occurrences.
This allowed us to write Java code and SQL queries that gave us the correct number of business days (excluding both weekends and holidays) between any 2 dates. I think you will need to do something similar.