[SOLVED]Count business days Difference excluding Saturday's and public holidays

[srinisid]

Hello everyone.
Can someone help me out here?

I have to calculate the days taken to resolve an issue where I have a "created" date column and "resolved" date column which holds the date a particular issue was created and when it was resolved. I also have a time dimension which lists out saturdays and public holidays.
I need to create a data item that will calculate the days taken to close an issue excluding saturdays and public holidays.

for eg if an issue was created on 11 june 2015 and was closed on 16 june 2015 then the new colomn should have 4 instead of 5.
if 12 june 2015 is a public holiday then the value should be 3 instead of 5. Can you help me with the calculation?

[MFGF]

Hello everyone.
Can someone help me out here?

I have to calculate the days taken to resolve an issue where I have a "created" date column and "resolved" date column which holds the date a particular issue was created and when it was resolved. I also have a time dimension which lists out saturdays and public holidays.
I need to create a data item that will calculate the days taken to close an issue excluding saturdays and public holidays.

for eg if an issue was created on 11 june 2015 and was closed on 16 june 2015 then the new colomn should have 4 instead of 5.
if 12 june 2015 is a public holiday then the value should be 3 instead of 5. Can you help me with the calculation?

Hi,

This is generally something you'd need to do using a specific calendar table in your database. There's no easy way otherwise of being able to identify public holidays.

Take a look at the post below for ideas:

http://www.cognoise.com/index.php/topic,20084.msg60694.html#msg60694

Cheers!

MF.

[srinisid]

Woah thanks for the prompt repy!!! :)

The problem is thats the best my DBA guys could do and stuck me with this problem. I do have a colomn listing saturdays and public holidays. I just have to exclude them from my days_between calculation.
I tried to scrounge up a calculation from this portal itself that takes care of removing the saturday but no solution for holidays

floor((_days_between([RESOLVED_DATE] ,[CREATION_DATE]))-((_days_between([RESOLVED_DATE] ,[CREATION_DATE]))/7))

[Lynn]

....

The problem is thats the best my DBA guys could do and stuck me with this problem.

....

Why are soooo many DBAs utterly lazy and totally worthless?  >:(

You could compute the Julian date to achieve the same thing and wrap it in a case statement to get the Julian date one day back for Saturdays, two days back for Sundays, three days back for Holiday Mondays, etc. The difficulty here arises for contiguous holidays since it isn't easy to interrogate how far back to go. You could code for known situations, but it is difficult to do dynamically with a non-procedural SQL statement.

If you can be clear about defining the rules for this attribute then take it to your manager and suggest the database would be a better place to compute and store this information for reporting purposes.

[cognos810]

Hello srinisid,
When you say "I also have a time dimension which lists out saturdays and public holidays.", how is this information listed. Do you have a flag column in your time dimension for example, with a value of 1 or a 0 of it is a Saturday or a Holiday ?
If that is the case then you can count the occurrences of these days in between two days and subtract it from the total number of days returned by the _days_between function.

Did I get your question right?

-Cognos810

[srinisid]

Why are soooo many DBAs utterly lazy and totally worthless?  >:(

You could compute the Julian date to achieve the same thing and wrap it in a case statement to get the Julian date one day back for Saturdays, two days back for Sundays, three days back for Holiday Mondays, etc.

Can you please elaborate on this. Not sure I follow you. Do you mean that I need to deduct 1 day for Saturday, 2 days for Sunday?
How do I find out if a particular range contains that Saturday or a Sunday or a Holiday?

Hello srinisid,
When you say "I also have a time dimension which lists out saturdays and public holidays.", how is this information listed. Do you have a flag column in your time dimension for example, with a value of 1 or a 0 of it is a Saturday or a Holiday ?
If that is the case then you can count the occurrences of these days in between two days and subtract it from the total number of days returned by the _days_between function.

Did I get your question right?

-Cognos810

Yes You got that right. I have a Flag column that  has value 0 for working days and 1 for Saturdays and for holidays.
But I am struggling to identify if a date range contains a Saturday or a holiday. Your solution seems something that I can accomplish. Can you please elaborate?

Thank you so much for your replies!!

[Lynn]

Your dilemma stems from the fact that you've got one record for the issue that has two dates on it, but you need to interrogate all the calendar records between those two dates to count up working days that exclude holidays.

Since we are in the Framework Manager forum it seems reasonable to assume that you want to model a query item for working days that the user can drag into a report. That won't be easy to do because this implies that a single SQL statement is going to be generated based on the join of your fact table to your calendar dimension (presumably aliased and joined twice, once for created and once for resolved), but all the records between those two calendar records are necessary to get the answer.

This is why the solution that the muppet pointed you to is just exactly what you need. It is elegant and simple but requires the DBA to brush the crumbs off his shirtfront and actually do some work by extending the calendar table to support this requirement. It is a trivial bit of code to develop when one has procedural tools to work with.

Unless I'm mistaken, the solution that Cognos810 is suggesting will be perfectly viable, but would need to be done on each and every report in Report Studio. I don't really see how you can model that expression in FM because you need to look at multiple records in order to do that count, not just the records that your facts are joined to.

What I suggested was a hard coded approach to Mr Lee's post about using a running count of days that doesn't increment for non-working days. If you google julian day you will learn it is just a sequential integer number for each day from the beginning of time. Many databases have a function to compute it which means a query item in your model could produce it, but it really isn't going to work because as I thought it might because you can't look at one record and know how to reflect the sequential count based on all the records that may or may not be weekend/holiday days in the past.

Good luck, but the first answer you got from the muppet really was the right one.

[srinisid]

Hmm I thought so.. I was kinda hoping that there would be a better solution but it seems kinda impossible to me too.

@lynn You are just awesome! All your assumptions are bang on and accurate. Your comment about "DBA to brush the crumbs off his shirtfront and actually do some work" quite literally made my day.

I think I will go with the incremental workday approach as i think that will be the most efficient and ideal thing to do.

Just a heads up to people who also have the same problem. Either implement a calendar as suggested by the muppet or go with the incremental approach.

If the clients or DBA dosent agree to the approach then do the same thing I did.

1)Take a screenshot of Lynns answer and print it
2) Give it to the DBA guys and walk off like a boss.. ;D


Thank you all!

[srinisid]

Just an update to the above problem. I managed to solve the problem above.
First you need to setup a calendar table that has all the dates for the foreseeable future.
Then you need to create a new flag column that has 0 for non working days and 1 for working days. This can easily be achieved by creating a new column with 0 as default value and then just update the value with one of the dates you want.
Next step would be create a counter value that will count the working days. This new column will count only when the flag value is 0 and will not count if the value is 1. To see how this can be done please see herehttp://stackoverflow.com/questions/30845272/update-a-column-sequentially-based-on-condition
Then calculating working days is very simple. TO calculate working days simple subtract the corresponding values from the counter column.
To see how to do that you can check here http://stackoverflow.com/questions/30890680/calculate-difference-between-two-rows-from-two-different-table

And Voila! you have working days!.
Thanks to everyone here for all the help!