获取日期时间范围和间隔之间的最大值 [英] Get Max Value between a datetime range and interval

问题描述

我在寻找答案或任何可能导致我找到答案的问题上遇到了麻烦.

我有一些具有价值的数据以及收集时间.我希望能够在整个指定的日期范围内获得一个间隔之间的最大值.

例如，我想获取12年12月10日至2012年10月10日之间的数据，并每小时获取最大值.所以...
06/10/12 00:00:00至06/10/12 00:59:59值为64
06/10/12 01:00:00至06/10/12 01:59:59值为32
06/10/12 02:00:00至06/10/12 02:59:59值是22
.
.
.
2012年7月10日22:00:00至2012年6月10日23:59:59值是94

知道我该怎么做吗?

到目前为止，我已经尝试过了:

Hi I am having trouble finding the answer or anything that could lead me to the answer.

I have some data where it has a value and the time it was collected. I want to be able to get the Max value between an interval throughout some specified date range.

For example I want to grab data between 06/10/12 to 07/10/12 and get the Max value every hour. So...
06/10/12 00:00:00 to 06/10/12 00:59:59 value is 64
06/10/12 01:00:00 to 06/10/12 01:59:59 value is 32
06/10/12 02:00:00 to 06/10/12 02:59:59 value is 22
.
.
.
07/10/12 22:00:00 to 06/10/12 23:59:59 value is 94

Any idea how I can do this?

I''ve tried this so far:

DECLARE @tableinfo table (TAG_NAME varchar(50) not null,
DATE_TIME_COLLECTED datetime not null, F_CURRENT_TAG_VALUE real not null)
DECLARE @startDT datetime
DECLARE @endDT datetime
DECLARE @LB datetime
DECLARE @UB datetime
SET @startDT = '2012-07-24 01:00:00.720'
SET @endDT = '2012-07-24 11:15:51.810'
SET @LB = @startDT
SET @UB = DATEADD(HOUR,1,@LB)
if(@UB>@endDT) SET @UB = @endDT
while(@UB<=@endDT)
BEGIN

insert into @tableinfo
SELECT [TAG_NAME] ,[DATE_TIME_COLLECTED],MAX([F_CURRENT_TAG_VALUE]) FROM [FMCS].[dbo].[M_TAG_VALUE]
WHERE DATE_TIME_COLLECTED BETWEEN @LB and @UB AND TAG_NAME='XVI_5990'
GROUP BY TAG_NAME, DATE_TIME_COLLECTED, F_CURRENT_TAG_VALUE

SET @LB = @UB
SET @UB = DATEADD(HOUR,1,@LB)
END

select * from @tableinfo

对不起，我发了双重帖子.我以为自己解决了，然后注意到我没有

Sorry for the double post. I thought I solved it, then noticed I didn''t

推荐答案

在GROUP BY子句中，您必须对日期部分使用函数.

In the GROUP BY clause, you must use functions for the date parts. Something like

SELECT MAX(aValue), YEAR(aDate), MONTH(aDate), DAY(aDate), HOUR(aDate)
FROM aTable
WHERE aDate BETWEEN startDate AND endDate
GROUP BY YEAR(aDate), MONTH(aDate), DAY(aDate), HOUR(aDate)

这是更改后的代码段
也许会对您有帮助

This is your code snippets after changing
May be it will help you

DECLARE @tableinfo table (TAG_NAME varchar(50) not null,
DATE_TIME_COLLECTED datetime not null, F_CURRENT_TAG_VALUE real not null)
DECLARE @startDT datetime
DECLARE @endDT datetime
DECLARE @LB datetime
DECLARE @UB datetime
SET @startDT = '2012-07-24 01:00:00.720'
SET @endDT = '2012-07-24 11:15:51.810'
SET @LB = @startDT
SET @UB = DATEADD(HOUR,1,@LB)
if(@UB>@endDT) SET @UB = @endDT
while(@UB<=@endDT)
BEGIN
 
insert into @tableinfo
----Changing portion-------
SELECT [TAG_NAME] ,MAX([DATE_TIME_COLLECTED]),MAX([F_CURRENT_TAG_VALUE]) FROM [FMCS].[dbo].[M_TAG_VALUE]
GROUP BY TAG_NAME
HAVING (DATE_TIME_COLLECTED BETWEEN @LB and @UB) AND TAG_NAME='XVI_5990'
----------------------- 
SET @LB = @UB
SET @UB = DATEADD(HOUR,1,@LB)
END
 
select * from @tableinfo

DECLARE @tableinfo table (DATE_TIME_COLLECTED datetime not null, F_CURRENT_TAG_VALUE real not null)
DECLARE @startDT datetime
DECLARE @endDT datetime
DECLARE @LB datetime
DECLARE @UB datetime
SET @startDT = '2012-07-24 01:00:00.720'
SET @endDT = '2012-07-24 11:15:51.810'
SET @LB = @startDT
SET @UB = DATEADD(minute,30,@LB)
if(@UB>@endDT) SET @UB = @endDT
while(@UB<=@endDT)
BEGIN

insert into @tableinfo
------------------------------------
SELECT DATE_TIME_COLLECTED, F_CURRENT_TAG_VALUE
FROM [FMCS].[dbo].[M_TAG_VALUE]
JOIN
(SELECT MAX([F_CURRENT_TAG_VALUE]) AS value FROM [FMCS].[dbo].[M_TAG_VALUE]
WHERE (DATE_TIME_COLLECTED BETWEEN @LB and @UB) AND TAG_NAME='XVI_5990')
 data ON data.value=F_CURRENT_TAG_VALUE
WHERE (DATE_TIME_COLLECTED BETWEEN @LB and @UB) AND TAG_NAME='XVI_5990'
------------------------------------
SET @LB = @UB
SET @UB = DATEADD(minute,30,@LB)
END

select * from @tableinfo

这篇关于获取日期时间范围和间隔之间的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！