Smart Reporting 1902 release has now capability to leverage entire set of RDBMS functions and it gives ability to achieve complex calculations and use-cases which were not possible in previous releases. More details can be found here
Speaking of Use-Case, One of the popular requirement was to calculate difference between two dates excluding weekends.
Well the wait is over now. This blog will walk you through the steps to calculate Incident Age Excluding weekends.
System Prerequisite :
AR Server version - 1902 or higher
Smart Reporting version - 1902 or higher
We are creating a complex formula from nesting multiple database functions and few arithmetic operation to get the results.
So I'll break it down the Native Database formula and then will see how we can define ARJDBC compliant SQL in Smart Reporting.
Here is the MSSQL formula (Native SQL) :
select cast(DATEDIFF(DD,'2019-01-01 12:00:00','2019-01-10 12:00:00')/7 as INT)*5 + cast(SUBSTRING('1234555123444512333451222345111234500123450123455',(DATEPART(dw,'2019-01-01 12:00:00')-1)*7 + (DATEDIFF(DD,'2019-01-01 12:00:00','2019-01-10 12:00:00')%7)+1,1) as INT)-1
This entire formula can be divided into 2 parts.
1 - Count Weeks
Calculate the number of weeks between dates, This can be done by (Number of Days/7) and next multiply it by 2, This gives you number of Working days in whole week.
Eg; 19/97 = 2.71
Since we need a whole number for this we can truncate decimals using Cast functions which gives me 2 whole weeks, We will deal with decimal points in next part. Now i can simply multiply this value by 5 which result number of working days in whole week. which is 10 in this case.
This calculates first piece of query -
select cast(DATEDIFF(DD,'2019-01-01 12:00:00','2019-01-10 12:00:00')/7 as INT)
2 - Calculate the leftover days
Now we need to calculate the leftover days which are not considered under whole week and then add it in the result of first part which gives you exact number of Working Days.
Remember we had truncated decimal places in first part, we need to get that part and convert it into leftover days, we have divided the value and truncated decimals now we need perform modulus arithmetic operation which do the division and returns remainder value added some further logic to get correct days.
Second part will calculate the leftovers:
cast(SUBSTRING('1234555123444512333451222345111234500123450123455',(DATEPART(dw,'2019-01-01 12:00:00')-1)*7 + (DATEDIFF(DD,'2019-01-01 12:00:00','2019-01-10 12:00:00')%7)+1,1) as INT)-1
At the end of formula we have subtracted 1 day to exclude current day, If you want to consider current day as well then remove (-1) arithmetic operation at the end of formula.
So consider the Dates passed in above query
Start Date - 01/01/2019
End Date - 10/01/2019
Actual Date Difference - 9
Excluding Weekends - 7
Now lets see how we can define it in Smart Reporting
Essentially you need 4 database functions for this calculation
Make sure this functions are defined in Remedy Form and the create Freehand SQL calculated field to define these in similar order as Native Database formula with special Keyword DBFN.
Below is an example to calculate Difference between Submit Date & Last Resolved Date for Incidents.
dbfn('cast',dbfn('datediff','DD',`Submit Date`,`Last Resolved Date`)/7,'as int')*5 +
dbfn('cast',dbfn('substring','1234555123444512333451222345111234500123450123455',(dbfn('datepart','dw',`Last Resolved Date`)-1)*7 + (dbfn('datediff','DD',`Submit Date`,`Last Resolved Date`)%7)+1,1),'as int')-1
Here is the Output of Sample Report