I have a query that pulls business days from a startdate and enddate (example from the web)
And I have my query.
I would like to piece the two together but unsure of how to accomplish this.
//business days query
select (DATEDIFF(dd, @StartDate, @EndDate) + 1) -- Number of days between Start and End
-
(
(DATEDIFF(wk, @StartDate, @EndDate)*2) --Weekend Days
+(case when DATENAME(dw, @StartDate) = 'Sunday' then 1 else 0 end) --Taking into consideration the startdate being a sunday
+(case when DATENAME(dw, @EndDate) = 'Saturday' then 1 else 0 end) --Taking into consideration the startdate being a saturday
)
//my query
declare @IssueType varchar(10)
declare @StartDate DateTime
declare @EndDate DateTime
set @IssueType = 'MyIssue'
set @StartDate = '1/1/2012'
set @EndDate = '3/31/2012'
select RefNo, FormReceivedThroughDoor, CallCompleteDate
--,datediff(d, FormReceivedThroughDoor,CallCompleteDate) as DaysBetween
from tbl_complaints
where
(IssueType = @IssueType)
and (FormReceivedThroughDoor between @StartDate and @EndDate)
and (DATEPART(WEEKDAY, FormReceivedThroughDoor)between 2 and 6)
What I'm aiming/trying to add is the function to replace "--,datediff(d, FormReceivedThroughDoor,CallCompleteDate) as DaysBetween" so the query gets the true number of business days.
Thanks in advance
Duncan