Workday computing ideas and implementation

Currently, tables of one week in the project store all the holidays of the year. The start date and end date of the query are given, and the number of workdays in the query period is calculated. The following assumptions are required to simplify this problem. 1. Do not consider Saturday and Sunday as normal working days. 2. There is no overlap between holidays, and October 1-7 is not the National Day.

Currently, tables of one week in the project store all the holidays of the year. The start date and end date of the query are given, and the number of workdays in the query period is calculated. The following assumptions are required to simplify this problem. 1. Do not consider Saturday and Sunday as normal working days. 2. There is no overlap between holidays, and October 1-7 is not the National Day.

Currently, tables of one week in the project store all the holidays of the year. The start date and end date of the query are given, and the number of workdays in the query period is calculated. The following assumptions are required to simplify this problem.

1. Do not consider Saturday and Sunday as normal working days

2. There is no overlap between holidays, and there will be no national day in October 1-7, with another holiday

The design of the holiday table is given. Each holiday has a start time and end time. Here, only the day of the month is used to represent the holiday of every year.

CREATE TABLE [dbo].[holiday]([begin_time] [varchar](50) NULL,[end_time] [varchar](50) NULL) ON [PRIMARY]GO

Insert test data, for example, holiday on National Day

The specified query period is from.

Declare @ query_begin datetime -- Query start time declare @ query_end datetime -- Query end time declare @ year1 int declare @ year2 intdeclare @ yeartemp intdeclare @ total_holidays intset @ query_begin = '2017-09-01 'set @ query_end = '1970-01-31 'set @ year1 = YEAR (@ query_begin) set @ year2 = YEAR (@ query_end) -- store all holidays containing YEAR, month, and day IF object_id ('tempdb .. # temp ') is not null BEGIN drop table # temp end create table # temp (begin_time date, end_time date,) insert into # tempselect convert (varchar (4), @ year1) + '-' + begin_time, convert (varchar (4), @ year1) + '-' + end_time from holiday -- here we mainly consider querying the cross-year period. set @ yeartemp = @ year1 + 1 while @ yeartemp <= @ year2begin insert into # temp select convert (varchar (4 ), @ yeartemp) + '-' + begin_time, convert (varchar (4), @ yeartemp) + '-' + end_time from holiday set @ yeartemp = @ yeartemp + 1end -- remove the holiday segment that does not have any intersection with the query time range delete from # tempwhere end_time <@ query_begin or begin_time> @ query_endselect @ 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 when 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_holidaysdrop table # temp

Run the script and the expected result is 2. The following are some special test cases to verify whether the script can calculate the work day correctly.

1. the query time is

Result: 1

2. the query time is

Result: 1

3. Add a holiday, for example, Teachers' Day. The query period is.

Result: 30

4. Add a new holiday record. The query period is.

Now the holiday table records are:

If you manually calculate the value: 30 + 31 + 30 + 31 + 31-7-1-1 = 144

Actual result: 144