June 3, 2009 at 12:05 am
Hi
iam working on a task management system. The database is sql server 2005. In my database iam keeping the task details such as TaskName,EstimatedStartTime,EstimatedFinishTime etc. i want to calculate the difference of Estimated start and finish time to get the estimated time required to finish the task. The problem is that there may be holidays(saturday,sunday) in between this dates. And there will be only 8 Hrs for a working day. So how will i calculate the estimated time required to finish the tasks. No matter the format of the estimated time required, it may in minutes,hours or in days. Anybody please do a help. Thanks in advance
June 3, 2009 at 12:11 am
Do you have any information stored in your db for holidays?
"Don't limit your challenges, challenge your limits"
June 3, 2009 at 12:53 am
Thanks dear friend
yes iam keeping the information about the holidays,working hours etc
June 3, 2009 at 1:11 am
I think you can use this as a starting stone.
CREATE FUNCTION dbo.fn_MaxInt(@Num1 INT, @Num2 INT)
RETURNS INT
AS
--Helper function
--Return the larger of two integers
BEGIN
RETURN CASE WHEN @Num1 > @Num2 THEN @Num1 ELSE @Num2 END
END
CREATE FUNCTION dbo.fn_WeekdayDiff(@StartDate DATETIME, @EndDate DATETIME)
RETURNS INT
AS
--Calculdate weekdays between two dates
BEGIN
--if @StartDate is AFTER @EndDate, swap them
IF @StartDate > @EndDate
BEGIN
DECLARE @TempDate DATETIME
SET @TempDate = @StartDate
SET @StartDate = @EndDate
SET @EndDate = @TempDate
END
RETURN
--number of weeks x 5 weekdays/week
(DATEDIFF(ww, @StartDate, @EndDate) * 5)
--add weekdays left in current week
+ CASE DATEPART(dw, @StartDate + @@DATEFIRST) WHEN 1 THEN 5 ELSE (7 - DATEPART(dw, @StartDate + @@DATEFIRST)) END
--subtract weekdays after @EndDate
- dbo.fn_MaxInt(6 - DATEPART(dw, @EndDate + @@DATEFIRST), 0)
END
Tanx 😀
June 3, 2009 at 3:01 am
Hi friend here i written the the structure of the tables
Structure of main task table : tbl_Tasks
ID,
TaskName(varchar),
TaskDescription(varchar),
TaskPriority(int),
EstimatedStartDateTime(datetime),
EstimatedFinishDateTime(datetime)
Structure of workingdays table : tbl_WorkingDaysAndHours
ID,
MondayWorkDay(bool),
MondayWorkStartTime(datetime),
MondayWorkDurationInMinutes(int),
TuesdayWorkDay(bool),
TuesdayWorkStartTime(datetime),
TuesdayWorkDurationInMinutes(int),
WednesdayWorkDay(bool),
WednesdayWorkStartTime(datetime),
WednesdayWorkDurationInMinutes(int),
ThursdayWorkDay(bool),
ThursdayWorkStartTime(datetime),
ThursdayWorkDurationInMinutes(int),
FridayWorkDay(bool),
FridayWorkStartTime(datetime),
FridayWorkDurationInMinutes(int),
SaturdayWorkDay(bool),
SaturdaydayWorkStartTime(datetime),
SaturdaydayWorkDurationInMinutes(int),
SundayWorkDay(bool),
SundayWorkStartTime(datetime),
SundayWorkDurationInMinutes(int),
June 3, 2009 at 4:26 am
I have some confusion here...
Structure of workingdays table : tbl_WorkingDaysAndHours
ID,
MondayWorkDay(bool),
MondayWorkStartTime(datetime),
MondayWorkDurationInMinutes(int),
TuesdayWorkDay(bool),
TuesdayWorkStartTime(datetime),
TuesdayWorkDurationInMinutes(int),
WednesdayWorkDay(bool),
WednesdayWorkStartTime(datetime),
WednesdayWorkDurationInMinutes(int),
ThursdayWorkDay(bool),
ThursdayWorkStartTime(datetime),
ThursdayWorkDurationInMinutes(int),
FridayWorkDay(bool),
FridayWorkStartTime(datetime),
FridayWorkDurationInMinutes(int),
SaturdayWorkDay(bool),
SaturdaydayWorkStartTime(datetime),
SaturdaydayWorkDurationInMinutes(int),
SundayWorkDay(bool),
SundayWorkStartTime(datetime),
SundayWorkDurationInMinutes(int),
according to above structure, you can get detail about the week day and week off in general.
eg.
MondayWorkDay = true
MondayWorkStartTime = 08am
MondayWorkDurationInMinutes = 480 (8hrs)
....
....
FridayWorkDay = true
FridayWorkStartTime = 08am
FridayWorkDurationInMinutes = 240 (4hrs)
....
SundayWorkDay = false (off day)
SundayWorkStartTime = null
SundayWorkDurationInMinutes = 0
BUT, how can i get the information that on monday (or any other week day, at any specific date) there is a holiday?
"Don't limit your challenges, challenge your limits"
June 3, 2009 at 4:57 am
Dear friend i want to exclude only those days marked as of days. Suppose if a task should start on 01/06/2009 and should end on 15/06/2009. In between these two dates i need to exclude only the off days(ex. sunday and saturday) and the hours after the working time. Means do not require to calculate all the hours in that day.
Thanks Kruti
Rajesh VR
June 3, 2009 at 6:37 am
hope this helps.
i have created weekoff's table according to your given structure and some assumption. Please find test table and a function to find total working hrs...
Table and data:
create table tbl_WorkingDaysAndHours(
ID int identity (1,1),
MondayWorkDay tinyint,
MondayWorkStartTime datetime,
MondayWorkDurationInMinutes int,
TuesdayWorkDay tinyint,
TuesdayWorkStartTime datetime,
TuesdayWorkDurationInMinutes int,
WednesdayWorkDay tinyint,
WednesdayWorkStartTime datetime,
WednesdayWorkDurationInMinutes int,
ThursdayWorkDay tinyint,
ThursdayWorkStartTime datetime,
ThursdayWorkDurationInMinutes int,
FridayWorkDay tinyint,
FridayWorkStartTime datetime,
FridayWorkDurationInMinutes int,
SaturdayWorkDay tinyint,
SaturdaydayWorkStartTime datetime,
SaturdaydayWorkDurationInMinutes int,
SundayWorkDay tinyint,
SundayWorkStartTime datetime,
SundayWorkDurationInMinutes int)
insert into tbl_WorkingDaysAndHours
select 1, '06/01/2009 08:00:00', 480,
1, '06/02/2009 08:00:00', 480,
1, '06/03/2009 08:00:00', 480,
1, '06/04/2009 08:00:00', 480,
1, '06/05/2009 08:00:00', 480,
0, '06/06/2009 08:00:00', 0,
0, '06/07/2009 08:00:00', 0
union all
select 1, '06/08/2009 08:00:00', 480,
1, '06/09/2009 08:00:00', 480,
1, '06/10/2009 08:00:00', 480,
1, '06/11/2009 08:00:00', 480,
1, '06/12/2009 08:00:00', 480,
0, '06/13/2009 08:00:00', 0,
0, '06/14/2009 08:00:00', 0
Function to find working hours:
CREATE FUNCTION dbo.fn_TotWorkingHrs(@StartDate DATETIME, @EndDate DATETIME)
RETURNS INT
AS
BEGIN
declare @curDate datetime
declare @wHr int
declare @mon tinyint, @tue tinyint, @wed tinyint, @thu tinyint, @fri tinyint, @sat tinyint, @sun tinyint
declare @monhr int, @tuehr int, @wedhr int, @thuhr int, @frihr int, @sathr int, @sunhr int
set @curDate = @startDate
set @wHr = 0
while @curDate <= @endDate begin
IF UPPER(DATENAME(WEEKDAY,@curDate))= 'MONDAY' BEGIN
select @mon=MondayWorkDay, @monhr=MondayWorkDurationInMinutes
from tbl_WorkingDaysAndHours
where DATEDIFF(dd,convert(datetime, MondayWorkStartTime, 101),convert(datetime, '06/01/2009', 101))=0
SET @wHr = @wHr + @monhr/60
END ELSE IF UPPER(DATENAME(WEEKDAY,@curDate))= 'TUESDAY' begin
select @tue=TuesdayWorkDay, @tuehr=TuesdayWorkDurationInMinutes
from tbl_WorkingDaysAndHours
where DATEDIFF(dd,convert(datetime, TuesdayWorkStartTime, 101),convert(datetime, @curDate, 101))=0
SET @wHr = @wHr + @tuehr/60
end
ELSE IF UPPER(DATENAME(WEEKDAY,@curDate))= 'WEDNESDAY' begin
select @wed=WednesdayWorkDay, @wedhr=WednesdayWorkDurationInMinutes
from tbl_WorkingDaysAndHours
where DATEDIFF(dd,convert(datetime, WednesdayWorkStartTime, 101),convert(datetime, @curDate, 101))=0
SET @wHr = @wHr + @wedhr/60
end
ELSE IF UPPER(DATENAME(WEEKDAY,@curDate))='THURSDAY' begin
select @thu=ThursdayWorkDay, @thuhr=ThursdayWorkDurationInMinutes
from tbl_WorkingDaysAndHours
where DATEDIFF(dd,convert(datetime, ThursdayWorkStartTime, 101),convert(datetime, @curDate, 101))=0
SET @wHr = @wHr + @thuhr/60
end
ELSE IF UPPER(DATENAME(WEEKDAY,@curDate))= 'FRIDAY' begin
select @fri=FridayWorkDay, @frihr=FridayWorkDurationInMinutes
from tbl_WorkingDaysAndHours
where DATEDIFF(dd,convert(datetime, FridayWorkStartTime, 101),convert(datetime, @curDate, 101))=0
SET @wHr = @wHr + @frihr/60
end
ELSE IF UPPER(DATENAME(WEEKDAY,@curDate))= 'SATURDAY' begin
select @sat=SaturdayWorkDay, @sathr=SaturdaydayWorkDurationInMinutes
from tbl_WorkingDaysAndHours
where DATEDIFF(dd,convert(datetime, SaturdaydayWorkStartTime, 101),convert(datetime, @curDate, 101))=0
SET @wHr = @wHr + @sathr/60
end
ELSE IF UPPER(DATENAME(WEEKDAY,@curDate))= 'SUNDAY' begin
select @sun=SundayWorkDay, @sunhr=SundayWorkDurationInMinutes
from tbl_WorkingDaysAndHours
where DATEDIFF(dd,convert(datetime, SundayWorkStartTime, 101),convert(datetime, @curDate, 101))=0
SET @wHr = @wHr + @sunhr/60
end
set @curDate = @curDate + 1
end
RETURN @wHr
END
Here is the example,
declare @hrs int
set @hrs = dbo.fn_TotWorkingHrs('06/01/2009','06/15/2009')
select @hrs as totHours
"Don't limit your challenges, challenge your limits"
June 3, 2009 at 10:36 pm
Thanks dear friend Kruti,thanks for your help 🙂
Regards
Rajesh V R
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply