473,327 Members | 1,892 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 1926
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

21
by: John Fabiani | last post by:
Hi, I'm a newbie and I'm attempting to learn howto create a select statement. When I use >>> string1='18 Tadlock Place' >>> cursor.execute("SELECT * FROM mytest where address = %s",string1) All...
4
by: jimh | last post by:
I'm not a SQL expert. I want to be able to write a stored procedure that will return 'people who bought this product also bought this...'. I have a user table that links to a transaction table...
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
4
by: Terencetrent | last post by:
I having been using Access '97/2002 for about 4 years now and have never really had the need or the time to learn visual basic. Well, I think the time has finally come. I need help with Visual...
4
by: Polly | last post by:
I had a macro that ran a parameter query and created and opened an Excel file with the system date as part of the file name, but I had to change the file name by hand. So I converted the macro to...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
0
by: djflow | last post by:
Hi! II was wondering if you can help me with SQL query.. Below 7 separated select query works fine(only when they are retrieved separately) But I want to combined them together and so that i...
9
by: P3Eddie | last post by:
Hello all! I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same. My problem may be a simple find duplicates / do something...
45
by: dizzydangler | last post by:
Hi, I'm new to access (2007, running on XP), but I've gotten some great help from this site on my project so far. However, the more I know, the more complex the problems seem to become.... I'm...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.