Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

Current meter reading query

Good morning,

As part of a db that uses meters (odometer, hourmeter, etc.), I have a table as follows:

TblMeterRead
MeterReadID - pk
MeterRead
ReadDate

I'm attempting to set up a 'rollover count' that keeps track of any meter rollovers (re-sets to zero) and in order to do so I need a "current meter reading" field. Logic would dictate that the current reading is the last meter read but I need some assistance in wording SQL to set up this field in a query.

Thank you

Directorz
0
directorz
Asked:
directorz
  • 7
  • 4
  • 2
  • +2
2 Solutions
 
peter57rCommented:
Hello directorz,

Is there no field which identifies the meter?

Pete
0
 
Rey Obrero (Capricorn1)Commented:
try this,
you will need a meterID for the query

SELECT tblMeterRead.MeterID, Max(tblMeterRead.MeterRead) AS Current_MeterReading, Max(tblMeterRead.ReadDate) AS MaxOfReadDate
FROM tblMeterRead
WHERE (((tblMeterRead.MeterRead)>0))
GROUP BY tblMeterRead.MeterID;

0
 
directorzAuthor Commented:
Hi Pete and Capricorn1,

I've uploaded a sample for a better visual. Please see geocities.com. Username is directorz2002.  Password is ticket.

Just a few notes: The MeterMax field is a user defined field. Different meters have different maximum readings ie; 9,999 hours or 999,999 miles etc. The RolloverCount uses this field to determine a rollover. Because RolloverCount is an attribute of the meter, I think I'm still in compliance with normalization.

Capricorn1...if I associate a rollover count with a current reading, as you have it, I think the calcs will not be accurate. Could you elaborate.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
GRayLCommented:
Try searching EE - body:"meter reading" - I got 18 hits and I think your answer is in there.
0
 
directorzAuthor Commented:
Your code is calling the maximum meter read as the current meter read. But, if the meter reset to zero, all bets are off.

So somehow, I  need to incorporate the fact that if the current meter read is less than the last meter read, the meter has rolled over and the rollover count equals the current value of the rollover count field +1. Let's say the meter rolls once.  The current meter reading will then be the sum of  the current reading + (MeterMax * 1). If it rolls twice, the current reading will be sum of  the current reading + (MeterMax * 2). You may see the dilemma.

GRayL,
I did a search and did not find too much, but I appreciate it.

Directorz
0
 
Rey Obrero (Capricorn1)Commented:
<Your code is calling the maximum meter read as the current meter read. But, if the meter reset to zero, all bets are off>
No.

that is why this clause

WHERE (((tblMeterRead.MeterRead)>0))  
0
 
shanesuebsahakarnCommented:
If you have a meter ID field, this will return the latest meter reading:

SELECT * FROM tblMeterRead WHERE ReadDate=(SELECT Max(A.ReadDate) FROM tblMeterRead As A WHERE A.MeterID=tblMeterRead.MeterID)

Simply join this query to the meter table and then add the rollover field*rollover value to the current meter reading.
0
 
directorzAuthor Commented:
Capricorn1,
I may not be seeing this right, please correct me if I am wrong. Your code selects the highest reading, greater then zero and makes it the current reading. If this is true, I don't understand how a rollover is accurately handled.

Oh no, here I go again. Let's say we're nearing the meter maximum of 9999. So, on October 1, I read 9980. October 2nd   I read 9995. Time goes on, lah-lah-lah-lah (with background music) and on October 5th, I read 10. Will your code result with 9995 or 10?
0
 
Rey Obrero (Capricorn1)Commented:
oh, it will yield 9995, i think i totally misunderstood your problem.
this will give the last reading

SELECT tblMeterRead.MeterID, Max(tblMeterRead.ReadDate) AS MaxOfReadDate, Last(tblMeterRead.MeterRead) AS LastOfMeterRead
FROM tblMeterRead
GROUP BY tblMeterRead.MeterID


how do you want to account for the rollover?
0
 
directorzAuthor Commented:
I think we're on the same page. I think the rollover needs to be handled by VB. Shanesuebsahakarn started me off with this concept. If the current meter reading is LESS THAN the last reading, then a rollover has occurred .

At the time of a rollover, I need to add "1" to the current value of the rollover field. In other words,
"the rollover count equals the current value of the rollover count field +1."

 Let's say the meter rolls once.  The current meter reading will then be the sum of the current reading + (MeterMax * 1). If it rolls twice, the current reading will be sum of  the current reading + (MeterMax * 2)....metermax being the maximum or highest reading the meter can show without rolling over to zero.

I think this will be a separate question, presented as a continuation of this one.
0
 
Rey Obrero (Capricorn1)Commented:
or if you have a RollOver field Update it to

RollOver=Rollover+1
everytime the difference between the last reading and the previous one is less than 0; meaning negative value (10-9995)
0
 
directorzAuthor Commented:
Shanesuebsahakarn,
I will followup with this. By keeping a 'rollovercount' field as a field in the table, am I  violating 3NF normalization rules. Should this be done at the form level. Just want to make sure when I start a new question...
0
 
shanesuebsahakarnCommented:
There are circumstances when you need to denormalise. This is probably one of them, given the nature of the data. You probably could calculate the number of rollovers, but it would be very, very tricky using SQL - you'd more than likely need VBA to do it in any case.
0
 
directorzAuthor Commented:
Capricorn1,
I'm showing yours as accepted but I need to split with shanesuebsahakarn...could have worked also

Thank you

Directorz  
0
 
directorzAuthor Commented:
As a continuation of this question - this may be a better way - I'll add 700 points. Using the previous tables and arrangement,  Anyway, I'm hung up on a rollover count. The concept is to get a rollover count and use it in determinining an 'actual' reading. Using a query, I'm thinking, as shanesuebsahakarn suggested in another thread, that the logic is if the current meter reading is LESS THAN the last meter reading, a rollover has occurred

The RolloverCount field value is increased by 1 each time this occurs. The RolloverCount field value is then multiplied by MeterMax and added to MeterRead. Jeez, I'm trying to be as clear as possible without confusing everyone.  

Anyone want to give this one a shot?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now