Need to update rows from a stored procedure via 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

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 

回答1:

First, some set up:

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;
*/

Now, the query:

;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

With an actual insert, e.g.

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

With an INTO clause, e.g.

INTO dbo.MyNewTable FROM x INNER JOIN mx

Don't forget to clean up:

DROP TABLE dbo.Outages;