I need a formula to calculate the time (let's say in minutes) between two dates/times.
The problem is that I have to exclude the time between 06 PM and 06 AM and also exclude the time in the weekend (Saturday and Sunday).
I will use this in a couple of reports made in Reporting Services. The main purpouse is to keep track of the time an incident is open in a help desk program and non working hours should not be counted.
If anyone have an algoritm that could be modified for this and is willing to share this I would be very grateful.
Many thanks!
/Per Lissel
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comThis made me curious. I didn't have an answer but I knew a group that would.
Being the Reporting Services MVP I have access to the private SQL Server MVP
newsgroup. These guys are truly amazing what they can do with SQL
statements. MVP Steve Kass provided me with the following. Essentially you
create a table that has on and off times. You insert rows for any week in
the past (his example did this for the year 2000). Then the rest of the
script fills in the table with values for 2100 weeks of values.
Next the fuction creation uses this helper table to come back with the
values. It makes my brain hurt to follow what the function is doing. Anyway,
give it a try and test it out from outside of Reporting Services.
This will also allow you to fill in Holidays as well.
>>>>>>>>>>>>>>
Bruce,
See if this approach with a helper table fits the bill:
create table Minute_Count(
d datetime primary key,
elapsed_minutes int not null,
switch varchar(10) not null check (switch in ('On','Off'))
);
insert into Minute_Count values ('2000-01-03T06:00:00',0,'On');
insert into Minute_Count values ('2000-01-03T18:00:00',12*60,'Off');
insert into Minute_Count values ('2000-01-04T06:00:00',12*60,'On');
insert into Minute_Count values ('2000-01-04T18:00:00',24*60,'Off');
insert into Minute_Count values ('2000-01-05T06:00:00',24*60,'On');
insert into Minute_Count values ('2000-01-05T18:00:00',36*60,'Off');
insert into Minute_Count values ('2000-01-06T06:00:00',36*60,'On');
insert into Minute_Count values ('2000-01-06T18:00:00',48*60,'Off');
insert into Minute_Count values ('2000-01-07T06:00:00',48*60,'On');
insert into Minute_Count values ('2000-01-07T18:00:00',60*60,'Off');
-- any Monday-Friday week
declare @.week int;
set @.week = 1;
while @.week < 2100 begin
insert into Minute_Count
select
dateadd(week,@.week,d),
elapsed_minutes + 60*@.week*60,
switch
from Minute_Count
set @.week = @.week * 2
end;
-- select * from Minute_Count;
go
create function DeskTimeDiff_minutes(
@.from datetime,
@.to datetime
) returns int as begin
declare @.fromSerial int;
declare @.toSerial int;
with S(d,elapsed_minutes,switch) as (
select top 1 d,elapsed_minutes, switch
from Minute_Count
where d <= @.from
order by d desc
)
select @.fromSerial = elapsed_minutes +
case when switch = 'On'
then datediff(minute,d,@.from)
else 0 end
from S;
with S(d,elapsed_minutes,switch) as (
select top 1 d,elapsed_minutes, switch
from Minute_Count
where d <= @.to
order by d desc
)
select @.toSerial = elapsed_minutes +
case when switch = 'On'
then datediff(minute,d,@.to)
else 0 end
from S;
return @.toSerial - @.fromSerial;
end;
go
>>>>>>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Per Lissel" <per.lissel[at]outokumpu.com> wrote in message
news:02479620-d21d-463d-8c50-07e27ca783c8@.developmentnow.com...
>I need a formula to calculate the time (let's say in minutes) between two
>dates/times.
> The problem is that I have to exclude the time between 06 PM and 06 AM and
> also exclude the time in the weekend (Saturday and Sunday).
> I will use this in a couple of reports made in Reporting Services. The
> main purpouse is to keep track of the time an incident is open in a help
> desk program and non working hours should not be counted.
> If anyone have an algoritm that could be modified for this and is willing
> to share this I would be very grateful.
> Many thanks!
> /Per Lissel
>
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||OK, here is another solution from SQL Server MVP Allen White. Note that
you'll have to tweak it to get rig of calls to employee and location tables
(used to factor in time zone). Also the below code is going 8 to 5 so that
needs to be tweaked as well. Note that no additional table is needed.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
CREATE FUNCTION [dbo].fnGetWorkMinutes
(@.start_dt datetime=null, @.end_dt datetime=null, @.co_user_id int=null)
RETURNS int
AS
-- Returns the number of weekday hours between the supplied
dates
BEGIN
declare @.d1 datetime, @.d2 datetime, @.d1a datetime, @.d2a datetime,
@.hours float, @.minutes int, @.offset int
set @.offset = 0
if @.co_user_id is not null
begin
select @.offset = l.timezone_offset
from [HelpDesk].[dbo].[Company_User] c INNER JOIN
[HelpDesk].[dbo].[Location_Def] l
on c.[loc_id] = l.[loc_id]
where c.co_user_id = @.co_user_id
end
set @.d1 = dateadd(hour, @.offset, @.start_dt)
set @.d2 = dateadd(hour, @.offset, @.end_dt)
if convert(char(10),@.d1,101) = convert(char(10),@.d2,101)
BEGIN
SELECT @.minutes = DATEDIFF(minute, @.d1, @.d2)
END
ELSE
BEGIN
SET @.d1 = CASE WHEN @.d1 > convert(char(10),@.d1,101) + ' 17:00'
THEN convert(char(10),dateadd(day,1,@.d1),101) + ' 08:00'
ELSE @.d1 END
SET @.d2 = CASE WHEN @.d2 < convert(char(10),@.d2,101) + ' 08:00'
THEN convert(char(10),@.d2,101) + ' 08:00'
ELSE @.d2 END
SET @.d1 = CASE WHEN DATEPART(weekday,@.d1+@.@.DATEFIRST)=1 THEN
convert(char(10),dateadd(day,1,@.d1),101) + ' 08:00'
WHEN DATEPART(weekday,@.d1+@.@.DATEFIRST)=7 THEN
convert(char(10),dateadd(day,2,@.d1),101) + ' 08:00'
ELSE @.d1 END
SET @.d2 = CASE WHEN DATEPART(weekday,@.d2+@.@.DATEFIRST)=1 THEN
convert(char(10),dateadd(day,-2,@.d2),101) + ' 17:00'
WHEN DATEPART(weekday,@.d2+@.@.DATEFIRST)=7 THEN
convert(char(10),dateadd(day,-1,@.d2),101) + ' 17:00'
ELSE @.d2 END
set @.d1a = dateadd(day,1,@.d1)
set @.d2a = dateadd(day,-1,@.d2)
SELECT @.minutes = CASE WHEN DATEDIFF(day, @.d1, @.d2) > 1 THEN
(DATEDIFF(day, @.d1a, @.d2a) + 1 - (2 * DATEDIFF(week,
@.d1, @.d2))) * 480
ELSE 0 END +
CASE WHEN convert(char(10),@.d1,101) =convert(char(10),@.d2,101)
THEN DATEDIFF(minute, @.d1, @.d2)
WHEN convert(char(10),@.d1,101) <
convert(char(10),@.d2,101)
THEN DATEDIFF(minute, @.d1, convert(char(10),@.d1,101) +
' 17:00')
ELSE 0 END +
CASE WHEN DATEDIFF(day, @.d1, @.d2) > 0 THEN
DATEDIFF(minute, convert(char(10),@.d2,101) + ' 08:00',
@.d2)
ELSE 0 END
END
RETURN(@.minutes)
END
go|||On Dec 19, 4:16 am, Per Lissel<per.lissel[at]outokumpu.com> wrote:
> I need a formula to calculate the time (let's say in minutes) between two dates/times.
> The problem is that I have to exclude the time between 06 PM and 06 AM and also exclude the time in the weekend (Saturday and Sunday).
> I will use this in a couple of reports made in Reporting Services. The main purpouse is to keep track of the time an incident is open in a help desk program and non working hours should not be counted.
> If anyone have an algoritm that could be modified for this and is willing to share this I would be very grateful.
> Many thanks!
> /Per Lissel
> Fromhttp://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-se...
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com
Just write a .NET Function and put it in the Code window. I did this
in a WindowsApplication Project then copied it over...
In a TextBox, I used:
=Code.ElapsedBetweenBusinessDates( #12/12/2007 7:00#, #12/17/2007
15:00# )
and it returned at Render time:
1.20:00:00
which is a TimeSpan object of 1 day, 20 hours; or 44 hours. According
to your rules, that would be 11 hours on the 12th, 12 hours on the
13th, 12 on the 14th, none on the 15th (weekend), none on the 16th,
and 9 hours on the 17th, for a total of 44 hours.
Function ElapsedBetweenBusinessDates(ByVal d1 As Date, ByVal d2 As
Date) As System.TimeSpan
Dim stDT As Date, enDT As Date
Dim curDT As Date, curDD As Date, elapsedDT As New
System.TimeSpan
Dim cutoffDT As Date
If d1 < d2 Then ' make sure the dates are ordered
stDT = d1
enDT = d2
Else
stDT = d2
enDT = d1
End If
curDT = stDT
While curDT < enDT
curDD = curDT.Date
If Weekday(curDD) = 7 Then ' Saturday
curDT = DateAdd("d", 1, curDD)
ElseIf Weekday(curDD) = 1 Then ' Sunday
curDT = DateAdd("d", 1, curDD)
ElseIf Hour(curDT) < 6 Then ' if before 6 AM, round up
curDT = curDD.AddHours(6)
ElseIf curDT >= curDD.AddHours(18) Then ' if after 6 PM,
jump to next day
curDT = curDD.AddDays(1)
Else ' curDT is a businessday between 6 AM and 6 PM
cutoffDT = curDD.AddHours(18)
If cutoffDT > enDT Then cutoffDT = enDT
elapsedDT = elapsedDT.Add(cutoffDT.Subtract(curDT))
curDT = cutoffDT
End If
End While
ElapsedBetweenBusinessDates = elapsedDT
End Function
Now, with all that being said, if you have to be pedantic about it,
you should have a database that tracks what days are weekends and
company holidays, and use a SQL approach to calculating the span. We
have to use that approach here for billing purposes.
-- Scott|||Here is another answer from a Hugo Kornelis:
>>>>>>>
Hi Bruce,
I've seen similar requests in the groups. My usual approach is to:
1) Calculate real number of minutes (or hours, seconds, or whatever time
span is requested) using DATEDIFF.
2) Calculate the number of days between the dates, multiply by the
amount of non-business time between close and open time, subtract.
3) Calculate the number of weeks between the dates, multiply by the
amount of EXTRA non-business time in weekends (remember that the nights
have already been catered for!), subtract.
In a single formula:
DATEDIFF(minute, StartDate, EndDate)
- DATEDIFF(day, StartDate, EndDate) * 720
- DATEDIFF(week, StartDate, EndDate) * 1440
(untested)
--
Hugo Kornelis, SQL Server MVP
>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Orne" <polysillycon@.yahoo.com> wrote in message
news:3583aa33-e51a-4c97-8526-fad60056aa61@.w56g2000hsf.googlegroups.com...
> On Dec 19, 4:16 am, Per Lissel<per.lissel[at]outokumpu.com> wrote:
>> I need a formula to calculate the time (let's say in minutes) between two
>> dates/times.
>> The problem is that I have to exclude the time between 06 PM and 06 AM
>> and also exclude the time in the weekend (Saturday and Sunday).
>> I will use this in a couple of reports made in Reporting Services. The
>> main purpouse is to keep track of the time an incident is open in a help
>> desk program and non working hours should not be counted.
>> If anyone have an algoritm that could be modified for this and is willing
>> to share this I would be very grateful.
>> Many thanks!
>> /Per Lissel
>> Fromhttp://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-se...
>> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com
> Just write a .NET Function and put it in the Code window. I did this
> in a WindowsApplication Project then copied it over...
> In a TextBox, I used:
> =Code.ElapsedBetweenBusinessDates( #12/12/2007 7:00#, #12/17/2007
> 15:00# )
> and it returned at Render time:
> 1.20:00:00
> which is a TimeSpan object of 1 day, 20 hours; or 44 hours. According
> to your rules, that would be 11 hours on the 12th, 12 hours on the
> 13th, 12 on the 14th, none on the 15th (weekend), none on the 16th,
> and 9 hours on the 17th, for a total of 44 hours.
> Function ElapsedBetweenBusinessDates(ByVal d1 As Date, ByVal d2 As
> Date) As System.TimeSpan
> Dim stDT As Date, enDT As Date
> Dim curDT As Date, curDD As Date, elapsedDT As New
> System.TimeSpan
> Dim cutoffDT As Date
> If d1 < d2 Then ' make sure the dates are ordered
> stDT = d1
> enDT = d2
> Else
> stDT = d2
> enDT = d1
> End If
> curDT = stDT
> While curDT < enDT
> curDD = curDT.Date
> If Weekday(curDD) = 7 Then ' Saturday
> curDT = DateAdd("d", 1, curDD)
> ElseIf Weekday(curDD) = 1 Then ' Sunday
> curDT = DateAdd("d", 1, curDD)
> ElseIf Hour(curDT) < 6 Then ' if before 6 AM, round up
> curDT = curDD.AddHours(6)
> ElseIf curDT >= curDD.AddHours(18) Then ' if after 6 PM,
> jump to next day
> curDT = curDD.AddDays(1)
> Else ' curDT is a businessday between 6 AM and 6 PM
> cutoffDT = curDD.AddHours(18)
> If cutoffDT > enDT Then cutoffDT = enDT
> elapsedDT = elapsedDT.Add(cutoffDT.Subtract(curDT))
> curDT = cutoffDT
> End If
> End While
> ElapsedBetweenBusinessDates = elapsedDT
> End Function
>
> Now, with all that being said, if you have to be pedantic about it,
> you should have a database that tracks what days are weekends and
> company holidays, and use a SQL approach to calculating the span. We
> have to use that approach here for billing purposes.
> -- Scott
>
