需要通过临时表中的 ID 更新存储过程中的行 [英] Need to update rows from a stored procedure via IDs in a temp table
本文介绍了需要通过临时表中的 ID 更新存储过程中的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我试图将描述中断的表中的行取出，并通过计算的时间范围将它们分成单独的行，然后插入到另一个表中，以描述每个给定范围内的逐小时时间段.
I'm trying to take rows in a table describing outages and break them, via a calculated time range, into separate rows to be inserted into another table to describe hour by hour time periods in each given range.
以下代码为我们提供了以下输出:
The below code gives us the following output:
我们从这一行开始(原始数据):
We went from this row (original data):
OutageDate StartTime EndTime Duration
2010-11-10 16:00:00.0000000 17:30:00.0000000 90
在我运行存储过程之后(这是所需的输出！我只需要知道如何将其保存到表中):
To this after I run the stored proc (THIS IS THE DESIRED OUTPUT! I just need to know how to save it to a table):
OutageDate StartHour StartMinutes EndHour EndMinutes StartTime EndTime Duration
2010-11-10 16 0 17 0 16:00:00.0000000 17:30:00.0000000 90
2010-11-10 17 30 18 0 16:00:00.0000000 17:30:00.0000000 90
以下是我需要弄清楚如何在我将行拆分为我想要的时间值后保存到表中的代码.我无法控制为什么会发生这种情况，它只需要按照比我高的人的要求并由他们以这种格式指定:
The following is the code i need to figure out how to save to a table once i split my rows out across the time values I want. I don't have control over why this has to happen, it just needs to as per a request by someone higher than me and specified by them in this format:
--First, let's look at the original data in the table...just one row will do
SELECT TOP (1) *
FROM actualTable
ORDER BY OutageDate ASC
--Begin sproc logic
declare @OutageDate date
declare @StartTime time(7)
declare @EndTime time(7)
declare @StartHour bigint
declare @EndHour int
declare @StartMinute int
declare @EndMinute int
declare @Duration int
declare @Temp_StartTime time
declare @Temp_EndTime time
declare @temp_StartHour int
declare @temp_EndHour int
declare @temp_StartMinute int
declare @temp_EndMinute int
SELECT TOP(1) @OutageDate = OutageDate, @StartTime = StartTime, @EndTime = EndTime, @Duration = Duration FROM actualTable
SET @Temp_StartTime=@StartTime
SET @Temp_EndTime=@EndTime
SET @temp_StartHour=DATEPART(HOUR, @StartTime)
SET @temp_EndHour=DATEPART(HOUR, @EndTime)
SET @temp_StartMinute=DATEPART(MI, @StartTime)
SET @temp_EndMinute=DATEPART(MI, @EndTime)
PRINT @temp_StartHour
PRINT @temp_EndHour
PRINT @temp_StartMinute
PRINT @StartTime
PRINT @EndTime
if(@temp_EndMinute>0)
BEGIN
SET @temp_EndHour=@temp_EndHour+1
END
--this declares the temp table
DECLARE @Temp_Table TABLE
(
OutageDate date,
StartHour int,
StartMinute int,
EndHour int,
EndMinute int,
StartTime time,
EndTime time,
Duration int
)
--Here's the loop that inserts the rows
While((@temp_EndHour-@temp_StartHour>1))
BEGIN
INSERT INTO @Temp_Table
SELECT
@OutageDate AS OutageDate,
(DATEPART(HOUR, @Temp_StartTime)) AS StartHour,
(DATEPART(MINUTE, @Temp_StartTime)) AS StartMinute,
@temp_StartHour+1 AS EndHour,
0 AS EndMinute,
@StartTime as StartTime,
@EndTime as EndTime,
@Duration AS Duration
--DATEADD returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.
SET @temp_StartHour=@temp_StartHour+1
SET @Temp_StartTime=DATEADD(HOUR,1,@Temp_StartTime)
--Let's make sure we account for the minutes in the first and last hours if any
if(DATEPART(MI, @Temp_StartTime)!=0)
BEGIN
SET @Temp_StartTime=DATEADD(MI,-@Temp_StartMinute,@Temp_StartTime)
END
END
--Ok, if we're at the last row insertion, we still need the minutes the outage finished at...those go into StartMinutes
WHile((@temp_EndHour-@temp_StartHour=1))
BEGIN
INSERT INTO @Temp_Table
SELECT
@OutageDate AS OutageDate,
(DATEPART(HOUR, @Temp_StartTime)) AS StartHour,
@temp_EndMinute AS StartMinute,
@temp_StartHour+1 AS EndHour,
0 AS EndMinute,
@StartTime as StartTime,
@EndTime as EndTime,
@Duration AS Duration
--DATEADD returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.
SET @temp_StartHour=@temp_StartHour+1
SET @Temp_StartTime=DATEADD(HOUR,1,@Temp_StartTime)
--Let's make sure we account for the minutes in the first and last hours if any
if(DATEPART(MI, @Temp_StartTime)!=0)
BEGIN
SET @Temp_StartTime=DATEADD(MI,-@temp_StartMinute,@Temp_StartTime)
END
END
--Need to add logic that drops and recreates the table from the temp table so we don't have to employ a cursor
SELECT * FROM @Temp_Table
BEGIN
SELECT * INTO newTable FROM @Temp_Table
END
如果您直接在 SMS 中执行此操作，您将获得最基本的逻辑:
IF YOU RUN THIS STRAIGHT IN SMS, You'll get the logic at its most basic:
declare @StartTime time
declare @EndTime time
declare @Temp_StartTime time
declare @temp_StartHour int
declare @temp_EndHour int
declare @temp_StartMinute int
declare @temp_EndMinute int
SET @StartTime='2:30:00'
SET @EndTime='4:01:00'
SET @Temp_StartTime=@StartTime
SET @temp_StartHour=DATEPART(HOUR, @StartTime)
SET @temp_EndHour=DATEPART(HOUR, @EndTime)
SET @temp_StartMinute=DATEPART(MI, @StartTime)
SET @temp_EndMinute=DATEPART(MI, @EndTime)
if(@temp_EndMinute>0)
BEGIN
SET @temp_EndHour=@temp_EndHour+1
END
DECLARE @Temp_Table TABLE
(
StartHour int,
StartMinute int,
EndHour int,
EndMinute int,
StartTime time,
EndTime time
)
WHile((@temp_EndHour-@temp_StartHour>=1))
BEGIN
INSERT INTO @Temp_Table
SELECT (DATEPART(HOUR, @Temp_StartTime)) AS StartHour,(DATEPART(MINUTE, @Temp_StartTime)) AS StartMinute,
@temp_StartHour+1 AS EndHour,
0 AS EndMinute, @StartTime as StartTime, @EndTime as EndTime
SET @temp_StartHour=@temp_StartHour+1
SET @Temp_StartTime=DATEADD(HOUR,1,@Temp_StartTime)
if(DATEPART(MI, @Temp_StartTime)!=0)
BEGIN
SET @Temp_StartTime=DATEADD(MI,-@temp_StartMinute,@Temp_StartTime)
END
END
SELECT * FROM @Temp_Table
推荐答案
首先，一些设置:
USE tempdb;
GO
CREATE TABLE dbo.Outages
(
OutageDate DATE,
StartTime TIME(7),
EndTime TIME(7),
Duration INT
);
INSERT dbo.Outages SELECT '20101110', '16:00', '17:30', 90;
/*
-- I also tested these cases, and *think* it still produces what you expect:
INSERT dbo.Outages SELECT '20101111', '13:00', '14:02', 62;
INSERT dbo.Outages SELECT '20101112', '17:00', '18:00', 60;
INSERT dbo.Outages SELECT '20101113', '16:05', '16:25', 20;
INSERT dbo.Outages SELECT '20101114', '16:59', '18:01', 62;
INSERT dbo.Outages SELECT '20101115', '22:15', '01:30', 165;
*/
现在，查询:
;WITH n(n) AS
(
SELECT TOP 24 ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.objects
),
x AS
(
SELECT
o.OutageDate, StartHour = (DATEPART(HOUR, StartTime) + n.n - 1) % 24,
StartTime, EndTime, Duration,
rn = ROW_NUMBER() OVER (PARTITION BY o.OutageDate, o.StartTime ORDER BY n.n)
FROM n INNER JOIN dbo.Outages AS o
ON n.n <= CEILING(DATEDIFF(MINUTE, CONVERT(DATETIME, StartTime),
DATEADD(DAY, CASE WHEN EndTime < StartTime THEN 1 ELSE 0 END,
CONVERT(DATETIME, EndTime)))/60.0)
),
mx AS (SELECT OutageDate, StartTime, minrn = MIN(rn), maxrn = MAX(rn)
FROM x GROUP BY OutageDate, StartTime)
-- insert into some other table
SELECT
x.OutageDate,
x.StartHour,
StartMinutes = CASE
WHEN x.rn = mx.minrn THEN DATEPART(MINUTE, x.StartTime) ELSE 0 END,
EndHour = x.StartHour + 1,
EndMinutes = CASE
WHEN x.rn = mx.maxrn THEN DATEPART(MINUTE, x.EndTime) ELSE 0 END,
x.StartTime,
x.EndTime,
x.Duration
FROM x INNER JOIN mx
ON x.OutageDate = mx.OutageDate
AND x.StartTime = mx.StartTime
ORDER BY x.OutageDate, x.rn;
GO
当您对它为各种场景提供正确的行感到高兴时，请替换
When you're happy that it is giving you the right rows for the various scenarios, then replace
-- insert into some other table
使用实际插入，例如
INSERT dbo.OtherTable(col1, col2, ...)
如果您尝试从此输出创建一个全新的表，请替换
If you're trying to create a brand new table from this output, then replace
FROM x INNER JOIN mx
带有 INTO 子句，例如
With an INTO clause, e.g.
INTO dbo.MyNewTable FROM x INNER JOIN mx
别忘了清理:
DROP TABLE dbo.Outages;
这篇关于需要通过临时表中的 ID 更新存储过程中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
