Calculating Task Past Due Dates without weekends

Question

Post reply

Calculating Task Past Due Dates without weekends

kenny-575801

Grasshopper

Points: 12
More actions
December 8, 2007 at 6:29 pm

#62890

I have a Task table with startdate,noofdays and enddate as 3 fields and I want to take the start date add the noofdays and place the correct end WORKING DAY date in the enddate field.
This way, I could have a query to determine whether the individual tasked with completing the task has missed a deadline for completing the task:
Here is the code for checking whether a task deadline is missed:
SELECT CONVERT(Char,TaskStartDate,101),
CONVERT(Char,taskEndDate,101), noOfDays
FROM tblTasks
WHERE CONVERT(Char,DateAdd(hour,-24,getdate()),101) > CONVERT(Char,taskEndDate,101)
Then here is the code for calculating the difference between taskStartDate and TaskEndDate without the weekends.
SELECT
(DATEDIFF(dd, taskStartDate, taskEndDate) + 1)
-(DATEDIFF(wk, taskStartDate, taskEndDate) * 2)
-(CASE WHEN DATENAME(dw, taskStartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, taskEndDate) = 'Saturday' THEN 1 ELSE 0 END) as noOfDays
from tblTask
The problem is that I want to add the difference between startDate and EndDate (noOfDays) into endDate so that the code I have above can accurately determine when a task is past due date.
Can some SQL guru assist with this, please?
Thanks a bunch.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004694 More actions · Answer 1

I wish I could take the credit for the following bit of SQL prestidigitation, but I can't... thank "Sergiy" 😉

CREATE FUNCTION dbo.fnAddWeekdays (@Start DATETIME, @Days INT)

RETURNS DATETIME

AS

BEGIN

DECLARE @End DATETIME

SELECT @End = @Start + @Days/5*7 + @Days%5

+ CASE

WHEN (@@DATEFIRST + (DATEPART(dw, @Start) - 2)) % 7 + 1 + @Days%5 > 5

THEN 8 - ((@@DATEFIRST + (DATEPART(dw, @Start) - 2)) % 7 + 1 + @Days%5)

ELSE 0

END

RETURN @End

END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kenny-575801 Grasshopper Points: 12 More actions · Answer 2

Wow, this is the fastest response I have ever gotten.

Thanks Jeff for your assistance.

Just one additional question, please.

I suppose this function determines the difference between start and end dates and adds that difference back to end date, thereby given true end dates without weekends, no?

If yes, can you please show how to call it with this code:

SELECT CONVERT(Char,TaskStartDate,101),

CONVERT(Char,taskEndDate,101), noOfDays

FROM tblTasks

WHERE CONVERT(Char,DateAdd(hour,-24,getdate()),101) > CONVERT(Char,taskEndDate,101)

Thanks again

Jeff Moden SSC Guru Points: 1004694 More actions · Answer 3

kenny (12/8/2007)
Wow, this is the fastest response I have ever gotten.
Thanks Jeff for your assistance.

Just lucky... I happened to be "in the neigborhood" and just happened to have an answer 😛

I suppose this function determines the difference between start and end dates and adds that difference back to end date, thereby given true end dates without weekends, no?

No, it does not... It takes the StartDate and the "Number of Weekdays" you want to add to the StartDate and returns the new/correct EndDate. The calculation skips WeekEnd days.

If yes, can you please show how to call it with this code:
SELECT CONVERT(Char,TaskStartDate,101),
CONVERT(Char,taskEndDate,101), noOfDays
FROM tblTasks
WHERE CONVERT(Char,DateAdd(hour,-24,getdate()),101) > CONVERT(Char,taskEndDate,101)

I'm a bit confused with that request... your original request stated the following... I've highlighted what I think is the really important part...

I have a Task table with startdate,noofdays and enddate as 3 fields and I want to take the start date add the noofdays and place the correct end WORKING DAY date in the enddate field.

If that is trully the task you want to accomplish, then the following code will update the TaskEndDate according to the current value of the TaskStartDate and the number of NoOfDays assigned for the task...

UPDATE tblTask

SET TaskEndDate = dbo.fnAddWeekdays (TaskStartDate DATETIME, NoOfDays INT)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)