Help with function for business hours

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with function for business hours

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-18 : 08:33:36

I have created a function from Visakh's blog post below, removing the code for the holiday table as he instructs because I'm only interested in excluding weekends. Other than that, I created the function exactly as in the post.
[url]http://visakhm.blogspot.co.uk/2010/03/calculating-business-hours.html[/url]

The function runs, but doesn't seem to exclude Saturdays & Sundays. For example, when I run

SELECT dbo.BusinessHours('2013-12-09 08:00','2013-12-16 08:00','09:00','17:00') as TotalHours

The value of 56 is returned, when I'm wanting it to return 40 as I don't want to count weekends. Any help in amending the function would be appreciated.

The code I've used to create the function is:

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='BusinessHours' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.BusinessHours
GO
CREATE FUNCTION dbo.BusinessHours
(
@StartDate datetime, --start of period of consideration for calculation of business hours
@EndDate datetime, --end of period of consideration for calculation of business hours
@BusinessStart datetime, --start of business hours
@BusinessEnd datetime --end of business hours
)
RETURNS int
AS
BEGIN
DECLARE @TotalHours int
IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
BEGIN
IF @BusinessStart>@BusinessEnd
BEGIN
SET @TotalHours= -1
END
ELSE
BEGIN
SELECT @BusinessStart=CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) ELSE @BusinessStart END,
@BusinessEnd =CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) ELSE @BusinessEnd END
SET @TotalHours= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
THEN 0
ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)/60
END
END
END
ELSE
BEGIN
SELECT @StartDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),1))
WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))
ELSE @StartDate
END,
@EndDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),-1))
WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),0))
ELSE @EndDate
END
;With Calendar_CTE (Date,Day,WeekDay)
AS
(
SELECT DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)), CASE WHEN DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
UNION ALL
SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
FROM Calendar_CTE
WHERE DATEADD(dd,1,Date) <= @EndDate
)
SELECT @TotalHours=CEILING(SUM(
CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
END
)/60.0)
FROM Calendar_CTE c
OPTION (MAXRECURSION 0)
END
RETURN @TotalHours
END

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-12-18 : 17:09:01

Hi,

I have had a nearly similar project, and came up with the below sql. This sql will handle the business having different businesshours each weekday. Also it will show the details for the businesshours.


declare @start varchar(16)='2013-12-09 08:00';
declare @end varchar(16)='2013-12-16 08:00';
declare @bstart1 varchar(5)='00:00'; /* Sunday */
declare @bend1   varchar(5)='00:00'; /* Sunday */
declare @bstart2 varchar(5)='09:00'; /* Monday */
declare @bend2   varchar(5)='17:00'; /* Monday */
declare @bstart3 varchar(5)='09:00'; /* Tuesday */
declare @bend3   varchar(5)='17:00'; /* Tuesday */
declare @bstart4 varchar(5)='09:00'; /* Wednesday */
declare @bend4   varchar(5)='17:00'; /* Wednesday */
declare @bstart5 varchar(5)='09:00'; /* Thursday */
declare @bend5   varchar(5)='17:00'; /* Thursday */
declare @bstart6 varchar(5)='09:00'; /* Friday */
declare @bend6   varchar(5)='17:00'; /* Friday */
declare @bstart7 varchar(5)='00:00'; /* Saturday */
declare @bend7   varchar(5)='00:00'; /* Saturday */

with businesshours(fromdatetime,todatetime,enddatetime)
  as (select cast(cast(@start as date) as datetime)
            +cast(case when cast(@start as time)<cast(case datepart(dw,cast(@start as date))
                                                         when 1 then @bstart1
                                                         when 2 then @bstart2
                                                         when 3 then @bstart3
                                                         when 4 then @bstart4
                                                         when 5 then @bstart5
                                                         when 6 then @bstart6
                                                         when 7 then @bstart7
                                                      end as time
                                                     )
                     then case datepart(dw,cast(@start as date))
                             when 1 then @bstart1
                             when 2 then @bstart2
                             when 3 then @bstart3
                             when 4 then @bstart4
                             when 5 then @bstart5
                             when 6 then @bstart6
                             when 7 then @bstart7
                          end
                     else @start
                  end as time
                 )
             as fromdatetime
            ,cast(cast(@start as date) as datetime)
            +cast(case when cast(@start as date)=cast(@end as date)
                        and cast(@end as time)<cast(case datepart(dw,cast(@start as date))
                                                       when 1 then @bend1
                                                       when 2 then @bend2
                                                       when 3 then @bend3
                                                       when 4 then @bend4
                                                       when 5 then @bend5
                                                       when 6 then @bend6
                                                       when 7 then @bend7
                                                    end as time
                                                   )
                     then @end
                     else case datepart(dw,cast(@start as date))
                             when 1 then @bend1
                             when 2 then @bend2
                             when 3 then @bend3
                             when 4 then @bend4
                             when 5 then @bend5
                             when 6 then @bend6
                             when 7 then @bend7
                          end
                  end as time
                 )
             as todatetime
            ,cast(@end as datetime) as enddatetime
      union all
      select cast(cast(fromdatetime+1 as date) as datetime)
            +cast(case datepart(dw,cast(fromdatetime+1 as date))
                     when 1 then @bstart1
                     when 2 then @bstart2
                     when 3 then @bstart3
                     when 4 then @bstart4
                     when 5 then @bstart5
                     when 6 then @bstart6
                     when 7 then @bstart7
                  end as time
                 )
             as fromdatetime
            ,cast(cast(todatetime+1 as date) as datetime)
            +cast(case when cast(todatetime+1 as date)=cast(@end as date)
                        and cast(@end as time)<cast(case datepart(dw,cast(todatetime+1 as date))
                                                       when 1 then @bend1
                                                       when 2 then @bend2
                                                       when 3 then @bend3
                                                       when 4 then @bend4
                                                       when 5 then @bend5
                                                       when 6 then @bend6
                                                       when 7 then @bend7
                                                    end as time
                                                   )
                     then @end
                     else case datepart(dw,cast(todatetime+1 as date))
                             when 1 then @bend1
                             when 2 then @bend2
                             when 3 then @bend3
                             when 4 then @bend4
                             when 5 then @bend5
                             when 6 then @bend6
                             when 7 then @bend7
                          end
                  end as time
                 )
             as todatetime
            ,enddatetime
        from businesshours
       where enddatetime>cast(cast(fromdatetime+1 as date) as datetime)
                        +cast(case datepart(dw,cast(fromdatetime+1 as date))
                                 when 1 then @bstart1
                                 when 2 then @bstart2
                                 when 3 then @bstart3
                                 when 4 then @bstart4
                                 when 5 then @bstart5
                                 when 6 then @bstart6
                                 when 7 then @bstart7
                              end as time
                             )
     )
select a.fromdatetime
      ,a.todatetime
      ,datediff(mi,a.fromdatetime,a.todatetime) as timediffminutes
      ,datediff(mi,a.fromdatetime,a.todatetime)/60.0 as timediffhours
  from businesshours as a
 where datediff(mi,a.fromdatetime,a.todatetime)>0
   and a.enddatetime>a.fromdatetime
   and not exists (select *
                     from businessholiday as b
                    where b.fromdate>=cast(a.fromdatetime as date)
                      and b.todate<=cast(a.fromdatetime as date)
                  )
 option(maxrecursion 0)
select sum(datediff(mi,fromdatetime,todatetime))/60.0 as timediffhours
  from businesshours
 where datediff(mi,fromdatetime,todatetime)>0
   and enddatetime>fromdatetime
   and not exists (select *
                     from businessholiday as b
                    where b.fromdate>=cast(a.fromdatetime as date)
                      and b.todate<=cast(a.fromdatetime as date)
                  )
 option(maxrecursion 0)

Green section will show details, whereas red section will show the summarized hours (like you requested).
Limegreen and pink section is for avoiding dates from Holiday table.

Hope this works for you.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-18 : 17:43:21

Here's another version:


go
CREATE FUNCTION dbo.BusinessHours
       (
       @StartDate datetime, --start of period of consideration for calculation of business hours
       @EndDate datetime, --end of period of consideration for calculation of business hours
       @BusinessStart datetime, --start of business hours
       @BusinessEnd datetime --end of business hours
       )
returns int
as
begin

declare @st int
       , @end int
       , @stHr int
       , @endHr int
       , @out int
select @st = 0
       , @end = datediff(day, @StartDate, @endDate)
       , @stHr = datepart(hour, @businessStart)
       , @endHr = datepart(hour, @businessEnd)

;with
       a as (select convert(bigint,0) as n union all select 0),
       b as (select 0 as n from a as a cross join a as b),
       c as (select 0 as n from b as a cross join b as b),
       d as (select 0 as n from c as a cross join c as b),
       nums as (select row_number() over (order by (select 1)) as num from d as a cross join d as b),
       dts as (select dateadd(day, @st + num - 1, dateadd(day, datediff(day, 0, @startDate), 0)) as dt from nums where num <= @end-@st+1)

select @out = sum(
       --business hours for each day in the range
       case
        --any day other than first and last
        when datediff(day, @startDate, dt) > 0 and datediff(day, dt, @endDate) > 0 then @endHr - @stHr

        --first day when last day is a different day
        when datediff(day, @startDate, dt) = 0 and datediff(day, dt, @endDate) > 0 then
              case 
              when datepart(hour, @startDate) > @endHr then 0
              when datepart(hour, @startDate) > @stHr then @endHr - datepart(hour, @startDate)
              else @endHr - @stHr
              end

        --last day when first day is a different day
        when datediff(day, @startDate, dt) > 0 and datediff(day, dt, @endDate) = 0 then
              case 
              when datepart(hour, @endDate) < @stHr then 0
              when datepart(hour, @endDate) >= @endHr then @endHr - @stHr
              else datepart(hour, @endDate) - @stHr
              end

        --when only a one day range
        when datediff(day, @startDate, dt) = 0 and datediff(day, dt, @endDate) = 0 then
              case 
              when datepart(hour, @endDate) < @stHr then 0
              when datepart(hour, @startDate) > @endHr then 0
              else
                     case
                     when datepart(hour, @endDate) > @endHr then @endHr
                     else datepart(hour, @endDate) 
                     end
                     -
                     case
                     when datepart(hour, @startDate) > @stHr then datepart(hour, @startDate)
                     else @stHr
                     end
              end
        else 0
        end) 
from   dts
--limit to weekdays
where  datediff(day, 0, dt) % 7 < 5 

return @out

end
go

Be One with the Optimizer
TG

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-19 : 04:55:36

Thanks TG that works as I wanted. One more question though, is it possible to return the hours AND minutes as a decimal to two places instead of a whole hour so my query returns the actual hours & minutes instead of to the whole hour? The query below illustrates how it is now (great) but machine was actually down only for 6.07 hours within working hours (excuse my dodgy mental arithmetic here) not 7:

select worknumber, date_in, est_complete_date, Case when [MCSReports].BusinessHours(date_in,est_complete_date, '08:00','17:00') is null then 0 else [MCSReports].BusinessHours(date_in,est_complete_date, '08:00','17:00') end [DownTime] 
 from worksorderhdr

Example result:

worknumber	date_in	      est_complete_date	          DownTime
DN-000009	2011-11-30 11:56:00	2011-12-01 09:00:00	7

Many thanks for your help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-19 : 10:13:23

>>AND minutes as a decimal to two places

do you mean hundredths of an hour or do you mean minutes?
ie: should 1 1/2 hours (1 hour 30 minutes) be 1.30 or 1.50?

in your original post the return type was INT. What should the new return type be?

keep in mind that if you choose a numeric return type but you want 1.30 to represent 1 hour 30 minutes then you can't really do math on the value. Well, you can but it will be wrong.

EDIT:
Or perhaps we could leave it as INT and return the total business minutes. Then you can do with it what you need.

Be One with the Optimizer
TG

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-19 : 10:55:15

I mean hundredths, so yes 1hr 30mins would like to see 1.50 returned.

How easy to change the return type to decimal in the function?

Many thanks
Martyn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 11:00:31

quote:

Originally posted by wembleybear

I have created a function from Visakh's blog post below, removing the code for the holiday table as he instructs because I'm only interested in excluding weekends. Other than that, I created the function exactly as in the post.
[url]http://visakhm.blogspot.co.uk/2010/03/calculating-business-hours.html[/url]

The function runs, but doesn't seem to exclude Saturdays & Sundays. For example, when I run

SELECT dbo.BusinessHours('2013-12-09 08:00','2013-12-16 08:00','09:00','17:00') as TotalHours

The value of 56 is returned, when I'm wanting it to return 40 as I don't want to count weekends. Any help in amending the function would be appreciated.

The code I've used to create the function is:

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='BusinessHours' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.BusinessHours
GO
CREATE FUNCTION dbo.BusinessHours
(
@StartDate datetime, --start of period of consideration for calculation of business hours
@EndDate datetime, --end of period of consideration for calculation of business hours
@BusinessStart datetime, --start of business hours
@BusinessEnd datetime --end of business hours
)
RETURNS int
AS
BEGIN
DECLARE @TotalHours int
IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
BEGIN
IF @BusinessStart>@BusinessEnd
BEGIN
SET @TotalHours= -1
END
ELSE
BEGIN
SELECT @BusinessStart=CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) ELSE @BusinessStart END,
@BusinessEnd =CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) ELSE @BusinessEnd END
SET @TotalHours= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
THEN 0
ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)/60
END
END
END
ELSE
BEGIN
SELECT @StartDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),1))
WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))
ELSE @StartDate
END,
@EndDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),-1))
WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),0))
ELSE @EndDate
END
;With Calendar_CTE (Date,Day,WeekDay)
AS
(
SELECT DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)), CASE WHEN DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
UNION ALL
SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
FROM Calendar_CTE
WHERE DATEADD(dd,1,Date) <= @EndDate
)
SELECT @TotalHours=CEILING(SUM(
CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
END
)/60.0)
FROM Calendar_CTE c
WHERE WeekDay=1
OPTION (MAXRECURSION 0)
END
RETURN @TotalHours
END

Its working fine for me

see below screenshot

If you see the code in blue thats where I filter for Weekends
The reason why it didnt work for you is because you left out a filter from original article which is where we exclude the weekend as shown in red above
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-19 : 11:33:21

Visakh

You are of course correct, I had incorrectly removed that line and it works perfectly now - thank you.

Could you help with my other query above but related to your code please; if I wanted the time difference in decimal to two places, what would I need to change in the function code? I'd like to know if I can see the exact time difference rather than whole hours e.g. 1hr 30 mins as 1.50 hours.

Many thanks for your help
Martyn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 11:40:37

just change return type from int to decimal(10,2) and then you'll get decimal result.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-19 : 12:11:17

Visakh

Almost there - it's returning a decimal result but only in full hours, 40.0, 10.0 etc. How do I get it to include the result to the exact decimal minutes?

Thanks
Martyn

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-19 : 12:18:24

Here's my version if you want it:


go
create function dbo.BusinessHours
       (
       @StartDate datetime, --start of period of consideration for calculation of business hours
       @EndDate datetime, --end of period of consideration for calculation of business hours
       @BusinessStart datetime, --start of business hours
       @BusinessEnd datetime --end of business hours
       )
returns decimal(7,2)
as
begin

declare @st int
       , @end int
       , @sumMins int
select @st = 0
       , @end = datediff(day, @StartDate, @endDate)

;with
       a as (select convert(bigint,0) as n union all select 0),
       b as (select 0 as n from a as a cross join a as b),
       c as (select 0 as n from b as a cross join b as b),
       d as (select 0 as n from c as a cross join c as b),
       nums as (select row_number() over (order by (select 1)) as num from d as a cross join d as b),
       dts as (select dateadd(day, @st + num - 1, dateadd(day, datediff(day, 0, @startDate), 0)) as dt from nums where num <= @end-@st+1)

select @sumMins = sum(
       --business hours for each day in the range
       case
        --any day other than first and last
        when datediff(day, @startDate, dt) > 0 and datediff(day, dt, @endDate) > 0 then datediff(minute, @BusinessStart, @BusinessEnd)

        --first day when last day is a different day
        when datediff(day, @startDate, dt) = 0 and datediff(day, dt, @endDate) > 0 then
              case 
              when dateadd(day, datediff(day, @startDate, 0), @startDate) > @BusinessEnd then 0
              when dateadd(day, datediff(day, @startDate, 0), @startDate) > @BusinessStart then datediff(minute, dateadd(day, datediff(day, @startDate, 0), @startDate), @businessEnd)
              else datediff(minute, @businessStart, @businessEnd)
              end


        --last day when first day is a different day
        when datediff(day, @startDate, dt) > 0 and datediff(day, dt, @endDate) = 0 then
              case 
              when dateadd(day, datediff(day, @endDate, 0), @endDate) < @businessStart then 0
              when dateadd(day, datediff(day, @endDate, 0), @endDate) >= @BusinessEnd then datediff(minute, @BusinessStart, @BusinessEnd)
              else datediff(minute, @BusinessStart, dateadd(day, datediff(day, @endDate, 0), @endDate))
              end

        --when only a one day range
        when datediff(day, @startDate, dt) = 0 and datediff(day, dt, @endDate) = 0 then
              case 
              when dateadd(day, datediff(day, @endDate, 0), @endDate) < @businessStart then 0
              when dateadd(day, datediff(day, @startDate, 0), @startDate) > @BusinessEnd then 0
              else datediff(minute, 
                     case
                     when dateadd(day, datediff(day, @startDate, 0), @startDate) > @businessStart then dateadd(day, datediff(day, @startDate, 0), @startDate)
                     else @businessStart
                     end
                     ,
                     case 
                     when dateadd(day, datediff(day, @endDate, 0), @endDate) > @BusinessEnd then @businessEnd
                     else dateadd(day, datediff(day, @endDate, 0), @endDate)
                     end
                     )
              end
        else 0
        end)
from   dts
--limit to weekdays
where  datediff(day, 0, dt) % 7 < 5 

return convert(decimal(7,2), @sumMins/60.0)

end
go

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 12:32:10

quote:
Originally posted by wembleybear

Visakh

Almost there - it's returning a decimal result but only in full hours, 40.0, 10.0 etc. How do I get it to include the result to the exact decimal minutes?

Thanks
Martyn

change like this and try


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='BusinessHours' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.BusinessHours
GO
CREATE FUNCTION dbo.BusinessHours
(
@StartDate datetime, --start of period of consideration for calculation of business hours
@EndDate datetime, --end of period of consideration for calculation of business hours
@BusinessStart datetime, --start of business hours
@BusinessEnd datetime --end of business hours
)
RETURNS varchar(25)
AS
BEGIN
DECLARE @TotalHours varchar(25)
IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
BEGIN
 IF @BusinessStart>@BusinessEnd
 BEGIN
  SET @TotalHours= -1
 END
 ELSE
BEGIN
SELECT @BusinessStart=CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) ELSE @BusinessStart END,
@BusinessEnd =CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) ELSE @BusinessEnd END
  SET @TotalHours= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
      THEN 0
ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)/60
END
END
END
ELSE
BEGIN
 SELECT @StartDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),1))
 WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))
   ELSE @StartDate
   END,
  @EndDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),-1))
 WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),0))
   ELSE @EndDate
   END
  ;With Calendar_CTE (Date,Day,WeekDay)
 AS
 (
 SELECT DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)), CASE WHEN DATEDIFF(dd,0,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))%7 <5 THEN 1 ELSE 0 END
  UNION ALL
  SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATEDIFF(dd,0,DATEADD(dd,1,Date))%7 < 5 THEN 1 ELSE 0 END
 FROM Calendar_CTE
 WHERE DATEADD(dd,1,Date) <= @EndDate
 )
 
  SELECT @TotalHours=CAST(SUM(
   CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
   WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
   ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
   END
   )/60 AS varchar(10))+ ':' + CAST(SUM(
   CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
   WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
   ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
   END
   )%60 AS varchar(10))
 FROM Calendar_CTE c
 WHERE WeekDay=1
 OPTION (MAXRECURSION 0)
END
RETURN @TotalHours
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-19 : 12:52:12

Visakh

This returns the downtime in my query as e.g. 8:7 hours, anyway to actually get it as decimal 8.7 as I'll want to add them up at some point. Also if my second datetime column (est_complete_date) is null it throws an error because of my case statement, because in that situation I want the downtime set to zero (if job is not complete, we don't know how long downtime is, so don't want to count it:

Case when [MCSReports].BusinessHours(date_in,est_complete_date, '08:00','17:00') is null then '0' else [MCSReports].BusinessHours(date_in,est_complete_date, '08:00','17:00') end [DownTime]

Thanks
Martyn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 13:17:38

This will give you value in decimal form


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='BusinessHours' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.BusinessHours
GO
CREATE FUNCTION dbo.BusinessHours
(
@StartDate datetime, --start of period of consideration for calculation of business hours
@EndDate datetime, --end of period of consideration for calculation of business hours
@BusinessStart datetime, --start of business hours
@BusinessEnd datetime --end of business hours
)
RETURNS decimal(10,2)
AS
BEGIN
DECLARE @TotalHours decimal(10,2)
IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
BEGIN
 IF @BusinessStart>@BusinessEnd
 BEGIN
  SET @TotalHours= -1
 END
 ELSE
BEGIN
SELECT @BusinessStart=CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) ELSE @BusinessStart END,
@BusinessEnd =CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) ELSE @BusinessEnd END
  SET @TotalHours= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
      THEN 0
ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)/60
END
END
END
ELSE
BEGIN
 SELECT @StartDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),1))
 WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))
   ELSE @StartDate
   END,
  @EndDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),-1))
 WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),0))
   ELSE @EndDate
   END
  ;With Calendar_CTE (Date,Day,WeekDay)
 AS
 (
 SELECT DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)), CASE WHEN DATEDIFF(dd,0,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))%7 <5 THEN 1 ELSE 0 END
  UNION ALL
  SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATEDIFF(dd,0,DATEADD(dd,1,Date))%7 < 5 THEN 1 ELSE 0 END
 FROM Calendar_CTE
 WHERE DATEADD(dd,1,Date) <= @EndDate
 )
 
  SELECT @TotalHours=(SUM(
   CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
   WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
   ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
   END
   )/60) +  ((SUM(
   CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
   WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
   ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
   END
   )%60)*.01)
 FROM Calendar_CTE c
 WHERE WeekDay=1
 OPTION (MAXRECURSION 0)
END
RETURN @TotalHours
END

for second part is this enough?

ISNULL([MCSReports].BusinessHours(date_in,est_complete_date, '08:00','17:00'),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 13:25:23

One more thing to note in my version is passing end date as 2013-12-16 means it takes end date as 2013-12-16 00:00:00 so it will calculate inclusive dates as 9 to 15 dec ie as 16th endtime is before start of business (09:00). so the result would be 42 hrs 30 mins (8 hrs 30 mins * 5). If you want to include 16th Dec also you need to end date as 2013-12-17 or 2013-12-16 with time part after 17:30

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-19 : 13:55:49

Comparing our latest versions Visakh and I have different answers - similar execution time. My decimals are hundredths of an hour. Not sure what Visakh's minutes represent. I thought it was minutes but the second query doesn't seem to return that.
Based on the OPs earlier post I think he wants hundredths of an hour:

quote:
I mean hundredths, so yes 1hr 30mins would like to see 1.50 returned.


select dbo.BusinessHoursTG      ('2011-11-30 11:56:00', '2011-12-01 09:00:00', '08:00', '17:00') --6.07
select dbo.BusinessHoursVisakh16('2011-11-30 11:56:00', '2011-12-01 09:00:00', '08:00', '17:00') --6.04

select dbo.BusinessHoursTG      ('2011-11-30 11:00:00', '2011-11-30 12:30:00', '08:00', '17:00') --1.50
select dbo.BusinessHoursVisakh16('2011-11-30 11:00:00', '2011-11-30 12:30:00', '08:00', '17:00') --1.00

Be One with the Optimizer
TG

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-19 : 14:28:27

Visakh

Yes, TG is right - I meant hundredths of an hour, so 1 hour 45 minutes should be 1.75 not 1.45 - but the results are also not quite right:

worknumber	date_in	       est_complete_date	   Downtime
IP-DN-053788	2013-12-10 15:00:00	2013-12-10 16:30:00	1.00
IP-DN-053789	2013-12-10 16:30:00	2013-12-10 17:30:00	0.00

First one above Downtime should be 1.50
Second one above Downtime should be 0.50

Also, I changed my query for Downtime to your suggestion, but this seems to give unexpected results:

worknumber	date_in	     est_complete_date	Downtime
IP-DN-053792	2013-12-11 10:20:00	NULL	6.40

Thanks
Martyn

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-19 : 14:41:23

quote:
Originally posted by wembleybear
First one above Downtime should be 1.50
Second one above Downtime should be 0.50

Those are the answers my function returns.
If you already rejected my function that's fine - I'll stop posting :)

Be One with the Optimizer
TG

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-19 : 15:45:03

On the contrary I have gone with your function TG, as that gives the results I expect and in the format I wanted.

But thank you very much to you both TG & Visahk as your help has been invaluable today :-)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-19 : 16:13:50

you're welcome - glad we could help.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources