February 10, 2015 at 6:33 am
Hello
I have set up a calculated field in a report to show the number of days between a job start and end date. I am using T-SQL:
=DateDiff("D", fields!jobstartdate.value, fields!jobenddate.value)
This works fine, but is of course including weekends in its calculation. Is there a way, using datediff, I can include working days only?
Any insight appreciated - thanks.
February 10, 2015 at 7:06 am
You need to use DATEPART with weekday to determine which day of the week it is. Also, you might consider a calendar table to determine holidays as well.
February 10, 2015 at 7:18 am
This creates a CTE table containing all dates between @ProjectStartDate and @ProjectEndDate datetime variables. The subquery below determines if the day in the CTE are Sunday or Saturday using the DatePart function, assigning 0 for weekend and 1 for weekday. The outer query sums the subquery for the total project days.
DECLARE @ProjectStartDate DATETIME
DECLARE @ProjectEndDate DATETIME
SET @ProjectStartDate = '01/01/2015'
SET @ProjectEndDate = '02/28/2015';
WITH ProjectDays
AS
(
SELECT @ProjectStartDate AS days
UNION ALL
SELECT days + 1
FROM ProjectDays
WHERE days <= @ProjectEndDate
)
SELECT SUM(WorkingDay) as ProjectDays
FROM
(SELECT
CASE
WHEN DATEPART(WEEKDAY,Days) = 1 THEN 0
WHEN DATEPART(WEEKDAY,Days) = 7 THEN 0
ELSE 1
END WorkingDay
FROM ProjectDays) AS WorkingDays
February 10, 2015 at 7:20 am
Many thanks for getting back.
Do you mean set up two calculated fields:
=DatePart("dw", fields!jobstartdate.value) and =DatePart("dw", fields!jobenddate.value) and then subtract the difference?
Sorry don't quite get it.
Thanks (and noted re the calendar table).
February 10, 2015 at 7:20 am
This approach will not work over holidays.
You're going to need a calendar table to make this work in all cases.
February 10, 2015 at 7:33 am
I agree, a calendar table is the way to go. You could code holidays into the case statement relatively easily, but you may as well create the calendar table at that point.
February 10, 2015 at 7:36 am
Thanks all - a table it shall be!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply