sql server-在sql中根据时间值自动增加日期

Question

我有一个客户端数据，它有一个候选id，一个只有varchar格式的日期的开始日期，以及一个与该日期相关的varchar格式时间值，在一个单独的列中。

为了对数据进行简要解释，候选人将在一天中的任何时间点（例如2014年10月20日上午10:00）来到研究中心。开始日期为2014年10月20日，从上午10:00开始，他将根据时间点进行测试。因此，如果时间点是2小时，那么在中午12:00，他将进行测试。如果时间点是8小时，则会添加到上午10:00，在此基础上，他将在下午06:00进行测试。当时间点达到00:00时，开始日期需要是下一个日期，而不是2014年10月20日。

开始日期需要附加时间值，这样当它跨越时间00:00时，开始日期需要增加1，即第二天。我使用下面的代码将开始日期添加到时间中

CAST（STARTDATE作为日期时间）+CAST（CAST（STUF（[TIME]，3,0，':'）作为时间（0））作为日期时间

通过上面的代码，我已经创建了预期的日期时间，然而

由于动态数据的原因，我将无法对该值进行硬编码。我尝试应用>=和<到类似的时间值

case when MyTime >= '00:00' and MyTime < '08:10' the Dateadd(day, 1, date)

这非常有效，但我担心的是，我不能将值设置为08:10，因为它不是所有行的常量。

我提供了我的数据截图和预期日期栏供参考。

Candidate   StartDate   Time    Expected DateTime       Timepoint
1           20141020    1000    2014-10-20 10:00:00     0 HR
1           20141020    1200    2014-10-20 12:00:00     02 HR
1           20141020    1400    2014-10-20 14:00:00     04 HR
1           20141020    1800    2014-10-20 18:00:00     08 HR
1           20141020    0000    2014-10-21 00:00:00     12 HR
1           20141020    1200    2014-10-21 12:00:00     24 HR
1           20141020    1300    2014-10-21 13:00:00     25 HR
2           20141020    1100    2014-10-20 11:00:00     0 HR
2           20141020    1300    2014-10-20 13:00:00     02 HR
2           20141020    1500    2014-10-20 15:00:00     04 HR
2           20141020    1900    2014-10-20 19:00:00     08 HR
2           20141020    2100    2014-10-20 21:00:00     12 HR
2           20141020    2300    2014-10-20 23:00:00     24 HR
2           20141020    0230    2014-10-21 02:30:00     27 HR
2           20141020    1330    ..............
3           20141026    1530    ...............
3           20141026    2000    
3           20141026    0930    
3           20141026    1020    
3           20141026    1120    

有人能帮我处理这个请求吗？

Answer 1

首先，这看起来像是错误的数据，可能应该使用正确的数据类型（datetime）正确插入。

您可以使用递归cte来执行您想要的操作。

为了使用您的数据集，我添加了一个Id列，用于对数据进行排序，因为您无法按日期和时间排序，因为每个候选分组的数据都存在日期重复的问题。

可运行样本：

-- temp table to hold your data
CREATE TABLE #Temp
    (
      Id INT ,
      Candidate INT ,
      MyDate NVARCHAR(10) ,
      MyTime NVARCHAR(10)
    )
-- insert the dummy data (with added id for ordering)
INSERT  INTO #Temp
        ( Id, Candidate, MyDate, MyTime )
VALUES  ( 1, 1, '20141020', '1000' ),
        ( 2, 1, '20141020', '1200' ),
        ( 3, 1, '20141020', '1400' ),
        ( 4, 1, '20141020', '1800' ),
        ( 5, 1, '20141020', '0000' ),
        ( 6, 1, '20141020', '1200' ),
        ( 7, 1, '20141020', '1300' ),
        ( 8, 2, '20141020', '1100' ),
        ( 9, 2, '20141020', '1300' ),
        ( 10, 2, '20141020', '1500' ),
        ( 11, 2, '20141020', '1900' ),
        ( 12, 2, '20141020', '2100' ),
        ( 13, 2, '20141020', '2300' ),
        ( 14, 2, '20141020', '0230' ),
        ( 15, 2, '20141020', '1330' ),
        ( 16, 3, '20141026', '1530' ),
        ( 17, 3, '20141026', '2000' ),
        ( 18, 3, '20141026', '0930' ),
        ( 19, 3, '20141026', '1020' ),
        ( 20, 3, '20141026', '1120' )
-- insert data into new temp table, converting values to dates and times  
SELECT  Id ,
        Candidate ,
        CONVERT(DATE, MyDate) AS MyDate ,
        CONVERT(TIME, LEFT(MyTime, 2) + ':' + RIGHT(MyTime, 2)) AS MyTime
INTO    #TempFormatted
FROM    #Temp
-- recursive cte
;WITH    cte
          AS ( 
               -- take the first row
               SELECT   Id ,
                        Candidate ,
                        MyDate ,
                        MyTime
               FROM     #TempFormatted
               WHERE    Id = 1
               -- add next row (look at join: t.Id = cte.Id + 1)
               UNION ALL
               SELECT   t.Id ,
                        t.Candidate ,
                        -- CASE used to compare rows and add a DAY if required
                        CASE WHEN t.MyTime > cte.MyTime
                                  AND t.Candidate = cte.Candidate
                             THEN cte.MyDate
                             WHEN t.MyTime < cte.MyTime
                                  AND t.Candidate = cte.Candidate
                             THEN DATEADD(DAY, 1, t.MyDate)
                             ELSE t.MyDate
                        END AS MyDate ,
                        t.MyTime
               FROM     cte
                        INNER JOIN #TempFormatted t ON t.Id = cte.Id + 1
             )
    -- output from cte
    SELECT  cte.Id ,
            cte.Candidate ,
            cte.MyDate ,
            cte.MyTime ,
            CONVERT(DATETIME, cte.MyDate) + CONVERT(DATETIME, cte.MyTime) FormattedValue
    FROM    cte
-- tidy up
DROP TABLE #Temp
DROP TABLE #TempFormatted