• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

T-SQL WorkingDay report

I have a number of hours remaining on a task (Ex: 60 hours).

The working Day are Monday to Friday, we are estimating that we can only work 6 hours by working day.

ex:
2011-03-02 I have 60 hours remaining
so I have 10 working day left to work
The task should be finish on 2011-03-15


How can I calculate the completion date in SQL 2005?



-- SOMETHING LIKE THIS
DECLARE @RemainingHours FLOAT
SET @RemainingHours = 60
SELECT 
	DATEADD(DD, @RemainingHours / 6 * 1.40, GETDATE())

Open in new window

0
Cboudroz
Asked:
Cboudroz
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
SELECT DATEDIFF(day,  GETDATE(), getdate()+3) * 10

this will return the days between two dates and multiply bt 10 hours per day
0
 
derekkrommCommented:
DECLARE
      @RemainingHours FLOAT,
      @EndDate datetime,
      @NewEndDate datetime

SET @RemainingHours = 65

--add 1 week for every 30 hours, calculate remainder
select
      @EndDate = dateadd(ww, convert(int, @remaininghours / 30), getdate()-1),
      @RemainingHours = @RemainingHours - (30 * convert(int, @RemainingHours / 30))

select
      getdate(),
      @EndDate,
      @RemainingHours

--add extra days from remainder (rounding up, 1 extra hour = the following day)
select
      @NewEndDate = dateadd(dd, ceiling(@RemainingHours / 6), @EndDate)

--account for weekends
select
      @NewEndDate =
            case
                  when datepart(dw, @NewEndDate) = 7  -- if it is saturday, move to monday
                        or datepart(dw, @NewEndDate) < datepart(dw, @EndDate) then dateadd(dd, 2, @EndDate) -- if the weekend passed, then add 2 days since sat/sun don't count
                  else @NewEndDate
            end

select
      @NewEndDate


0
 
8080_DiverCommented:
I would modify derekkromm's response by adding 3 more input parameters, one for @HrsPerDay (INT Default = 6), one for @WorkSaturday (Char(1) Default = N) and one for @WorkSunday (Char(1) Default = N).  That would let you also cover cases where the pressure is really on and you are required (or you elect) to work Saturdays, Sundays, or both.  I would then use those to add @HrsPerDay or (@HrsPerDay * 2) in order to handle estimating the completion date based upon the additional information.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
CboudrozAuthor Commented:
derekkromm:

It's not working for some hours.

SET @RemainingHours = 19
...
SET @RemainingHours = 29


will give tomorrow date.
0
 
derekkrommCommented:
sorry, small typo in the case statement at the bottom (changed dateadd(dd, 2, @EndDate) to dateadd(dd, 2, @NewEndDate)

DECLARE 
      @RemainingHours FLOAT, 
      @EndDate datetime,
      @NewEndDate datetime

SET @RemainingHours = 29

--add 1 week for every 30 hours, calculate remainder
select 
      @EndDate = dateadd(ww, convert(int, @remaininghours / 30), getdate()-1),
      @RemainingHours = @RemainingHours - (30 * convert(int, @RemainingHours / 30))

select 
      getdate(),
      @EndDate,
      @RemainingHours

--add extra days from remainder (rounding up, 1 extra hour = the following day)
select 
      @NewEndDate = dateadd(dd, ceiling(@RemainingHours / 6), @EndDate)

--account for weekends
select
      @NewEndDate = 
            case
                  when datepart(dw, @NewEndDate) = 7  -- if it is saturday, move to monday
                        or datepart(dw, @NewEndDate) < datepart(dw, @EndDate) then dateadd(dd, 2, @NewEndDate) -- if the weekend passed, then add 2 days since sat/sun don't count
                  else @NewEndDate
            end

select
      @NewEndDate

Open in new window

0
 
CboudrozAuthor Commented:
derekkromm:

Thanks for your help, I wasn't able to figure out the logic to used.

I created my function and everything work as expected.


ALTER FUNCTION fct_TaskEndDate_GET 
(
	-- Add the parameters for the function here
	@RemainingHours			FLOAT, 
	@WorkingHoursInWeek		INT,
	@StratingDate			SMALLDATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
	-- Declare the return variable here
	DECLARE @EndDate		SMALLDATETIME
	DECLARE @WeekEndDate	SMALLDATETIME

	IF @RemainingHours < 1
	BEGIN
		SET @EndDate = @StratingDate
		RETURN @EndDate

	END 

	
	--add 1 week for every @WorkingHoursInWeek hours
	SET	@WeekEndDate	= dateadd(ww, convert(int, @remaininghours / @WorkingHoursInWeek), @StratingDate-1)
	SET @RemainingHours = @RemainingHours - (30 * convert(int, @RemainingHours / 30))



	--add extra days from remainder (rounding up, 1 extra hour = the following day)
	SET @EndDate = dateadd(dd, ceiling(@RemainingHours / (convert(int, @WorkingHoursInWeek / 5) )), @WeekEndDate)


	--account for weekends
	SET @EndDate = 
					CASE 
						
						WHEN datepart(dw, @EndDate) = 7	-- if it is saturday, move to monday
						or datepart(dw, @EndDate) < datepart(dw, @WeekEndDate) then dateadd(dd, 2, @EndDate) -- if the weekend passed, then add 2 days since sat/sun don't count 
						ELSE @EndDate
					END


	-- Return the result of the function
	RETURN @EndDate

END
GO

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now