持续时间总和基于需要在前半小时间隔内更新的日期时间 [英] SUM of Duration based on the DateTime that needs to be updated in previous Half hour interval

问题描述

我搜查了几个问题，但都没有完全回答我的问题。因此在这里提出这个问题。我有一个表格，说'表格'有DateTime，ID，Code和Duration是列。
DateTime的数据为'2017-12-12 00:30:00'，'2017-12-12 00:30:37'等，我需要基于此间隔日期来合计持续时间列。
如果DateTime是'2017-12-12 00:30:00'，那么我们的查询应该将'2017-12-12 00:00:00'到'2017- 12-12 00:30:00'
并在'2017-12-12 00:00:00'中显示

实际的表格数据。

  DateTime ID代码持续时间
 2017-12-12 00:30:00 1 12 1800 
 2017-12-12 00:30:37 1 12 37 
 2017-12-12 01:00:00 1 12 1793 
 2017-12-12 01:30:00 1 12 1800 
 2017-12-12 01:30:59 1 12 59 
  

如果我运行查询然后预期的结果是，

  DateTime ID代码持续时间
 2017-12-12 00:00:00 1 12 1800 
 2017-12-12 00:30:00 1 12 1800 
 2017-12-12 01:00:00 1 12 1800 
 2017-12-12 01:30:00 1 12 59 
  

我在我身边的查询如下，

  SELECT 
 Interval =（CASE WHEN datepart（MINUTE，[DateTime]）= 0和datepart（SS，DateTime）= 0 THEN 
 CAST （CONV ERT（VARCHAR（30），DATEADD（HOUR，-1，[DateTime]），101）+''+ cast（格式（DATEPART（HOUR，DATEADD（HOUR，-1，[DateTime]）），'0＃' ）
 as varchar）+'：30:00'as DateTime）
 ELSE（CAST（CONVERT（VARCHAR（30），[DateTime]，101）+''+（datepart（MINUTE， [DateTime]）<= 30 then cast（格式（DATEPART（HOUR，[DateTime]），'0＃'）as varchar）+'：00:00'
 else cast（format（DATEPART（HOUR， （DateTime）），'0＃'）as varchar）+'：30:00'end）as DateTime））END），
 ID，Code，Duration = SUM（Duration）FROM Table 
 WHERE [DateTime]> ='2017-12-11 00:00:00'和[日期时间]< ='2017-12-11 23:59:59'
 GROUP BY ID，代码，
（CASE WHEN datepart（MINUTE，[DateTime]）= 0和datepart（SS，DateTime）= 0 THEN 
 CAST（CONVERT（VARCHAR（30），DATEADD（HOUR，-1，[DateTime]），101 ）+''+ cast（format（DATEPART（HOUR，DATEADD（HOUR，-1，[DateTime]）），'0＃'）as varchar）+'：30:00'as DateTime）
 ELSE CAST（CONVERT（VARCHAR（30），[DateTime]，101）+''+（cas当datepart（MINUTE，[DateTime]）<= 30然后被施放（作为varchar的格式（DATEPART（HOUR，[DateTime]），'0＃'）+'：00:00'
 else cast格式（DATEPART（HOUR，[DateTime]），'0＃'）as varchar）+'：30:00'end）as DateTime））END）
  

我得到的结果是，

  DateTime ID代码持续时间
 2017-12-12 00:00:00 1 12 1837 
 2017-12-12 00:30:00 1 12 1793 
 2017-12-12 01:00:00 1 12 1859 
  

我在这里发生的错误是，秒部分不是分裂，而是添加到前一个区间，而是下一个时间间隔。关键是我们不应该在30分钟的时间间隔内获得超过1800秒的日期时间。

如果您需要更多说明，请让我知道我进一步澄清。 p>

解决方案

使用简单的日期逻辑

  GROUP BY DATEADD（MINUTE 
，（DATEDIFF（MINUTE，[DateTime]，'2017-12-12'）/ 30）* 30 
，'2017-12-12'）
  

这会将DateTime列（不应该使用关键字作为列名称）从种子值中划分为30分钟的片段，任何剩余物。然后它再次乘以30以获得桶中种子值持续时间的分钟数。最后，它将分钟添加到种子值以创建日期时间桶。

请注意，种子日期值并不重要，但种子时间值会有所不同，因为您希望桶对齐到半小时。使用午夜是最干净的方法。

I searched several questions but that all are not exactly answering my question. Hence raised this question here. I have a table say 'Table' that has has DateTime,ID, Code and Duration are the columns. DateTime will be having data as '2017-12-12 00:30:00','2017-12-12 00:30:37' etc and I need to SUM the duration column based on this Interval date. If DateTime is '2017-12-12 00:30:00' then our query should sum all the data between '2017-12-12 00:00:00' to '2017-12-12 00:30:00' and show it in '2017-12-12 00:00:00'

Actual table data.

DateTime    ID  Code    Duration
2017-12-12 00:30:00 1   12  1800
2017-12-12 00:30:37 1   12  37
2017-12-12 01:00:00 1   12  1793
2017-12-12 01:30:00 1   12  1800
2017-12-12 01:30:59 1   12  59

If I run the query then expected result is,

DateTime    ID  Code    Duration
2017-12-12 00:00:00 1   12  1800
2017-12-12 00:30:00 1   12  1800
2017-12-12 01:00:00 1   12  1800
2017-12-12 01:30:00 1   12  59

The Query I have with me is below,

      SELECT 
      Interval=(CASE WHEN datepart(MINUTE,[DateTime]) = 0 and datepart(SS,DateTime)=0 THEN
CAST(CONVERT(VARCHAR(30),DATEADD(HOUR,-1,[DateTime]),101) + ' '+ cast(format(DATEPART(HOUR,DATEADD(HOUR,-1,[DateTime])),'0#')
 as varchar)+':30:00' as DateTime)
ELSE (CAST(CONVERT(VARCHAR(30),[DateTime],101) +' ' + (case when datepart(MINUTE,[DateTime])<=30 then cast(format(DATEPART(HOUR,[DateTime]),'0#') as varchar)+':00:00'
      else cast(format(DATEPART(HOUR,[DateTime]),'0#') as varchar)+':30:00' end) as DateTime)) END),    
      ID,Code,Duration=SUM(Duration) FROM Table
      WHERE [DateTime]  >= '2017-12-11 00:00:00'  and [DateTime] <= '2017-12-11 23:59:59' 
        GROUP BY  ID,Code,
      (CASE WHEN datepart(MINUTE,[DateTime]) = 0 and datepart(SS,DateTime)=0 THEN
CAST(CONVERT(VARCHAR(30),DATEADD(HOUR,-1,[DateTime]),101) + ' '+ cast(format(DATEPART(HOUR,DATEADD(HOUR,-1,[DateTime])),'0#') as varchar)+':30:00' as DateTime)
ELSE (CAST(CONVERT(VARCHAR(30),[DateTime],101) +' ' + (case when datepart(MINUTE,[DateTime])<=30 then cast(format(DATEPART(HOUR,[DateTime]),'0#') as varchar)+':00:00'
       else cast(format(DATEPART(HOUR,[DateTime]),'0#') as varchar)+':30:00' end) as DateTime)) END)

The output i'm getting is,

DateTime    ID  Code    Duration
2017-12-12 00:00:00 1   12  1837
2017-12-12 00:30:00 1   12  1793
2017-12-12 01:00:00 1   12  1859

The mistake I have here is, seconds part is not splitting and it is adding to previous interval, instead of next interval. Key point here is we should not get more than 1800 seconds in a 30 minutes interval DateTime.

If you need any more clarifications please let me know I clarify further.

解决方案

Use simple date logic

 GROUP BY  DATEADD( MINUTE
    , ( DATEDIFF( MINUTE, [DateTime], '2017-12-12') / 30 )  * 30
    , '2017-12-12')

This divides the DateTime column (you should never use keywords as column names) into 30 minute segments from the seed value, discarding any remainders. Then it multiplies by 30 again to get to the number of minutes duration from the seed value for the bucket. Finally it adds the minutes to the seed value to create the datetime bucket.

Note that the seed date value doesn't matter, but the seed time value will make a difference because you want the buckets aligned to the half hour. Using midnight is the cleanest approach.

这篇关于持续时间总和基于需要在前半小时间隔内更新的日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！