Calculate the number of working days between two dates

SQL calculates the number of days working between two dates http://blog.163.com/im_foto/blog/static/49085060200953010553914/ -- calculates the number of days working between two dates
Create Function f_workdatediff (
@ Dt_begin datetime,
@ Dt_end datetime)
Returns int
As
Begin
Declare @ workday int, @ I int, @ BZ bit, @ DT datetime
If @ dt_begin> @ dt_end
Select @ Bz = 1, @ dt = @ dt_bsegin, @ dt_begin = @ dt_end, @ dt_end = @ dt
Else
Set @ Bz = 0
Select @ I = datediff (day, @ dt_begin, @ dt_end) + 1,
@ Workday = @ I/7*5,
@ Dt_begin = dateadd (day, @ I/7*7, @ dt_begin)
While @ dt_begin <= @ dt_end
Begin
Select @ workday = case
When (@ datefirst + datepart (weekday, @ dt_begin)-1) % 7 between 1 and 5
Then @ workday + 1 else @ workday end,
@ Dt_begin = @ dt_begin + 1
End
Return (case when @ Bz = 1 then-@ workday else @ workday end)
End
Go

-- Test Data
Create Table Tb (name varchar (10), workdate datetime)
Insert TB select 'A', '2017-01-03'
Union all select 'A', '2017-01-04'
Union all select 'A', '2017-01-05'
Union all select 'A', '2017-01-06'
Union all select 'A', '2017-01-07'
Union all select 'A', '2017-01-10'
Union all select 'A', '2017-01-14'
Union all select 'A', '2017-01-17'
Union all select 'bb', '2017-01-11'
Union all select 'bb', '2017-01-12'
Union all select 'bb', '2017-01-13'
Union all select 'bb', '2017-01-10'
Union all select 'bb', '2017-01-14'
Union all select 'bb', '2017-01-20'
Go

-- Absence statistics
Declare @ dt_begin datetime, @ dt_end datetime
Select @ dt_begin = '2014-1-1-1 ', -- start date of the statistics
@ Dt_end = '2014-1-20 '-- end date of statistics

-- Statistics
Select name, days = sum (days) from (
Select name, days = DBO. f_workdatediff (
Dateadd (day, 1, workdate ),
Isnull (dateadd (day,-1 ,(
Select min (workdate) from TB AA
Where name = A. Name
And workdate> A. workdate and workdate <= @ dt_end
And not exists (
Select * from TB
Where workdate> @ dt_begin and workdate <= @ dt_end
And name = AA. Name
And DBO. f_workdatediff (workdate, AA. workdate) = 2 ))
), @ Dt_end ))
From (
Select name, workdate from TB
Where workdate >=@ dt_begin and workdate <@ dt_end
Union all -- add auxiliary records for each group of numbers to query whether the start number is missing
Select distinct name, dateadd (day,-1, @ dt_begin) from TB
)
Where (@ datefirst + datepart (weekday, workdate)-1) % 7 between 1 and 5
And not exists (
Select * from TB
Where workdate> @ dt_begin and workdate <= @ dt_end
And name = A. Name
And DBO. f_workdatediff (workdate, A. workdate) =-2)
) AA group by name
/* -- Result
Name days
---------------------------
AA 6
Bb 8
--*/