Question
Business days calculation using Function alias in Report definition
Hi,
We have a requirement to display business days in a report definition.
we need to calculate the age as difference in business days. If the target date was Friday 4th Oct, if we run the function today we need to get the age as 10 as we need to exclude the weekends which are not business days.
Function BusinessCalendar is not supporting in RD. I had gone through PDN and Mesh, some posts are like creating DE for a property using BusinessCalander function. So that we can use that property. But in our requirement without property, we need to apply businessdays calculation to the required columns. I tried to create Function Alias rule, but some where I missed some logic. It’s not working for some scenarios.
Below is the logic of function :{2} -Is the calendar days(difference of days between Target data and Today)
CASE
WHEN {2}>=6 THEN {2}-(({2}/7)*2)
WHEN {2}>=0 AND {2}<6 THEN {2}
WHEN {2}>=0 AND {2}<6 AND DATENAME(weekday,{1}) = 'Sunday' OR DATENAME(weekday,GETDATE()) = 'Saturday' THEN {2}-1
WHEN {2}<0 THEN {2}+(({2}/7)*2)
END
Any one have function alias logic to get business days, please help me to solve the issue.
Thank you,
***Moderator Edit-Vidyaranjan: Updated Platform Capability***
I have created my own function alias by passing target date, and calendardays(difference of targetdate and currentdate) below is the code. It is not working for some scenarios, could you please help me to achieve this.
CASE
WHEN {2}>=6 THEN {2}-1-(({2}/7)*2)
WHEN {2}>=0 AND {2}<6 THEN {2}
WHEN {2}>=0 AND {2}<6 AND DATENAME(weekday,{1}) = 'Sunday' OR DATENAME(weekday,GETDATE()) = 'Saturday' THEN {2}-2
WHEN {2}<0 THEN {2}+1+(({2}/7)*2)
END
Assuming you are calculating just non-weekend days and not accounting for holidays, there are many solutions available online (Google "sql business days between") that can be adapted to a function alias. For example, here is one from stackoverflow:
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)You may need to tweak this depending on whether you want to count both start and end dates in the total (e.g., is 12/01/2020 to 12/01/2020 zero working days or one working day).
If you need to account for holidays, the problem becomes more difficult. There is no built in function to tell you if a given date is a holiday, as holidays are different from country to country, business to business, and the dates change from year to year. The way this is normally done is keep a table of holiday dates that you can check from within your function alias. This table has to be maintained manually.
Hello Team,
We have tried using below code in Function Alias and referred in Report Definition. We are getting the attached error while running the Report definition.
{1} = from_date
{2} = to_date
select count(d::date) as d from generate_series({1}, {2}, '1 day'::interval) d where extract('dow' from d) not in (0, 6)
Thanks,
Niti
Are you using the code from the stackoverflow example as is? If so, you need to use just the case part of the sample code.
The best way to do this is to take advantage of the fact that FAs can call other FAs. Break the problem down to components, e.g., first write a simple IsWeekendDay(date) FA which has a CASE statement that returns true when the date is a Sat or Sun. Then use that to check the dates you need to check in a totalBusinessDays(from_date, to_date) FA.
I am not sure what you mean by "business calendar function". You can call a function alias from a report definition or from another function alias. You can call any SQL functions (e.g., DATENAME() in the above example) in the source for a function alias.