我在 T-SQL 中有这个存储过程,用于将DATETIME
拆分为班次。
我会将DATETIME START
和DATETIME END
分成班次(例如,一小时的一个班次或每 15 分钟一个班次)。我应该将其转换为临时表以在另一个查询中使用它。
因此,为此,我创建了这个循环:
BEGIN
SET @DATASTART = '2014-11-28 06:00:00'
SET @DATAEND = '2014-11-28 21:00:00'
--DICHIARO DUE VARIABILI DATA CHE UTILIZZO
--PER MANTENERE I DUE SHIFT
DECLARE @DataFirstShift as DATETIME
DECLARE @DataLastShift as DATETIME
--DICHIARO UN CONTATORE PER POPOLARE IL CAMPO ID
DECLARE @Contatore as INT
SET @Contatore = 0
--SETTO LA DATA FIRSTSHIFT A DATASTART
SET @DataFirstShift = @DATA_START
WHILE(@DataFirstShift <= @DATA_END)
BEGIN
--POPOLO LA DATA LAST CON UN ORA IN PIU RISPETTO ALLA PRIMA DATA
IF @Shift LIKE 'All'
BEGIN
SET @DataLastShift = DATEADD(HOUR,1,@DataFirstShift)
END
ELSE
BEGIN
SET @DataLastShift = DATEADD(MINUTE,15,@DataFirstShift)
END
INSERT INTO @TemporaryTable2 (ID,DATASTART,DATAEND)
VALUES (@Contatore,@DataFirstShift,@DataLastShift)
SET @DataFirstShift=@DataLastShift
--INCREMENTO IL CONTATORE
SET @Contatore+=1
END
END
这种方法有效,但我这个周期很慢。我想知道是否存在比它更快的方法。
有人可以帮助我吗?问候
试试下面:
--param of SP
DECLARE @DATASTART DATETIME = '2014-11-28 06:00:00'
DECLARE @DATAEND DATETIME = '2014-11-28 21:00:00'
DECLARE @Shift VARCHAR(50) = 'All'
--body
DECLARE @TemporaryTable2 TABLE
(
id INT,
startdate DATETIME,
enddate DATETIME
)
DECLARE @id INT = 0
IF @Shift LIKE 'All'
BEGIN
INSERT INTO @TemporaryTable2
SELECT N.number + 1 AS id,
startdate,
enddate
FROM master..spt_values N
CROSS apply (SELECT Dateadd(dd, N.number, @DATASTART),
Dateadd(dd, N.number + 1, @DATASTART)) AS D(startdate, enddate)
WHERE N.number BETWEEN 0 AND Datediff(dd, @DATASTART, @DATAEND)
AND N.type = 'P'
END
ELSE
BEGIN
INSERT INTO @TemporaryTable2
SELECT N.number + 1 AS id,
startdate,
enddate
FROM master..spt_values N
CROSS apply (SELECT Dateadd(MINUTE, N.number * 15, @DATASTART),
Dateadd(MINUTE, ( N.number + 1 ) * 15, @DATASTART)) AS D(startdate, enddate)
WHERE N.number BETWEEN 0 AND ( Datediff(MINUTE, @DATASTART, @DATAEND) / 15 )
AND N.type = 'P'
END
SELECT *
FROM @TemporaryTable2
这里也存在类似的问题。此方法的唯一限制是spt_values
type = 'p'
的范围从 0 到 2047 。因此,它最多可以返回 2047 行。