469,890 Members | 2,154 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

Select Query Help

Hello Group,

I have a table that has 3 columns:
ID (int), datetime, Value(varchar)

ID = ID for the SNMP device
datetime = time record was added
value = value added for that device.

This table contains sample # values taken from a device (SNMP) every 5
minutes.

I would like to issue a Select query that would do the following:

For each ID, find the first and last values for a given date range (Ex:
First and Last values for July),
Then subtract the latest value from the previous value for that same ID.

Similar to a power meter on your house, finding the reading on the meter at
the first day of the month, and the last day of the month to calculate usage
for that month.

Thanks For the help.
Please post on group.

Jul 20 '05 #1
5 1208
Got2Go wrote:
I would like to issue a Select query that would do the following:

For each ID, find the first and last values for a given date range (Ex:
First and Last values for July),
Then subtract the latest value from the previous value for that same ID.


select
min(value),
max(value),
max(value)-min(value)
from tablename
where date_ >= '2004-01-01'
and date_ <= '2005-01-01'
group by id;
Jul 20 '05 #2
Hi Aggro,
Thanks for the suggestion.

This does not seem to do what I needed.
It looks for the highest and lowest values for a given date range.

But what I need is the first value and last values for the given date range.
First and last based on date, and not the actual value.

Any other ideas ?

Thank You.

"Aggro" <sp**********@yahoo.com> wrote in message
news:Yg***************@read3.inet.fi...
Got2Go wrote:
I would like to issue a Select query that would do the following:

For each ID, find the first and last values for a given date range (Ex:
First and Last values for July),
Then subtract the latest value from the previous value for that same ID.


select
min(value),
max(value),
max(value)-min(value)
from tablename
where date_ >= '2004-01-01'
and date_ <= '2005-01-01'
group by id;

Jul 20 '05 #3
how do you define first and last? The earliest/last date?

Got2Go wrote:
Hi Aggro,
Thanks for the suggestion.

This does not seem to do what I needed.
It looks for the highest and lowest values for a given date range.

But what I need is the first value and last values for the given date range.
First and last based on date, and not the actual value.

Any other ideas ?

Thank You.

"Aggro" <sp**********@yahoo.com> wrote in message
news:Yg***************@read3.inet.fi...
Got2Go wrote:

I would like to issue a Select query that would do the following:

For each ID, find the first and last values for a given date range (Ex:
First and Last values for July),
Then subtract the latest value from the previous value for that same ID.


select
min(value),
max(value),
max(value)-min(value)
from tablename
where date_ >= '2004-01-01'
and date_ <= '2005-01-01'
group by id;


Jul 20 '05 #4
Hi David,
Yes, correct.

Given a date range, the first and last records that fall within that date
range. Based on the date of the record.

Thanks for the help!
"David L" <dl@nospam.com> wrote in message
news:gM*******************@news-server.bigpond.net.au...
how do you define first and last? The earliest/last date?

Got2Go wrote:
Hi Aggro,
Thanks for the suggestion.

This does not seem to do what I needed.
It looks for the highest and lowest values for a given date range.

But what I need is the first value and last values for the given date range. First and last based on date, and not the actual value.

Any other ideas ?

Thank You.

"Aggro" <sp**********@yahoo.com> wrote in message
news:Yg***************@read3.inet.fi...
Got2Go wrote:
I would like to issue a Select query that would do the following:

For each ID, find the first and last values for a given date range (Ex:
First and Last values for July),
Then subtract the latest value from the previous value for that same ID.
select
min(value),
max(value),
max(value)-min(value)
from tablename
where date_ >= '2004-01-01'
and date_ <= '2005-01-01'
group by id;


Jul 20 '05 #5
Got2Go wrote:
But what I need is the first value and last values for the given date range.
First and last based on date, and not the actual value.

Any other ideas ?


If you are using version 4.1 or above, you could use subselects to help
out with the query (I'm pretty sure it is possible, but never tried it,
since I live in the past with version 3.x). If you are using version <
4.1, your options are:
- Use a query with joins (I don't know how to one in this case, and I'm
not even sure if it is possible)
- Run multiple queries. This is the easy way, but most likely the slow
way if you have many devices. You can do this by first finding out the
min and max dates for each device and then selecting values matching them.
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

21 posts views Thread by John Fabiani | last post: by
4 posts views Thread by jimh | last post: by
3 posts views Thread by Tcs | last post: by
4 posts views Thread by Terencetrent | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.