文章/答案/技术大牛

发布

社区首页 >问答首页 >Server 2008 - StartTime、EndTime在选定点上的分割时间间隔

问Server 2008 - StartTime、EndTime在选定点上的分割时间间隔
EN

Stack Overflow用户

提问于 2014-11-26 15:46:41

回答 3查看 682关注 0票数 1

在Server 2008中，我有一个表，它以列StartTime、EndTime的形式包含时间间隔。该表还包含一些其他标识符。该表的前3行如下所示：

create table #ExampleTable (ID int, PRDATE date, RID int, StartTime DateTime, EndTime DateTime); 
INSERT INTO #ExampleTable(ID, PRDATE, RID, StartTime, EndTime) VALUES  
(10000422,'2014-09-01',6,'2014-09-01 04:08:15.000','2014-09-01 04:13:13.000'),
(10000457,'2014-09-01',103,'2014-09-01 04:08:23.000','2014-09-01 09:44:47.000'),
(20000432,'2014-09-01',116,'2014-09-01 04:08:51.000','2014-09-01 05:07:38.000');

我想在其他表#SplitPoint中给出的SplitPoints中分割间隔，

create table #SplitPoints (SplitPoint DateTime); 
INSERT INTO #SplitPoints(SplitPoint) VALUES 
('2014-09-01 04:15:00.000'),
('2014-09-01 04:45:00.000'),
('2014-09-01 05:30:00.000');

适当时创建新行。所需的结果如下

create table #DesiredResult (ID int, PRDATE date, RID int, StartTime DateTime, EndTime DateTime); 
INSERT INTO #DesiredResult(ID, PRDATE, RID, StartTime, EndTime) VALUES 
(10000422,'2014-09-01',6,'2014-09-01 04:08:15.000','2014-09-01 04:13:13.000'),
(10000457,'2014-09-01',103,'2014-09-01 04:08:23.000','2014-09-01 04:15:00.000'),
(10000457,'2014-09-01',103,'2014-09-01 04:15:00.000','2014-09-01 04:45:00.000'),
(10000457,'2014-09-01',103,'2014-09-01 04:45:00.000','2014-09-01 05:30:00.000'),
(10000457,'2014-09-01',103,'2014-09-01 05:30:00.000','2014-09-01 09:44:47.000'),
(20000432,'2014-09-01',116,'2014-09-01 04:08:51.000','2014-09-01 04:15:00.000'),
(20000432,'2014-09-01',116,'2014-09-01 04:15:00.000','2014-09-01 04:45:00.000'),
(20000432,'2014-09-01',116,'2014-09-01 04:45:00.000','2014-09-01 05:07:38.000');

我怎么能忍受这一切？

datetime

sql-server

sql-server-2008

回答 3

Stack Overflow用户

回答已采纳

发布于 2014-11-26 20:16:58

让我们来制作示例数据：

DECLARE @ExampleTable TABLE (ID int, PRDATE date, RID int, StartTime DateTime, EndTime DateTime); 
INSERT INTO @ExampleTable(ID, PRDATE, RID, StartTime, EndTime) VALUES  
(10000422,'2014-09-01',6,'2014-09-01 04:08:15.000','2014-09-01 04:13:13.000'),
(10000457,'2014-09-01',103,'2014-09-01 04:08:23.000','2014-09-01 09:44:47.000'),
(20000432,'2014-09-01',116,'2014-09-01 04:08:51.000','2014-09-01 05:07:38.000'); 

DECLARE @SplitPoints TABLE (SplitPoint DateTime); 
INSERT INTO @SplitPoints(SplitPoint) VALUES 
('2014-09-01 04:15:00.000'),
('2014-09-01 04:45:00.000'),
('2014-09-01 05:30:00.000');

现在，我创建了一个CTE来获取所有唯一的日期(开始日期和结束日期)来订购它们：

;WITH MyDates AS
(
SELECT ID, PRDATE, RID, StartTime
FROM @ExampleTable
UNION
SELECT ID, PRDATE, RID, EndTime
FROM @ExampleTable
UNION
SELECT et.ID, et.PRDATE, et.RID, sp.SplitPoint
FROM @ExampleTable et 
INNER JOIN @SplitPoints sp
    ON sp.SplitPoint >= et.StartTime AND sp.SplitPoint < et.EndTime
)

现在，让我们按顺序排列数据并创建新记录，以前面的记录作为开始，以下一个记录作为结束时间，直到每个日期/rid集的记录用完为止。

SELECT * FROM
(
    SELECT *,
    LEAD(StartTime) OVER (PARTITION BY PRDATE, RID ORDER BY StartTime) AS EndTime 
    FROM MyDates
) d
WHERE d.EndTime IS NOT NULL

这是输出：

ID  PRDATE  RID StartTime   EndTime
10000422    2014-09-01  6   2014-09-01 04:08:15.000 2014-09-01 04:13:13.000
10000457    2014-09-01  103 2014-09-01 04:08:23.000 2014-09-01 04:15:00.000
10000457    2014-09-01  103 2014-09-01 04:15:00.000 2014-09-01 04:45:00.000
10000457    2014-09-01  103 2014-09-01 04:45:00.000 2014-09-01 05:30:00.000
10000457    2014-09-01  103 2014-09-01 05:30:00.000 2014-09-01 09:44:47.000
20000432    2014-09-01  116 2014-09-01 04:08:51.000 2014-09-01 04:15:00.000
20000432    2014-09-01  116 2014-09-01 04:15:00.000 2014-09-01 04:45:00.000
20000432    2014-09-01  116 2014-09-01 04:45:00.000 2014-09-01 05:07:38.000

票数 1

Stack Overflow用户

发布于 2014-11-26 16:39:58

通过使用UNION，您可能能够完成您正在寻找的内容，我做了一个快速而肮脏的查询，以显示如何以我以为您希望这样做的方式拉出日期。其中最棘手的部分是上一次选择中的Start_Date，因为除非您想要我在查询中输入的逻辑，否则您将需要进行调整以满足您的需要。我还在你的桌子上增加了一个分裂开始和分裂的时间，这使得联盟再次有可能，不确定这对你是否可能，但我想如果我可以的话，我会尽力帮助你。

DECLARE 
    @Start_Date DATETIME = '2014-09-01 04:08:23.000'
    ,@End_Date DATETIME = '2014-09-01 09:44:47.000'

    create table #SplitPoints (SplitPointStart DateTime,SplitPointEnd DateTime); 
    INSERT INTO #SplitPoints(SplitPointStart,SplitPointEnd) VALUES 
    ('2014-09-01 04:15:00.000','2014-09-01 04:45:00.000'),
    ('2014-09-01 04:45:00.000','2014-09-01 05:30:00.000'),
    ('2014-09-01 05:30:00.000','2014-09-01 06:00:00.000');


    SELECT 
            @Start_Date
    ,CASE 
    WHEN DATEPART(m,@Start_Date) <= 15 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@Start_Date)AS VARCHAR) + ':15.000' 
    WHEN DATEPART(m,@Start_Date) <= 30 AND DATEPART(m,@Start_Date) > 15 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@Start_Date)AS VARCHAR) + ':30.000' 
    WHEN DATEPART(m,@Start_Date) <= 45 AND DATEPART(m,@Start_Date) > 30 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@Start_Date)AS VARCHAR) + ':45.000' 
    WHEN DATEPART(m,@Start_Date) <= 60 AND DATEPART(m,@Start_Date) > 45 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,DATEADD(hh,1,@Start_Date))AS VARCHAR) + ':00.000' 
    ELSE ''
    END
    UNION
    SELECT
    SplitPointStart,SplitPointEnd
    FROM #Splitpoints
    WHERE 
    SplitPointStart > @Start_Date
    AND SplitPointEnd < @End_Date
    UNION 
    SELECT 
    CASE 
    WHEN DATEPART(m,DATEADD(mm,-30,@End_Date)) <= 15 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@End_Date)AS VARCHAR) + ':15.000' 
    WHEN DATEPART(m,DATEADD(mm,-30,@End_Date)) <= 30 AND DATEPART(m,@End_Date) > 15 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@End_Date)AS VARCHAR) + ':30.000' 
    WHEN DATEPART(m,DATEADD(mm,-30,@End_Date)) <= 45 AND DATEPART(m,@End_Date) > 30 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@End_Date)AS VARCHAR) + ':45.000' 
    WHEN DATEPART(m,DATEADD(mm,-30,@End_Date)) <= 60 AND DATEPART(m,@End_Date) > 45 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,DATEADD(hh,-1,@End_Date))AS VARCHAR) + ':00.000' 
    ELSE ''
    END
    ,@End_Date

    drop table #SplitPoints

票数 0

Stack Overflow用户

发布于 2014-11-26 16:54:13

我认为您所需要的功能不可能直接从查询中获得，它需要类似循环之类的东西。

我想下面的代码能帮到你

DECLARE @ID NUMERIC
DECLARE @PRDDate DATE,
        @rid     NUMERIC
DECLARE @starttime DATETIME
DECLARE @endtime DATETIME
DECLARE @SplitPoint DATETIME

CREATE TABLE #desiredresult
  (
     id        INT,
     prdate    DATE,
     rid       INT,
     starttime DATETIME,
     endtime   DATETIME
  )

 -- declare cursor called firstcursor
  DECLARE firstcursor CURSOR FOR
  SELECT id,
         prdate,
         rid,
         starttime,
         endtime
  FROM   #exampletable
  ORDER  BY id

-- Open the cursor
OPEN firstcursor

-- Fetch the first row of the cursor and assign its values into variables
FETCH next FROM firstcursor INTO @ID, @PRDDate, @rid, @starttime, @endtime

-- perform action whilst a row was found
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE secoundcursor CURSOR FOR
             SELECT splitpoint
                FROM   #splitpoints
                WHERE  splitpoint < @endtime
                ORDER  BY splitpoint
      OPEN secoundcursor      
      -- perform action whilst a row was found
      DECLARE @prev DATETIME
      FETCH next FROM secoundcursor INTO @SplitPoint
      DECLARE @index int = 0
      WHILE @@FETCH_STATUS = 0
        BEGIN
            IF( @index =0 )
              BEGIN
                  INSERT INTO #desiredresult
                          (id,
                           prdate,
                           rid,
                           starttime,
                           endtime)
                  VALUES     (@ID,
                          @PRDDate,
                          @rid,
                          @starttime,
                          @SplitPoint)
               END
            ELSE
               BEGIN
                  INSERT INTO #desiredresult
                              (id,
                               prdate,
                               rid,
                               starttime,
                               endtime)
                  VALUES     (@ID,
                              @PRDDate,
                              @rid,
                              @prev,
                              @SplitPoint)
              END

            SET @prev = @SplitPoint
            SET @index = @index + 1
           FETCH next FROM secoundcursor INTO @SplitPoint
        END

      CLOSE secoundcursor

      -- Free memory used by cursor
      DEALLOCATE secoundcursor

      if(@index =0)
      BEGIN
        INSERT INTO #desiredresult
                          (id,
                           prdate,
                           rid,
                           starttime,
                           endtime)
              VALUES     (@ID,
                          @PRDDate,
                          @rid,
                          @starttime,
                          @endtime)
      END
      else
          begin
              INSERT INTO #desiredresult
                          (id,
                           prdate,
                           rid,
                           starttime,
                           endtime)
              VALUES     (@ID,
                          @PRDDate,
                          @rid,
                         @prev,
                          @endtime)
      end

      -- get next row of cursor
      FETCH next FROM firstcursor INTO @ID, @PRDDate, @rid, @starttime, @endtime
   END

 -- Close the cursor to release locks
 CLOSE firstcursor

 -- Free memory used by cursor
 DEALLOCATE firstcursor

 SELECT *
 FROM   #desiredresult

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/27153326

复制

相似问题

问Server 2008 - StartTime、EndTime在选定点上的分割时间间隔
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Server 2008 - StartTime、EndTime在选定点上的分割时间间隔EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Server 2008 - StartTime、EndTime在选定点上的分割时间间隔
EN