sql - 如何生成新行并根据特定持续时间为日期时间列添加 1 秒

Question

我在sql azure中有一个数据，如下所示，并尝试循环并迭代一个新的Start_date列(让我们称之为“New_time”)，根据另一列(StatusDuration)增加1秒，然后创建一个新列(让我们称之为“Interval”)将新时间的新列四舍五入到最接近的 15 分钟间隔，最后按 (UserId,StatusKey,Interval) 对日期进行分组并计算 new_time。

SQL Azure 上的日期如下所示:-

userid  statuskey   stateDuration   StatusDateTimeGMT   EndDateTimeGMT
Abanoub.Sanad   available   4656    2022-09-29 07:29:16 2022-09-29 08:46:52
Abanoub.Sanad   available   24626   2022-09-29 10:12:26 2022-09-29 17:02:52
Abanoub.Sanad   available   9030    2022-09-29 17:18:23 2022-09-29 19:48:53
Abanoub.Sanad   available   33647   2022-09-29 23:04:07 2022-09-30 08:24:54

想要看到的输出数据

同一逻辑上sql和excel查询的对比

使用的查询

with cte1 as(
select userid,statuskey,stateDuration,[StatusDateTimeGMT]+ '02:00' as StatusDateTimeGMT,[EndDateTimeGMT] + '02:00' as EndDateTimeGMT,
interval= cast(floor(cast([StatusDateTimeGMT]+ '02:00' as float)*(96))/(96) as datetime),
interval_end_date=cast(ceiling(cast([StatusDateTimeGMT]+ '02:00' as float)*(96))/(96) as datetime)
from AgentActivityLog
where [StatusDateTimeGMT]+'2:00'>= '2022-09-28' and [StatusDateTimeGMT]+'2:00' < '2022-09-30' and StateDuration > 0 and userid = 'Abanoub.Sanad'),
cte2 as(
select userid,statuskey,[EndDateTimeGMT],[StatusDateTimeGMT],interval,interval_end_date from cte1
union all
select userid,statuskey,[EndDateTimeGMT],interval_end_date,dateadd(second,900,interval),dateadd(second,900,interval_end_date) 
from cte2 
where dateadd(second,15,interval_end_date)< [EndDateTimeGMT])
select userid,statuskey,interval,
[Duration]= case 
when interval_end_date<[EndDateTimeGMT] then datediff(second,[StatusDateTimeGMT],interval_end_date)
else datediff(second,[StatusDateTimeGMT],[EndDateTimeGMT]) end
from cte2
ORDER BY interval

Answer 1

我已经尝试了sql脚本来满足上述要求。

• 示例源表如下图所示

SQL 脚本

• 使用下面的 SQL 脚本方法1

    with cte as(
    select userid,statuskey,stateDuration,cnt=0,[start_date],
    nearestdate= cast(floor(cast([start_date] as float)*(24*4))/(24*4) as smalldatetime) from src_table
    UNION ALL
    SELECT userid,statuskey,stateDuration,cnt+1,dateadd(second,1,[start_date]),
    nearestdate= cast(floor(cast(dateadd(second,1,[start_date]) as float)*(24*4))/(24*4) as smalldatetime) from cte where cnt+1<stateDuration)
    (select distinct userid,statuskey,nearestdate,count(nearestdate)as count from cte group by userid,statuskey,nearestdate) 
    option (maxrecursion 0);

替代方法-方法2

    with cte1 as(
    select userid,statuskey,stateDuration,[start_date],
    nearestdate= cast(floor(cast([start_date] as float)*(24*4))/(24*4) as datetime),
    batch_end_date=cast(ceiling(cast([start_date] as float)*(24*4))/(24*4) as datetime),
    total_end_date=DATEADD(second,stateDuration,[start_date])
    from src_table
    ),
    cte2 as(
    select userid,statuskey,total_end_date,[start_date],nearestdate,batch_end_date from cte1
    union all
    select userid,statuskey,total_end_date,batch_end_date,dateadd(second,900,nearestdate),
    dateadd(second,900,batch_end_date) from cte2 where dateadd(second,15,batch_end_date)< total_end_date)
    select userid,statuskey,nearestdate,
    [count]= case 
    when batch_end_date<total_end_date then datediff(second,Start_date,batch_end_date)
    else datediff(second,Start_date,total_end_date) end
    from cte2

输出

• 输出表符合要求