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.

re: Select Query Help

Got2Go wrote:
[color=blue]
> 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.[/color]

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

re: Select Query Help

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" <spammerdream@yahoo.com> wrote in message
news:YgsPc.286$Qk2.132@read3.inet.fi...[color=blue]
> Got2Go wrote:
>[color=green]
> > 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.[/color]
>
> select
> min(value),
> max(value),
> max(value)-min(value)
> from tablename
> where date_ >= '2004-01-01'
> and date_ <= '2005-01-01'
> group by id;[/color]

re: Select Query Help

how do you define first and last? The earliest/last date?

Got2Go wrote:[color=blue]
> 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" <spammerdream@yahoo.com> wrote in message
> news:YgsPc.286$Qk2.132@read3.inet.fi...
>[color=green]
>>Got2Go wrote:
>>
>>[color=darkred]
>>>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.[/color]
>>
>>select
>>min(value),
>>max(value),
>>max(value)-min(value)
>>from tablename
>>where date_ >= '2004-01-01'
>>and date_ <= '2005-01-01'
>>group by id;[/color]
>
>
>[/color]

re: Select Query Help

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:gMHPc.30284$K53.24504@news-server.bigpond.net.au...[color=blue]
> how do you define first and last? The earliest/last date?
>
> Got2Go wrote:[color=green]
> > 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[/color][/color]
range.[color=blue][color=green]
> > First and last based on date, and not the actual value.
> >
> > Any other ideas ?
> >
> > Thank You.
> >
> > "Aggro" <spammerdream@yahoo.com> wrote in message
> > news:YgsPc.286$Qk2.132@read3.inet.fi...
> >[color=darkred]
> >>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[/color][/color][/color]
ID.[color=blue][color=green][color=darkred]
> >>
> >>select
> >>min(value),
> >>max(value),
> >>max(value)-min(value)
> >>from tablename
> >>where date_ >= '2004-01-01'
> >>and date_ <= '2005-01-01'
> >>group by id;[/color]
> >
> >
> >[/color][/color]

re: Select Query Help

Got2Go wrote:
[color=blue]
> 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 ?[/color]

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.