How does a continuous time segment split in SQL Server?

Today, a very practical problem has been encountered in the work, the customer in the OA interface employee leave in the intermediate table provides a continuous period of leave records, for example: Zhang San, 2018-12-1 ~2018-12-31, sick leave, 31 days. The problem with this is that if I need to count the number of days Zhang San from December 1 to December 15, the entire continuous record is not counted. At this point, we need to split a long record.

Here is a note of my own ideas:

1: Use the system table to get 0-2047 of the sequence, 2048*2048 absolutely enough.

SELECT sv.  Number  as  from  as WHERE sv. [type] = ' P '

Results

2: Use Cross APPLY to get final sequence (starting from 1, last line is 2048*2048)

 withT1 as(SELECTSv. Number  asN fromMASTER.dbo.spt_values asSvWHERESv.[type]='P'), T2 as(SELECTRow_number () Over(ORDER  by(SELECT 1)) asRid fromT1 asA CrossAPPLY T1 asb)----------------- SELECT * fromT2

Results

You must not tell me that more than 4 million is not enough for you to dismantle, be careful I will beat you

3: Full Code

DECLARE @begindateDATE=CAST('2017-12-1'  asdate)DECLARE @enddateDATE=CAST('2017-12-31'  asdate)BEGIN  withT1 as(SELECTSv. Number  asN fromMASTER.dbo.spt_values asSvWHERESv.[type]='P'), T2 as(SELECTRow_number () Over(ORDER  by(SELECT 1)) asRid fromT1 asA CrossAPPLY T1 asb)----------------- --SELECT *from T2SELECT DATEADD( Day, T2.rid-1,@begindate) asDate fromT2WHERET2.ridbetween -1  and(DATEDIFF( Day,@begindate,@enddate)+1)END 

PS: Since the sequence starts with 1, I need to start with the DateAdd function from 0 days (as the first day), so note the effect of the above code-1 and +1.

Effect

At last:

About the WITH keyword, row_nunber () over () the window function and cross apply please do your own brain study to avoid smattering

How does a continuous time segment split in SQL Server?