Get Max Value between a datetime range and interval

Question

0.00/5 (No votes)

See more:

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:

SQL

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

Posted 24-Jul-12 11:13am

Member 8336910

Add a Solution

Comments

Sergey Alexandrovich Kryukov 24-Jul-12 17:15pm

The double post is fine if you makes sure you removed the duplicate question. Did you?
--SA

Member 8336910 24-Jul-12 17:18pm

Yes I did. I thought I solved it. Then I deleted the first post. Then 5 minutes later I noticed I didn't solve it. Then I posted this post again

Sergey Alexandrovich Kryukov 24-Jul-12 23:03pm

No problem then. Thank you for your reply. Good luck.
--SA

3 solutions

Solution 1

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

SQL

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)

Posted 24-Jul-12 21:07pm

Bernhard Hiller

Solution 2

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

SQL

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

Posted 25-Jul-12 0:44am

Kaushik Saha from Kolkata,India

Comments

Member 8336910 25-Jul-12 13:34pm

It says it is invalid because DATE_TIME_COLLECTED isn't in the aggregate function or the group by. When I added in the group by, it gives me the data

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 8336910 · Accepted Answer · 2012-07-25T08:04:00

SQL

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