[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

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
0
Louis Capece
Asked:
Louis Capece
  • 5
  • 2
2 Solutions
 
twoboatsCommented:
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

0
 
twoboatsCommented:
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

0
 
Scott PletcherSenior DBACommented:
First, create a table of sequential numbers from **0** to at least the max number of days difference you will need -- or just create 2000 or 3000 rows to be safe :-).  For example:
CREATE TABLE seqNums (
    seqNum SMALLINT NOT NULL,
    CONSTRAINT seqNums_CI
        UNIQUE CLUSTERED (seqNum)
    )
-- load table with values from *0* to, say, 2000


Then:

CREATE FUNCTION SecondsDiff (
    @date1 DATETIME,
    @date2 DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @totalSeconds INT
SET @totalSeconds = 0
SELECT @totalSeconds = @totalSeconds +
    CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, seqNum, @date1)) IN ('Saturday', 'Sunday')
         THEN 0
         ELSE CASE
              WHEN seqNum = 0 --first (maybe only) day
              THEN CASE WHEN DATEPART(HOUR, @date1) >= 19 THEN 0
                   ELSE DATEDIFF(SECOND,
                        CASE WHEN DATEPART(HOUR, @date1) < 07
                             THEN CONVERT(CHAR(8), @date1, 112) + ' 07:00' ELSE @date1 END,
                        CASE WHEN (DATEDIFF(DAY, @date1, @date2) > 0 OR DATEPART(HOUR, @date2) >= 19)
                             THEN CONVERT(CHAR(8), @date1, 112) + ' 19:00' ELSE @date2 END)
                   END
              WHEN seqNum = DATEDIFF(DAY, @date1, @date2) --last day
              THEN CASE WHEN DATEPART(HOUR, @date2) < 7 THEN 0
                   WHEN DATEPART(HOUR, @date2) >= 19 THEN 12 * 60 * 60 -- full day
                   ELSE DATEDIFF(SECOND, CONVERT(CHAR(8), @date2, 112) + ' 07:00', @date2)
                   END
              ELSE 12 * 60 * 60 -- in-between day, count all work hours
              END
          END
FROM seqNums
WHERE seqNum BETWEEN 0 AND DATEDIFF(DAY, @date1, @date2)
RETURN @totalSeconds
END --FUNCTION
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Scott PletcherSenior DBACommented:
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.
0
 
twoboatsCommented:
Yes, quite


0
 
twoboatsCommented:
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
0
 
twoboatsCommented:
Now, use this query batch to test the logic, by changing set statements for @dt1 and @dt2. Once your satisifed it's correct, it can be wrapped up in a function

declare
      @sign smallint,
      @dt1 datetime,
      @dt2 datetime,
      @tmp datetime,
      @0700s int,
      @1900s int,
      @dt1times int,
      @dt2times int,
      @secs int

set @0700s = 7*3600
set @1900s = 19*3600

set @dt1 = convert (datetime, '2007-07-23 07:00:00.000', 121)
set @dt2 = convert (datetime, '2007-07-23 19:00:00.000', 121)
set @sign = 1

-- are the dates wrong way round
if @dt1>@dt2
begin      
      set @sign = -1
      set @tmp = @dt1
      set @dt1 = @dt2
      set @dt2 = @tmp
end

-- if either date is a sat or sun, set to monday 00:00
select      @dt1 = case (datepart(dw,@dt1))
      when 1 then dbo.tzero(@dt1)+1
      when 7 then dbo.tzero(@dt1)+2
      else @dt1
      end,
      @dt2 = case (datepart(dw,@dt2))
      when 1 then dbo.tzero(@dt2)+1
      when 7 then dbo.tzero(@dt2)+2
      else @dt2
      end


set @dt1times = datediff(s,dbo.tzero(@dt1), @dt1)
select @dt1 =
      case
      when @dt1times < @0700s then dateadd(s,@0700s,dbo.tzero(@dt1))
      when @dt1times > @1900s then dateadd(s,@1900s,dbo.tzero(@dt1))
      else @dt1
      end

set @dt2times = datediff(s,dbo.tzero(@dt2), @dt2)
select @dt2 =
      case
      when @dt2times < @0700s then dateadd(s,@0700s,dbo.tzero(@dt2))
      when @dt2times > @1900s then dateadd(s,@1900s,dbo.tzero(@dt2))
      else @dt2
      end

-- over second difference
set @secs = datediff(s, @dt1,@dt2)

-- take out the non work time from each day
-- the 19:00 to 07:00
set @secs = @secs - (datediff(d,@dt1,@dt2)*12*3600)

-- take out the non work time for each weeekend
-- the 07:00 to 19:00
set @secs = @secs - (datediff(wk,@dt1,@dt2)*12*3600*2)

-- sign change
set @secs = @secs * @sign

select      @secs secs
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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