Thinking and realization of workday calculation problem

There is currently a weekly table in the project that stores all the holidays of the year, given the start and end dates of the query, and how many working days are worked out during the query period. To simplify the problem, the following assumptions are required.

1. Do not consider Saturday Sunday as an ordinary working day

2. The holiday does not overlap the situation, will not appear October 1 to 7th is the National Day, which has another holiday

Given the design of the holiday table, a holiday has a starting time and an end time, and it only takes a month's day to indicate the holiday for each year.

CREATE TABLE [dbo]. [Holiday] (
	[begin_time] [varchar] (null
	) [end_time] [varchar] (null
) on [PRIMARY]

Go

Insert test data, for example, to insert a national day holiday

The given query time period is from 2014-09-30 to 2014-10-08, during which the working day

DECLARE @query_begin datetime     --Query start time declare @query_end datetime        --Query end time DECLARE @year1 int                   DECLARE @year2 int DECLARE @yeartemp int declare @total_holidays int set @query_begi n = ' 2014-09-01 ' Set @query_end = ' 2015-01-31 ' Set @year1 = year (@query_begin) Set @year2 = year (@query_end)--store all the dates that contain the month Vacation IF object_id (' tempdb ... 
#temp ') is not null     begin         drop table #temp     end     create table #temp      (        begin_time date,          end_time date,              ) insert INTO #t EMP Select CONVERT (varchar (4), @year1) + '-' +begin_time, CONVERT (varchar (4), @year1) + '-' +end_time from holiday-- This is mainly to consider the situation of the query time span year set @yeartemp= @year1 +1 while @yeartemp <= @year2 begin     INSERT INTO #temp     select CONVERT (
varchar (4), @yeartemp) + '-' +begin_time, CONVERT (varchar (4), @yeartemp) + '-' +end_time     from holiday     Set @yeartemp = @yeartemp +1 End-Removes a holiday segment that does not have a little intersection with the query time period delete from #temp where end_time< @query_begin o R begin_time> @query_end Select @total_holidays = SUM (DATEDIFF (dd,begin_time,end_time) +1) from (    Select Case when begin_time< @query_begin then @query_begin else Begin_time end as Begin_time,     case W Hen end_time> @query_end then @query_end else End_time end as end_time from #temp) t select DATEDIFF (DD, @query_begin, @query_end) +1-@total_holidays drop table #temp

Executing the script will result in 2, which is in line with expectations. Here are some special test cases to verify that the script calculates the working day correctly.

1. The inquiry time is from 2014-10-05 to 2014-10-08

Results: 1

2. The inquiry time is from 2014-09-30 to 2014-10-07

Results: 1

3. Add a holiday, such as teachers ' day, for a period of 2014-09-01 to 2014-10-08

Results: 30

4. In addition to a holiday record, New Year's Day, query time period of 2014-09-01 to 2015-01-31

Now the record for the holiday table is:

If the manual to calculate is: 30+31+30+31+31-7-1-1=144

Actual results: 144