June 3, 2011 at 2:58 pm
Hello, everyone.
First, let me apologize up front; I am both a newbie here, and admittedly self taught (OJT, in a way :-D). Even so, I have searched quite a bit, both on here and else where, and while I have seen some similar questions, nothing has been an actual solution to my issue.
I am using Report Builder 3.0, and I have created a report that, among other things, determines the number of days between two date fields, RequestDate and CompletedDate. However, the business has decided that it would be more useful if this were presented in business days. I currently arrive at the difference using (DateDiff(DateInterval.Minute,Fields!RequestDate.Value,Fields!CompleteDate.Value) / 1440), but I cannot find anything that helps me get from here to there, if you will.:crazy:
Any assistance anyone can provide would be GREATLTY appreciated.
Thanks,
Michael
June 3, 2011 at 3:48 pm
Here is group of date calculations that you might find very useful
https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
A question. You must now use "Business days", does that exclude holidays? If so search on SSC for the use of a calendar table, as an example that you may find useful
June 6, 2011 at 9:14 am
Thanks, Ron!
Those are useful in general, but I am not seeing how I could apply those here. In laymen's terms, I am looking to "simply" not count Saturdays and Sundays. I have seen several examples of the calendar table you have mentioned, and was hoping to avoid that solution as I am reporting statistics, but am not a DBA. Thus, creating tables is something I would rather not tackle, if at all possible.
Again, thanks for any input, though.
June 6, 2011 at 11:44 am
I actually was given the answer by Nehemiah Willis here.
While it does not account for holidays, it is perfect just for eliminating weekends.
you can do this in custom code:
Function business_days(ByVal StartDate As DateTime, ByVal EndDate As DateTime) As Integer
Dim Bus As Integer
Dim temp As DateTime
Bus = 0
temp = StartDate
While (temp <= EndDate)
If (temp.DayOfWeek <> DayOfWeek.Saturday And temp.DayOfWeek <> DayOfWeek.Sunday) Then
Bus += 1
End If
temp = DateAdd("d", 1, temp)
End While
Return Bus
End Function
then reference your code by doing something like:
=Code.business_days(Parameters!fromdate.Value,Parameters!todate.Value)
I hope this helps anyone else who might have had the same question.:-D
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply