3 Replies Latest reply on Nov 2, 2016 4:13 PM by Trevor Toll

    Count of Days Between 2 Dates in Same Table (have a DateTable as well)

    Steve Moberg

      Long time lurker, first time poster, so bear with me if I've committed any heinous crimes.


      I've received business requirements to determine the number of 'Business Days' between two dates (CreateDate and StatusChangeDate) that reside in the same table as each other -- table called WorkObject

      The good news is that my database does have a DateTable that has all dates listed in it as well as a Business Day Indicator (1 if Business Day - 0 if not a Business Day). -- table called Date


      In SQL I just did a count of rows between CreateDate and STatusChangeDate that didn't have the 1 flag:

           (select count(*) from dbo.vwDimDate d where d.IsBusinessDay=1 and d.Date between wo.CreateDateTime and wo.StatusChangeDateTime)


      I've tried CustomSQL to do this same thing, but performance is severely impacted when the query runs.


      Anybody have any ideas on how to find the BusinessDays BETWEEN these two dates?


      **One issue that I believe I may have is that in order to join to Date, I have a DateKey that is set to a DateTime but is set to 00:00:00.000 -- while my other two dates are set to actual date times (10/21/2016 3:33:52PM).


      Thanks all in advance !!