declare @starttime datetime ,@endtime datetime , @intervalincweekends int ; set @starttime = '03/13/2017 11:00:02' set @endtime = '03/30/2017 19:53:59' if datename(dw, @starttime) = 'saturday' set @starttime = cast(cast(dateadd(dd,2,@starttime) date) datetime); if datename(dw, @starttime) = 'sunday' set @starttime = cast(cast(dateadd(dd,1,@starttime) date) datetime); if datename(dw, @endtime) = 'saturday' set @endtime = dateadd(mi,-1, cast(cast(@endtime date) datetime)); if datename(dw, @endtime) = 'sunday' set @endtime = dateadd(mi,-1, cast(cast(dateadd(dd,-1,@endtime) date) datetime)); if @endtime <= @starttime begin select 0; end else begin declare @numberofweekenddays decimal(5,2) = 0; ;with cte ( select cast(dateadd(dd,1,@starttime) date) dt dateadd(dd,1,@starttime) < @endtime union select dateadd(dd,1,dt) cte dateadd(dd,1,dt) < @endtime ) select @numberofweekenddays = count(*) cte datename(dw, dt) in ('saturday','sunday'); select ((datediff(mi,@starttime,@endtime) - (@numberofweekenddays * 60 * 24))/60)/24 m; end how can change sql function can pass 2 dates parameter function, , time return inserted?
here option can turned table-valued funcion or scalar-valued function. strong recomendation tvf.
you have added option of excluding holidays. (currently listed values, table well).
example quick test
declare @d1 datetime ='2017-03-13 11:00:02' declare @d2 datetime ='2017-03-30 19:53:59' select count(*)/60.0/24.0 ( select top (datediff(minute,@d1,@d2)) d=dateadd(minute,-1+row_number() on (order (select null)),@d1) master..spt_values n1,master..spt_values n2 ) n datename(weekday,d) not in ('saturday','sunday') , d not in ( '2016-01-01' -- new year's day ,'2016-01-18' -- martin luther king, jr, ,'2016-02-15' -- washington''s birthday ,'2016-03-25' -- friday ,'2016-05-30' -- memorial day ,'2016-07-04' -- independence day ,'2016-09-05' -- labor day ,'2016-11-24' -- thanksgiving ,'2016-11-25' -- black friday ,'2016-12-26' -- christmas day ) returns
13.370138875 edit - if interested , fun - 2 functions tvf , svf
the table-valued-function
create function [dbo].[myfunctiontv] (@d1 datetime,@d2 datetime) returns table return ( select value = count(*)/60.0/24.0 ( select top (datediff(minute,@d1,@d2)) d=dateadd(minute,-1+row_number() on (order (select null)),@d1) master..spt_values n1,master..spt_values n2 ) n datename(weekday,d) not in ('saturday','sunday') , d not in ( '2016-01-01' -- new year's day ,'2016-01-18' -- martin luther king, jr, ,'2016-02-15' -- washington''s birthday ,'2016-03-25' -- friday ,'2016-05-30' -- memorial day ,'2016-07-04' -- independence day ,'2016-09-05' -- labor day ,'2016-11-24' -- thanksgiving ,'2016-11-25' -- black friday ,'2016-12-26' -- christmas day ) ) -- goal : 13.370138875 -- select * [dbo].[myfunctiontv]('2017-03-13 11:00:02','2017-03-30 19:53:59') the scalar-valued-function
create function [dbo].[myfunctionsv] (@d1 datetime,@d2 datetime) returns float begin return ( select count(*)/60.0/24.0 ( select top (datediff(minute,@d1,@d2)) d=dateadd(minute,-1+row_number() on (order (select null)),@d1) master..spt_values n1,master..spt_values n2 ) n datename(weekday,d) not in ('saturday','sunday') , d not in ( '2016-01-01' -- new year's day ,'2016-01-18' -- martin luther king, jr, ,'2016-02-15' -- washington''s birthday ,'2016-03-25' -- friday ,'2016-05-30' -- memorial day ,'2016-07-04' -- independence day ,'2016-09-05' -- labor day ,'2016-11-24' -- thanksgiving ,'2016-11-25' -- black friday ,'2016-12-26' -- christmas day ) ) end -- goal : 13.370138875 -- select [dbo].[myfunctionsv]('2017-03-13 11:00:02','2017-03-30 19:53:59') 
Comments
Post a Comment