Calculating time between 2 dates minus time from week(s) end(s)
-
Hi
I’m using the DATEDIFF function to retrieve minutes from 2 dates ( DATEDIFF(HH, date1, date2)),
I want to exclude from this value the time from the week-end
For example, if
date1 = 18-10-2006 15:00PM (Wednesday)
and
date2= 23-10-2006 15:00PM (Monday)
The value retrieved should be 120 (hours), but I want to exclude 48 Hours from the week-end , how can I achieve that ?
I’m thinking about using DATENAME(DW, Date), or (datepart) but….i’m stuck ….
Any guideline for this ?
-
DATEDIFF(hour, date1, date2) - (DATEDIFF(week, date1, date2) * 48)
providing date1 and date2 are not sat/sun dates
Far away is close at hand in the images of elsewhere.
Anon. -
Nice indeed J
It´s better now, however date1 and date2, can also be on Sundays and Saturdays…
-
This type of problem has occurred in a number of threads on this site with various answers, one of which is to do calculus on the days of the week by adjusting the start date forward to Mon if weekend and backwards for the end date and doing the calculation I posted.
Another method is to create a permanent Calendar table containing dates for several years and hold different values for each day, e.g. Weekday, Weekend, Bank Holiday, Hours in day, Working hours etc
Then you can use this table for joining or in your case selecting rows between your dates, summing the hours. You will have adjust the hours on the start/end dates according to the time portion of those dates.
Far away is close at hand in the images of elsewhere.
Anon. -
Hi, this is a script I have used to create a stored procedure that builds a calendar table between any two given dates. You may find it useful.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/************************************************
CREATE PROCEDURE
EXECUTING PROCESDURE REQUIRES TWO ARGUMENTS: -
@STARTDATE = THE FIRST DATE IN THE CALENDAR
@ENDDATE = THE LAST DATE IN THE CALENDAR
************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_create_calendar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_create_calendar]
GO
create procedure usp_create_calendar(@StartDate smalldatetime, @EndDate smalldatetime)
as
/************************************************
DROP EXISTING TABLE
************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dim_calendar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dim_calendar]
/************************************************
CREATE NEW CALENDAR TABLE
************************************************/
CREATE TABLE [dim_calendar](
CalDate smalldatetime NULL
, SOMDate smalldatetime NULL
, EOMDate smalldatetime NULL
, FinYear int NULL
, FinQtr tinyint NULL
, FinQtrName varchar (20) NULL
, FinMonth tinyint NULL
, CalMonthName varchar (20) NULL
, DateKey int NULL
, RentYear int NULL
, RentPrdWk int NULL
, DayOfWeek VARCHAR (10) NULL
 
/************************************************
INSERT FIRST RECORD INTO CALENDAR TABLE
************************************************/
INSERT INTO dim_calendar
SELECT @StartDate
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
/************************************************
DECLARE VARIABLE @MAXCALDATE WHICH IS THE
LAST DATE REQUIRED IN THE CALENDAR
************************************************/
declare @MaxCalDate smalldatetime
/************************************************
SET VARIABLE @MAXCALDATE = THE ARGUMENT @ENDDATE
************************************************/
set @MaxCalDate = @EndDate
/************************************************
WHILE THE DATE IS LESS THAN OR EQUAL TO THE
LAST DATE REQUIRED ADD ANOTHER RECORD TO
THE TABLE
************************************************/
while (@MaxCalDate <= @EndDate)
begin
insert into dim_calendar
select dateadd(dd,1,max(CalDate))
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
from dim_calendar
if (select max(CalDate) from dim_calendar)>= @EndDate
break
else
continue
end
/************************************************
UPDATE EACH RECORD WITH FINANCIAL YEAR, QUARTER
AND MONTH
************************************************/
update dim_calendar
set FinYear = (
select case
when datepart(mm,CalDate) between 4 and 12
then datepart(yyyy,CalDate)+1
else datepart(yyyy,CalDate)
end
 
,FinQtr = (
select case
when datepart(mm,CalDate) between 4 and 6
then 1
when datepart(mm,CalDate) between 7 and 9
then 2
when datepart(mm,CalDate) between 10 and 12
then 3
else 4
end
 
,FinQtrName = (
select case
when datepart(mm,CalDate) between 4 and 6
then 'Qtr. 1 '+cast((datepart(yyyy,CalDate)+1)as char)
when datepart(mm,CalDate) between 7 and 9
then 'Qtr. 2 '+cast((datepart(yyyy,CalDate)+1)as char)
when datepart(mm,CalDate) between 10 and 12
then 'Qtr. 3 '+cast((datepart(yyyy,CalDate)+1)as char)
else 'Qtr. 4 '+cast(datepart(yyyy,CalDate)as char)
end
 
,FinMonth = (
select case
when datepart(mm,CalDate) between 4 and 12
then datepart(mm,CalDate)-3
else datepart(mm,CalDate)+9
end
 
,CalMonthName = datename(dd,CalDate)+' '+datename(mm,CalDate)+' '+datename(yyyy,CalDate)
, DateKey = (DATEPART(yyyy,Caldate)*100)+DATEPART(mm,Caldate)
, RentYear = (
select case
when datepart(mm,CalDate) between 4 and 12
then datepart(yyyy,CalDate)+1
else datepart(yyyy,CalDate)
end
 
, DayOfWeek = DATENAME(dw,CalDate)
--finds the first (SOMDate) date of each month and the last date of each month (EOMDate) places results
--into a temporary table. This temporary table is then used to update the calendar table
select min(CalDate)SOMDate,
max(CalDate)EOMDate
into #tmp_cal_months
from dim_calendar
group by datepart(yyyy,CalDate),
datepart(mm,CalDate)
order by datepart(yyyy,CalDate),
datepart(mm,CalDate)
update c
set c.SOMDate = t1.SOMDate,
c.EOMDate = t1.EOMDate
from #tmp_cal_months t1,
dim_calendar c
where datepart(yyyy,t1.SOMDate) = datepart(yyyy,c.CalDate)
and
datepart(mm,t1.SOMDate) = datepart(mm,c.CalDate)
drop table #tmp_cal_months
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Regards
Duncan
-
It's amazing how many things you can get wrong doing date arithmetic. I think I may have posted this function a while back (see below) which calculates the number of workdays between any two dates. These are some requirements:
1. Should be efficient (i.e. not loop through the days or use tables)
2. Should handle negative differencences
3. Needs to include David's suggestion of normalizing the start and end dates
Whatever clever thing you come up with, I suggest you always bulk test it using an alternative function which does just the counting.
It should not be very difficult to apply this to the original problem. You need to take the (result-2)*24 and add the hours from the first day(if it's not on a weekend) and the hours from the last day(if it's not on a weekend).
create function trx_workdays1(@p_startdate datetime, @p_enddate datetime) returns integer as
begin
declare @startdate datetime
declare @enddate datetime
declare @dowSat int
declare @dowSun int
declare @normstartdate datetime
declare @normenddate datetime
declare @DW int
declare @dwdest int
declare @padded_enddate datetime
declare @padded_workdays int
declare @diff int
if @p_startdate is null or @p_enddate is null
return 0
-- truncate time portion
set @startdate=convert(varchar,year(@p_startdate))+substring(convert(varchar,month(@p_startdate)+100),2,2)+substring(convert(varchar,day(@p_startdate)+100),2,2)
set @enddate=convert(varchar,year(@p_enddate))+substring(convert(varchar,month(@p_enddate)+100),2,2)+substring(convert(varchar,day(@p_enddate)+100),2,2)
-- capture these for known dates as they may vary based on SET DATEFIRST
set @dowSat=datepart(dw,'2006-10-28')
set @dowSun=datepart(dw,'2006-10-29')
-- normalize start date by moving forward, i.e. should not be a saturday or sunday
set @normstartdate=@startdate
set @DW=datepart(dw,@normstartdate)
if @DW=@dowSun
set @normstartdate=dateadd(d,1,@normstartdate)
else
if @DW=@dowSat
set @normstartdate=dateadd(d,2,@normstartdate)
-- normalize end date by moving backwards, i.e. should not be a saturday or sunday
set @normenddate=@enddate
set @DW=datepart(dw,@normenddate)
if @DW=@dowSun
set @normenddate=dateadd(d,-2,@normenddate)
else
if @DW=@dowSat
set @normenddate=dateadd(d,-1,@normenddate)
set @padded_enddate=@normenddate
set @padded_workdays=0
-- pad end date so that difference becomes a multiple of 7 days;
-- we also need to count the number of weekdays in the days we added for the padding;
set @dwdest=datepart(dw,@startdate)-1
if @dwdest=0 set @dwdest=7
while datepart(dw,@padded_enddate)<>@dwdest -- loops no more than 6 times
begin
set @padded_enddate=dateadd(d,1,@padded_enddate)
set @DW=datepart(dw,@padded_enddate)
if @DW<>@dowSat and @DW<>@dowSun set @padded_workdays=@padded_workdays+1
end
set @diff=datediff(d,@startdate,@padded_enddate)+1
-- subtract number of intervening saturdays/sundays and subtract number of workdays we added for the padding
return @diff-@diff/7*2-@padded_workdays
end
-
Oops, line near end
set @diff=datediff(d,@startdate,@padded_enddate)+1
shoud read
set @diff=datediff(d,@normstartdate,@padded_enddate)+1
-
Here is a simple approach:
declare
@date1 datetime,@date2 datetime
select
@date1=CURRENT_TIMESTAMP
select
@date2=CURRENT_TIMESTAMP-2
print
@date1
print
@date2
if datename(dw,@date1) in('Friday','Saturday','Sunday')
begin
print datediff(hh,@date2,@date1)-48
end
if
datename(dw,@date1) not in('Friday','Saturday','Sunday')
begin
print datediff(hh,@date2,@date1)
end
-
I preffer not to use strings manipulation (performance on large datasets)., but your solution could use and else instead of a second if which should make it run pretty fast and make it easy to maintain.
-
When I set @date1 to Sun '2006-11-05' and @date1 to Wed '2006-11-01' your code returns 48 hours. That doesn't look right to me.
-
This will give you whole days... modify the "ends" of the range to do hours... also, not affected by @@DATEFIRST settings... very fast... very simple...
SELECT (DATEDIFF(dd,@StartDate,@EndDate)+1)
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1 ELSE 0 END)
For a full explanation of the pieces, please refer to the following URL:
http://www.sqlservercentral.com/columnists/jmoden/calculatingworkdays.asp
--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) -
Sorry... had posted a solution here (and deleted it) for hours that didn't take into account days that were on the weekends... I'll be right back with the correction....
--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) -
This should do it... don't worry about the couple of strings you see... this is still pretty darned fast and they make it independent of the @@DATEFIRST setting... (you can worry if you want, though)
SELECT CAST(
(
(DATEDIFF(dd,@StartDate,@EndDate)+1)
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1 ELSE 0 END)
)
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 0
ELSE CAST(CONVERT(CHAR(12),@StartDate,114) AS DATETIME) END)
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 0
ELSE (1.0-CAST(CONVERT(CHAR(12),@EndDate,114) AS DATETIME)) END)
AS FLOAT) * 24
--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) -
Hi
I aqm looking into solving the same problem,, but i need to make this in to a trigger. so when the user enters a start time and finish time is calculate the time diff.
can this stored procedure be converted in to a trigger and if so how.??
-
It's pretty straight forward. You should read up on how to create a trigger. Here is a complete example:
-- create a sample table
if exists (select name from dbo.sysobjects where name = 'my_table' and type='U') drop table my_table
go
create table my_table(
k integer identity(1,1),
d1 datetime,
d2 datetime,
duration integer)
go
-- create Jeff's workdays function
if exists (select name from sysobjects where name='workdays' and type='FN') drop function workdays
go
create function workdays(@p_startdate datetime, @p_enddate datetime) returns integer as
begin
return
(DATEDIFF(dd, @p_startdate, @p_enddate)+1)
-(DATEDIFF(wk, @p_startdate, @p_enddate) * 2)
-(CASE WHEN DATENAME(dw, @p_startdate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @p_enddate) = 'Saturday' THEN 1 ELSE 0 END)
end
go
-- create the trigger
if exists (select name from sysobjects where name='my_trigger' and type='TR') drop trigger my_trigger
go
create trigger my_trigger on my_table
for update,insert
as
if @@rowcount = 0 return
if not (update(d1) or update(d2)) return
update m
set duration=dbo.workdays(m.d1,m.d2)
from my_table m
join inserted i on m.k=i.k
where i.d1 is not null and i.d2 is not null
go
-- run some tests
insert into my_table(d1,d2)
values('20080503','20080511')
go
select * from my_table
go
update my_table set d2='20080512'
go
select * from my_table
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply