asked on

SQL Server: Elapsed number of seconds during weekday business hours.

LOOKING FOR SQL SERVER (2000) SCALAR FUNCTION OR SIMILAR SOLUTION....
Need to calculate the number of elapsed seconds between two DateTimes variables.
Exceptions are as follows:
1. DO NOT count weekends (12:01AM Saturday thru 11:59PM Sunday)
2. Do not count hours outside of weekday business hours. (7:00PM thru 7:00AM)

In short, we need to only count the elapsed number seconds that are from MON-FRO 7:00AM to 7:00PM
Thanks in Advance!
Louis

twoboats

IF EXISTS (SELECT *
       FROM sysobjects
       WHERE name = N'ssdiff')
      DROP FUNCTION ssdiff
GO

CREATE FUNCTION ssdiff (@dt1 datetime, @dt2 datetime)
RETURNS int
AS
BEGIN
declare @mdt1 datetime, @mdt2 datetime, @days1 int, @days2 int, @wks int,
      @totdays int, @secs int, @ptdy1secs int, @ptdy2secs int

select      @days1 = case (datepart(dw,@dt1)) when 1 then 5 when 7 then 0 else 6-datepart(dw,@dt1) end,
      @days2 = case (datepart(dw,@dt2)) when 1 then 5 when 7 then 0 else 6-datepart(dw,@dt2) end

select @wks = datediff(wk,@dt1, @dt2)

if @wks > 1
      set @totdays = (@wks * 5) + @days1 + @days2
else
      set @totdays = @days1 + @days2

if (datepart(dw,@dt2)) between 2 and 6
begin
      select @mdt1 = convert(datetime, convert(varchar(25), @dt1, 101))
      set @ptdy1secs = case
            when datediff(s,@mdt1, @dt1) < 25200 then 0
            when datediff(s,@mdt1, @dt1) > 25200 and datediff(s,@mdt1, @dt1) <= 68400 then datediff(s,@mdt1, @dt1) - 25200
            else 68400
            end
end
else
      set @ptdy1secs = 0

if (datepart(dw,@dt2)) between 2 and 6
begin
      select @mdt2 = convert(datetime, convert(varchar(25), @dt2, 101))
      set @ptdy2secs = case
            when datediff(s,@mdt2, @dt2) >= 68400 then 0
            when datediff(s,@mdt2, @dt2) < 68400 and datediff(s,@mdt2, @dt2) > 25200 then 68400- datediff(s,@mdt2, @dt2)
            else 68400
            end
end
else
      set @ptdy2secs = 0

set @secs = (12*3600*@totdays) + @ptdy1secs + @ptdy2secs

--select @dt1, @dt2, datepart(dw,@dt1) wkdy1, datepart(dw,@dt2) wkdy2, @days1, @days2, @wks wks,
--      @totdays totdays, @secs secs, @ptdy1secs pt1, @ptdy2secs pt2

return @secs
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.ssdiff (getdate()-78, getdate())
GO

twoboats

Bug in that above

-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
       FROM sysobjects
       WHERE name = N'ssdiff')
      DROP FUNCTION ssdiff
GO

CREATE FUNCTION ssdiff (@dt1 datetime, @dt2 datetime)
RETURNS int
AS
BEGIN
declare @mdt1 datetime, @mdt2 datetime, @days1 int, @days2 int, @wks int,
      @totdays int, @secs int, @ptdy1secs int, @ptdy2secs int

select      @days1 = case (datepart(dw,@dt1)) when 1 then 5 when 7 then 0 else 6-datepart(dw,@dt1) end,
      @days2 = case (datepart(dw,@dt2)) when 1 then 5 when 7 then 0 else 6-datepart(dw,@dt2) end

select @wks = datediff(wk,@dt1, @dt2)

if @wks > 1
      set @totdays = (@wks * 5) + @days1 + @days2
else
      set @totdays = @days1 + @days2

if (datepart(dw,@dt2)) between 2 and 6
begin
      select @mdt1 = convert(datetime, convert(varchar(25), @dt1, 101))
      set @ptdy1secs = case
            when datediff(s,@mdt1, @dt1) < 25200 then 0
            when datediff(s,@mdt1, @dt1) > 25200 and datediff(s,@mdt1, @dt1) <= 68400 then datediff(s,@mdt1, @dt1) - 25200
            else 43200
            end
end
else
      set @ptdy1secs = 0

if (datepart(dw,@dt2)) between 2 and 6
begin
      select @mdt2 = convert(datetime, convert(varchar(25), @dt2, 101))
      set @ptdy2secs = case
            when datediff(s,@mdt2, @dt2) >= 68400 then 0
            when datediff(s,@mdt2, @dt2) < 68400 and datediff(s,@mdt2, @dt2) > 25200 then 68400- datediff(s,@mdt2, @dt2)
            else 43200
            end
end
else
      set @ptdy2secs = 0

set @secs = (12*3600*@totdays) + @ptdy1secs + @ptdy2secs

--select @dt1, @dt2, datepart(dw,@dt1) wkdy1, datepart(dw,@dt2) wkdy2, @days1, @days2, @wks wks,
--      @totdays totdays, @secs secs, @ptdy1secs pt1, @ptdy2secs pt2

return @secs
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.ssdiff (getdate()-78, getdate())
GO

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

The SELECT computes the seconds for each day separately, based on adding the seqNum to the starting date (@date1).

I think the ssdiff function still has at least one bug:

DECLARE @date1 DATETIME
DECLARE @date2 DATETIME
SET @date1 = '20070722 08:00'
SET @date2 = '20070727 18:45'

SELECT dbo.SecondsDiff(@date1, @date2),
dbo.SecondsDiff(@date1, @date2) / 3600.00
SELECT dbo.ssdiff(@date1, @date2),
dbo.ssdiff(@date1, @date2) / 3600.00

You can tell fairly quickly just by looking at the dates that the correct total should be a full week minus 15 minutes.

twoboats

Yes, quite

twoboats

First off, create the function below, which returns the 00:00:00.000 time date of any given date

IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'tzero')
DROP FUNCTION tzero
GO

CREATE FUNCTION tzero (@dt datetime)
RETURNS datetime
AS
BEGIN
return convert(datetime, convert(varchar(25), @dt, 101),101)
END
go

select dbo.tzero(getdate())
go

ASKER CERTIFIED SOLUTION

twoboats

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial