我有一个区间（2013-11-25 04:05:27.000 ---- 2013-12-05 07:06:34.000）
DECLARE @d TABLE(line varchar(3),from_date DATETIME, to_date DATETIME);
INSERT @d VALUES ('G01','20131125 04:05:27','20131205 07:06:34');
我想每天分成几行
我的一天从05:00开始，而不是00:00。
结果应该是：
G01 ---- 2013-11-25 04:05:27.00 ---- 2013-11-25 04:59:59.00
G01 ---- 2013-11-25 05:00:00.00 ---- 2013-11-26 04:59:59.00
G01 ---- 2013-11-26 05:00:00.00 ---- 2013-11-27 04:59:59.00
.
.
.
G01 ---- 2013-12-05 05:00:00.00 ---- 2013-12-05 07:06:34.00
创建函数：
CREATE FUNCTION [dbo].[datestable] (
@DateStart datetime,
@DateEnd datetime
)
RETURNS @datestable TABLE
(
dStart datetime NULL,
dEnd datetime NULL
)
AS
BEGIN
DECLARE @time time = '04:59:59.000',
@s int, @e int, @r int
DECLARE @dates TABLE (
d datetime
)
DECLARE @ddates TABLE (
dstart datetime,
dend datetime,
rn int
)
SELECT @s = CASE WHEN CAST(@DateStart as time) < @time THEN 1 ELSE 0 END ,
@e = CASE WHEN CAST(@DateEnd as time) < @time THEN 1 ELSE 0 END
;WITH cte AS (
SELECT CAST(@DateStart as date) as d
UNION ALL
SELECT CAST(DATEADD(day,1,d) as date)
FROM cte
WHERE d < CAST(@DateEnd as date)
)
INSERT INTO @dates
SELECT d
FROM cte
IF @s != @e AND CAST(@DateStart as date) = CAST(@DateEnd as date)
BEGIN
-- Here we go if dates are equal and start/end time are start<4:59:59 and end>4:59:59
INSERT INTO @datestable
SELECT @DateStart as dstart,
CAST(CONVERT(nvarchar(10),@DateStart,20) + ' 04:59:59.000' as datetime) as dend
UNION ALL
SELECT CAST(CONVERT(nvarchar(10),@DateStart,20) + ' 05:00:00.000' as datetime),
@DateEnd
END
ELSE
BEGIN
IF @s = 1
BEGIN
INSERT INTO @ddates
SELECT d.d as dstart,
d1.d as dend,
ROW_NUMBER() OVER (ORDER BY d.d) as rn
FROM @dates d
LEFT JOIN @dates d1
ON d.d = d1.d
SELECT @r = @@ROWCOUNT
IF @e = 1 and @r > 1
BEGIN
--Here goes if both times < 4:59:59
INSERT INTO @datestable
SELECT CASE WHEN rn = 1 THEN @DateStart ELSE CAST(CONVERT(nvarchar(10),d.dstart,20) + ' 05:00:00.000' as datetime) END as dstart,
CASE WHEN rn = @r-1 THEN @DateEnd ELSE CAST(CONVERT(nvarchar(10),d.dend,20) + ' 04:59:59.000' as datetime) END as dEnd
FROM @ddates d
WHERE rn < @r
END
ELSE
BEGIN
--Here goes if start time < 4:59:59 and end time > 4:59:59
INSERT INTO @datestable
SELECT CASE WHEN rn = 1 THEN @DateStart ELSE CAST(CONVERT(nvarchar(10),d.dstart,20) + ' 05:00:00.000' as datetime) END as dstart,
CASE WHEN rn = @r THEN @DateEnd ELSE CAST(CONVERT(nvarchar(10),d.dend,20) + ' 04:59:59.000' as datetime) END as dEnd
FROM @ddates d
END
END
ELSE
BEGIN
INSERT INTO @ddates
SELECT d.d as dstart,
d1.d as dend,
ROW_NUMBER() OVER (ORDER BY d.d) as rn
FROM @dates d
LEFT JOIN @dates d1
ON d.d = DATEADD(day,-1,d1.d)
SELECT @r = @@ROWCOUNT
IF @e = 1 and @r > 1
BEGIN
--Here we go if start time > 4:59:59 and end time < 4:59:59
INSERT INTO @datestable
SELECT CASE WHEN rn = 1 THEN @DateStart ELSE CAST(CONVERT(nvarchar(10),d.dstart,20) + ' 05:00:00.000' as datetime) END as dstart,
CASE WHEN rn = @r-1 THEN @DateEnd ELSE CAST(CONVERT(nvarchar(10),d.dend,20) + ' 04:59:59.000' as datetime) END as dEnd
FROM @ddates d
WHERE rn < @r
END
ELSE
BEGIN
----Here goes if both times > 4:59:59
INSERT INTO @datestable
SELECT CASE WHEN rn = 1 THEN @DateStart ELSE CAST(CONVERT(nvarchar(10),d.dstart,20) + ' 05:00:00.000' as datetime) END as dstart,
CASE WHEN rn = @r THEN @DateEnd ELSE CAST(CONVERT(nvarchar(10),d.dend,20) + ' 04:59:59.000' as datetime) END as dEnd
FROM @ddates d
END
END
END;
RETURN;
END;
然后：
DECLARE @d TABLE(line nvarchar(3), from_date DATETIME, to_date DATETIME);
INSERT @d VALUES
--Here dates are different (few days)
('G01', '2013-11-25 04:05:27.000','2013-11-28 04:06:34.000'),--date start < 5:00:00 and date end < 5:00:00
('G02', '2012-11-21 14:11:09.220','2012-11-23 14:48:28.733'),--date start > 5:00:00 and date end > 5:00:00
('G03', '2016-05-18 03:00:00.000','2016-05-20 07:00:00.000'),--date start < 5:00:00 and date end > 5:00:00
('G04', '2013-11-25 05:05:27.000','2013-11-27 04:06:34.000'),--date start > 5:00:00 and date end < 5:00:00
--Here dates are equal
('G05', '2013-11-25 04:05:27.000','2013-11-25 04:06:34.000'),--date start < 5:00:00 and date end < 5:00:00
('G06', '2014-01-02 23:24:07.120','2014-01-02 23:25:28.130'),--date start > 5:00:00 and date end > 5:00:00
('G07', '2014-01-02 02:24:07.120','2014-01-02 23:25:28.130'),--date start < 5:00:00 and date end > 5:00:00
--Here dates are different (1 day)
('G08', '2014-01-02 23:24:07.120 ','2014-01-03 00:25:28.130'),--date start > 5:00:00 and date end > 5:00:00
('G09', '2014-01-02 04:24:07.120 ','2014-01-03 00:25:28.130'),--date start < 5:00:00 and date end < 5:00:00
('G10', '2014-01-02 04:24:07.120 ','2014-01-03 06:25:28.130')--date start < 5:00:00 and date end > 5:00:00
SELECT d.line,
dt.dStart,
dt.dEnd
FROM @d d
OUTER APPLY [dbo].[datestable] (d.from_date, d.to_date) as dt
输出：
line dStart dEnd
G01 2013-11-25 04:05:27.000 2013-11-25 04:59:59.000
G01 2013-11-26 05:00:00.000 2013-11-26 04:59:59.000
G01 2013-11-27 05:00:00.000 2013-11-28 04:06:34.000
G02 2012-11-21 14:11:09.220 2012-11-22 04:59:59.000
G02 2012-11-22 05:00:00.000 2012-11-23 04:59:59.000
G02 2012-11-23 05:00:00.000 2012-11-23 14:48:28.733
G03 2016-05-18 03:00:00.000 2016-05-18 04:59:59.000
G03 2016-05-19 05:00:00.000 2016-05-19 04:59:59.000
G03 2016-05-20 05:00:00.000 2016-05-20 07:00:00.000
G04 2013-11-25 05:05:27.000 2013-11-26 04:59:59.000
G04 2013-11-26 05:00:00.000 2013-11-27 04:06:34.000
G05 2013-11-25 04:05:27.000 2013-11-25 04:06:34.000
G06 2014-01-02 23:24:07.120 2014-01-02 23:25:28.130
G07 2014-01-02 02:24:07.120 2014-01-02 04:59:59.000
G07 2014-01-02 05:00:00.000 2014-01-02 23:25:28.130
G08 2014-01-02 23:24:07.120 2014-01-03 00:25:28.130
G09 2014-01-02 04:24:07.120 2014-01-03 00:25:28.130
G10 2014-01-02 04:24:07.120 2014-01-02 04:59:59.000
G10 2014-01-03 05:00:00.000 2014-01-03 06:25:28.130
您可以放入视图的最后一个查询。
编辑：2021-05-12
新的变体是使用带有一些逻辑的cte：
WITH cte AS (
SELECT line,
from_date,
CASE WHEN DATEPART(hour, from_date) < 5 AND CAST(from_date as date) != CAST(to_date as date)
THEN CAST(CONVERT(nvarchar(10),from_date,20) + ' 04:59:59.000' as datetime)
WHEN CAST(from_date as date) = CAST(to_date as date) OR DATEPART(hour, to_date) < 5 AND DATEDIFF(day,from_date,to_date) <=1
THEN to_date
ELSE CAST(CONVERT(nvarchar(10),DATEADD(day,1,from_date),20) + ' 04:59:59.000' as datetime) END as new_to_date,
to_date
FROM #dates
UNION ALL
SELECT line,
CAST(CONVERT(nvarchar(10),new_to_date,20) + ' 05:00:00.000' as datetime),
CASE WHEN CAST(CONVERT(nvarchar(10),DATEADD(day,1,new_to_date),20) + ' 04:59:59.000' as datetime) > to_date
THEN to_date
ELSE CAST(CONVERT(nvarchar(10),DATEADD(day,1,new_to_date),20) + ' 04:59:59.000' as datetime) END,
to_date
FROM cte
WHERE to_date > new_to_date
)
SELECT line,
from_date,
new_to_date as to_date
FROM cte
ORDER BY line, from_date
输出为：
line from_date to_date
G01 2013-11-25 04:05:27.000 2013-11-25 04:59:59.000
G01 2013-11-25 05:00:00.000 2013-11-26 04:59:59.000
G01 2013-11-26 05:00:00.000 2013-11-27 04:59:59.000
G01 2013-11-27 05:00:00.000 2013-11-28 04:06:34.000
G02 2012-11-21 14:11:09.220 2012-11-22 04:59:59.000
G02 2012-11-22 05:00:00.000 2012-11-23 04:59:59.000
G02 2012-11-23 05:00:00.000 2012-11-23 14:48:28.733
G03 2016-05-18 03:00:00.000 2016-05-18 04:59:59.000
G03 2016-05-18 05:00:00.000 2016-05-19 04:59:59.000
G03 2016-05-19 05:00:00.000 2016-05-20 04:59:59.000
G03 2016-05-20 05:00:00.000 2016-05-20 07:00:00.000
G04 2013-11-25 05:05:27.000 2013-11-26 04:59:59.000
G04 2013-11-26 05:00:00.000 2013-11-27 04:06:34.000
G05 2013-11-25 04:05:27.000 2013-11-25 04:06:34.000
G06 2014-01-02 23:24:07.120 2014-01-02 23:25:28.130
G07 2014-01-02 02:24:07.120 2014-01-02 23:25:28.130
G08 2014-01-02 23:24:07.120 2014-01-03 00:25:28.130
G09 2014-01-02 04:24:07.120 2014-01-02 04:59:59.000
G09 2014-01-02 05:00:00.000 2014-01-03 00:25:28.130
G10 2014-01-02 04:24:07.120 2014-01-02 04:59:59.000
G10 2014-01-02 05:00:00.000 2014-01-03 04:59:59.000
G10 2014-01-03 05:00:00.000 2014-01-03 06:25:28.130
我希望这个例子能对您有所帮助。
declare @start datetime='2013-11-25 04:05:27.000'
,@dateend datetime = '2013-12-05 07:06:34.000' --set range
;with tm as ( -- generate and populate table
select @start dt, 1 evt
union all
select dateadd(minute,67,tm.dt), evt+1
from tm where dt <=@dateend
)
,shifted as ( -- shift and combine
select ROW_NUMBER() over(partition by cast(dateadd(hour,-5,tm.dt) as char(10)) order by tm.dt) rn,
convert(char(10), dateadd(hour,-5,tm.dt),101 ) s,
tm.*
from tm
)
select * from shifted
order by dt
option (maxrecursion 1000)--required because tm has >100 rows
一些结果。请注意，下一个范围从5:00开始。
rn s dt evt
1 11/24/2013 2013-11-25 04:05:27.000 1
1 11/25/2013 2013-11-25 05:12:27.000 2
2 11/25/2013 2013-11-25 06:19:27.000 3
3 11/25/2013 2013-11-25 07:26:27.000 4
4 11/25/2013 2013-11-25 08:33:27.000 5
5 11/25/2013 2013-11-25 09:40:27.000 6
6 11/25/2013 2013-11-25 10:47:27.000 7
7 11/25/2013 2013-11-25 11:54:27.000 8
8 11/25/2013 2013-11-25 13:01:27.000 9
9 11/25/2013 2013-11-25 14:08:27.000 10
10 11/25/2013 2013-11-25 15:15:27.000 11
11 11/25/2013 2013-11-25 16:22:27.000 12
12 11/25/2013 2013-11-25 17:29:27.000 13
13 11/25/2013 2013-11-25 18:36:27.000 14
14 11/25/2013 2013-11-25 19:43:27.000 15
15 11/25/2013 2013-11-25 20:50:27.000 16
16 11/25/2013 2013-11-25 21:57:27.000 17
17 11/25/2013 2013-11-25 23:04:27.000 18
18 11/25/2013 2013-11-26 00:11:27.000 19
19 11/25/2013 2013-11-26 01:18:27.000 20
20 11/25/2013 2013-11-26 02:25:27.000 21
21 11/25/2013 2013-11-26 03:32:27.000 22
22 11/25/2013 2013-11-26 04:39:27.000 23
1 11/26/2013 2013-11-26 05:46:27.000 24
2 11/26/2013 2013-11-26 06:53:27.000 25
参考Iballan对gofr1的回答的评论，即在某些情况下记录会丢失。
发生这种情况是因为CTE只创建足够的行来满足每个日期1行的要求，但在某些情况下，单个日期应该有多个行。例如，如果一个输入范围在截止时间部分之前开始，那么它应该有两条该日期的记录。
我编写了另一个函数来实现这一点。
DROP FUNCTION IF EXISTS [dbo].[datestable]
GO
CREATE FUNCTION [dbo].[datestable] (
@DateStart datetime2(3),
@DateEnd datetime2(3)
)
RETURNS @DatesTable TABLE
(
dStart datetime2(3) NULL,
dEnd datetime2(3) NULL
)
AS
BEGIN
DECLARE @EndTimeCutoff time(3) = '04:59:59.999'
DECLARE @StartTimeCutoff time(3) = dateadd(ms,1,@EndTimeCutoff)
DECLARE @EndTimeCutoffChar char(12) = convert(char(12),@EndTimeCutoff,14)
,@StartTimeCutoffChar char(12) = convert(char(12),@StartTimeCutoff,14)
IF @DateEnd>=@DateStart -- if a period is required for a stop that starts and ends at the same time use ">=" - if not then use ">"
BEGIN
DECLARE @EndTime datetime2(3) -- endtime for iterating periods
,@StartTime datetime2(3) -- starttime for iterating periods
,@TmpEndTime datetime2(3)
,@FullDays integer
SELECT @StartTime=@DateStart, @EndTime=@DateEnd
-- create the first interval from time part of @DateStart until next @endtimecutoff
IF cast(@DateStart as time(3))<=@EndTimeCutoff -- the first period is starting before the cutoff so the first period must end either at cutoff or end - whichever comes first
BEGIN
SELECT @TmpEndTime = convert(char(8),@StartTime,112) + ' '+@EndTimeCutoffChar -- endtime for the first day
IF @DateEnd>@TmpEndTime -- period ends after cutoff of first day - create 1 period from start to the cutoff of first day
SELECT @EndTime=@TmpEndTime
ELSE
SELECT @EndTime=@DateEnd
END
ELSE -- first period starts after the cutoff - create 1 period from start to the first ocurring time of either dateend or the next day's cutoff
BEGIN
SELECT @TmpEndTime = convert(char(8),dateadd(day,1,@StartTime),112) + ' '+@EndTimeCutoffChar -- cutoff endtime for the next day
IF @DateEnd>@TmpEndTime -- period ends after cutoff of second day - create 1 period from start to the cutoff of second day
SELECT @EndTime=@TmpEndTime
ELSE
SELECT @EndTime=@DateEnd
END
INSERT INTO @DatesTable (dStart,dEnd) values (@StartTime,@EndTime) -- insertion of first period range
SELECT @FullDays = floor(datediff_big(ms,@EndTime,@DateEnd)/(24*60*60*1000)) -- full days remaining
, @StartTime = convert(CHAR(8),@EndTime,112)+' '+@StartTimeCutoffChar -- set new starttime
IF @FullDays>0 -- add the full day periods from @starttimecutoff to @endtimecutoff
BEGIN
WITH CTE AS (SELECT 0 AS "rn" UNION ALL SELECT rn+1 FROM CTE WHERE rn<@FullDays-1)
INSERT INTO @DatesTable(dStart,dEnd)
SELECT dateadd(day,rn,@StartTime), dateadd(DAY,rn+1,@EndTime) FROM CTE
END
SELECT @EndTime=(SELECT MAX(dEND) FROM @DatesTable)
SELECT @StartTime=convert(char(8),@EndTime,112)+' '+@StartTimeCutoffChar
IF @StartTime<=@DateEnd AND @DateEnd<>@DateStart -- create period for the remaining time (part of day) of the period
-- period is already created above for the case where @DateEnd=@DateStart
-- if a period is required for a stop that starts and ends at the same time on the "@starttimecutoff" exactly then use "<="
-- if not then use "<"
INSERT INTO @DatesTable(dStart,dEnd) VALUES (@StartTime,@DateEnd)
END;
RETURN;
END
向表中添加一些数据点，以从gofr1上面的答案中检索数据。
DECLARE @d TABLE(line nvarchar(3), from_date DATETIME, to_date DATETIME, stopid int identity(1,1));
INSERT @d VALUES
--Here dates are different (few days)
('G01', '2013-11-25 04:05:27.000','2013-11-28 04:06:34.000'),--date start < 5:00:00 and date end < 5:00:00
('G02', '2012-11-21 14:11:09.220','2012-11-23 14:48:28.733'),--date start > 5:00:00 and date end > 5:00:00
('G03', '2016-05-18 03:00:00.000','2016-05-20 07:00:00.000'),--date start < 5:00:00 and date end > 5:00:00
('G04', '2013-11-25 05:05:27.000','2013-11-27 04:06:34.000'),--date start > 5:00:00 and date end < 5:00:00
--Here dates are equal
('G05', '2013-11-25 04:05:27.000','2013-11-25 04:06:34.000'),--date start < 5:00:00 and date end < 5:00:00
('G06', '2014-01-02 23:24:07.120','2014-01-02 23:25:28.130'),--date start > 5:00:00 and date end > 5:00:00
('G07', '2014-01-02 02:24:07.120','2014-01-02 23:25:28.130'),--date start < 5:00:00 and date end > 5:00:00
--Here dates are different (1 day)
('G08', '2014-01-02 23:24:07.120','2014-01-03 00:25:28.130'),--date start > 5:00:00 and date end > 5:00:00
('G09', '2014-01-02 04:24:07.120','2014-01-03 00:25:28.130'),--date start < 5:00:00 and date end < 5:00:00
-- Here dates are exactly on datetime cutoffs
('G10', '2020-01-02 04:59:59.999','2020-01-03 04:59:59.999'),--date start = cutoff and date end = cutoff
('G10', '2020-01-05 05:00:00.000','2020-01-05 05:00:00.000')--date start = cutoff start = date end
SELECT d.line,
d.stopid,
row_number() over (partition by d.stopid order by dt.dstart asc) as "stopid_split",
d.from_date as "stop_from_date",
d.to_date as "stop_end_date",
dt.dStart as "split_start_date",
dt.dEnd as "split_end_date"
FROM @d d
OUTER APPLY [dbo].[datestable] (d.from_date, d.to_date) as dt
输出在这里（我为格式道歉——我的技能不够好）。
line stopid stopid_split stop_from_date stop_end_date split_start_date split_end_date
G01 1 1 2013-11-25 04:05:27.000 2013-11-28 04:06:34.000 2013-11-25 04:05:27.000 2013-11-25 04:59:59.999
G01 1 2 2013-11-25 04:05:27.000 2013-11-28 04:06:34.000 2013-11-25 05:00:00.000 2013-11-26 04:59:59.999
G01 1 3 2013-11-25 04:05:27.000 2013-11-28 04:06:34.000 2013-11-26 05:00:00.000 2013-11-27 04:59:59.999
G01 1 4 2013-11-25 04:05:27.000 2013-11-28 04:06:34.000 2013-11-27 05:00:00.000 2013-11-28 04:06:34.000
G02 2 1 2012-11-21 14:11:09.220 2012-11-23 14:48:28.733 2012-11-21 14:11:09.220 2012-11-22 04:59:59.999
G02 2 2 2012-11-21 14:11:09.220 2012-11-23 14:48:28.733 2012-11-22 05:00:00.000 2012-11-23 04:59:59.999
G02 2 3 2012-11-21 14:11:09.220 2012-11-23 14:48:28.733 2012-11-23 05:00:00.000 2012-11-23 14:48:28.733
G03 3 1 2016-05-18 03:00:00.000 2016-05-20 07:00:00.000 2016-05-18 03:00:00.000 2016-05-18 04:59:59.999
G03 3 2 2016-05-18 03:00:00.000 2016-05-20 07:00:00.000 2016-05-18 05:00:00.000 2016-05-19 04:59:59.999
G03 3 3 2016-05-18 03:00:00.000 2016-05-20 07:00:00.000 2016-05-19 05:00:00.000 2016-05-20 04:59:59.999
G03 3 4 2016-05-18 03:00:00.000 2016-05-20 07:00:00.000 2016-05-20 05:00:00.000 2016-05-20 07:00:00.000
G04 4 1 2013-11-25 05:05:27.000 2013-11-27 04:06:34.000 2013-11-25 05:05:27.000 2013-11-26 04:59:59.999
G04 4 2 2013-11-25 05:05:27.000 2013-11-27 04:06:34.000 2013-11-26 05:00:00.000 2013-11-27 04:06:34.000
G05 5 1 2013-11-25 04:05:27.000 2013-11-25 04:06:34.000 2013-11-25 04:05:27.000 2013-11-25 04:06:34.000
G06 6 1 2014-01-02 23:24:07.120 2014-01-02 23:25:28.130 2014-01-02 05:00:00.000 2014-01-02 23:25:28.130
G06 6 2 2014-01-02 23:24:07.120 2014-01-02 23:25:28.130 2014-01-02 23:24:07.120 2014-01-02 23:25:28.130
G07 7 1 2014-01-02 02:24:07.120 2014-01-02 23:25:28.130 2014-01-02 02:24:07.120 2014-01-02 04:59:59.999
G07 7 2 2014-01-02 02:24:07.120 2014-01-02 23:25:28.130 2014-01-02 05:00:00.000 2014-01-02 23:25:28.130
G08 8 1 2014-01-02 23:24:07.120 2014-01-03 00:25:28.130 2014-01-02 23:24:07.120 2014-01-03 00:25:28.130
G09 9 1 2014-01-02 04:24:07.120 2014-01-03 00:25:28.130 2014-01-02 04:24:07.120 2014-01-02 04:59:59.999
G09 9 2 2014-01-02 04:24:07.120 2014-01-03 00:25:28.130 2014-01-02 05:00:00.000 2014-01-03 00:25:28.130
G10 10 1 2020-01-02 05:00:00.000 2020-01-03 05:00:00.000 2020-01-02 05:00:00.000 2020-01-03 04:59:59.999
G10 10 2 2020-01-02 05:00:00.000 2020-01-03 05:00:00.000 2020-01-03 05:00:00.000 2020-01-03 05:00:00.000
G10 11 1 2020-01-05 05:00:00.000 2020-01-05 05:00:00.000 2020-01-05 05:00:00.000 2020-01-05 05:00:00.000