--自动补全时间（number的范围 0~2047）
SELECT CONVERT (VARCHAR (10),dateadd(d, number, GETDATE()),23) AS rq
FROM master..spt_values
WHERE type = 'p' AND number between 1 and 365
--按照时间段划分成每个月的时间段
declare @Init_Data table
(
ID INT IDENTITY(1,1) NOT NULL,
TypeID bigint NULL,
dtEnd datetime NULL,
dtStart datetime NULL
);
insert into @Init_Data(TypeID,dtStart,dtEnd)
select 1,'2020-01-04 02:00:00','2020-04-24 22:05:00'
union all
select 2,'2020-03-04 01:00:00','2020-04-21 22:25:00'
declare @TEMP_Data table
(
ID INT IDENTITY(1,1) NOT NULL,
TypeID bigint NULL,
BeforeDate datetime NULL,
AfterDate datetime NULL
);
with T
as
(
select A.TypeID,A.dtStart,isnull(A.dtend,getdate())dtEnd ,A.dtStart AS beforeStart,
(case when A.dtEnd is null then dateadd(month,1,convert(char(07),LEFT(CONVERT(varchar(19),A.dtStart,120),7),120)+'-01')
when A.dtEnd is not null and MONTH(A.dtStart)=MONTH(A.dtEnd) and Year(A.dtStart)=Year(A.dtEnd) then A.dtEnd
else dateadd(month,1,convert(char(07),LEFT(CONVERT(varchar(19),A.dtStart,120),7),120)+'-01') end) as afterDtAudit,
MONTH(isnull(A.dtEnd,getdate()))-MONTH(A.dtStart)+12*(Year(isnull(A.dtEnd,getdate()))-Year(A.dtStart)) AS MonthS
from @Init_Data A
union all
select T.TypeID,T.dtStart, T.dtend,T.afterDtAudit AS beforeStart,
(case when MonthS=1 then T.dtend else dateadd(month,1,T.afterDtAudit) end) AS afterDtAudit,MonthS-1 AS MonthS
from T where MonthS>0 and Months is not null
)
insert into @TEMP_Data(TypeID,BeforeDate,AfterDate)
select TypeID, beforeStart, min(afterDtAudit)afterDtAudit from(
select TypeID, min(T.beforeStart) beforeStart,T.afterDtAudit
from T
group by TypeID,T.afterDtAudit)a
group by TypeID,beforeStart
select * from @TEMP_Data order by TypeID,BeforeDate ;