Using SQL 1

[Poweruser99]

I have a whole bunch of records I am developing for a water meter reading program.
Basically I record the water meter reading for a particular house on a monthly basis. Also for that month the rate is different. To calculate the full rate I need to take the difference of the current meter reading from the last months water reading for that house.

I have no idea how to do this. SQL is not my area of strenth.
Please help!!

 

[PHV]

Without your Table(s) schema nor input samples nor expected result, it's hard to say ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Poweruser99]

Here is the schema.

tblReadings (In this table I record the reading and the unit number along with the ReadDate.

ReadID Unit Number ReadDate Reading
1 699 01-Sep-04 3.66
2 699 01-Oct-04 2
3 698 01-Sep-04 2
4 698 01-Oct-04 2

TblRate
WaterRate GracePeriod CurrentReadingDate
3.2 2 9/1/2004
4.2 3 10/1/2004
5.2 6 2/2/2002

The Waterrate is used to calculate the payment. Basicaly the formula is:
WaterRate * ( Last months Reading - Current Reading)
Hope I can get somewhere with this.

 

[PHV]

And with your posted data, what is the expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[alvechurchdata]

SQL is not my area of strength.

Do you have to use SQL? SQL treats tables as unordered sets of data and your problem relies on the chronological relationship between data. You might do better to work through the recordset row by row in program code.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Poweruser99]

I think I got the SQL for this. This is what I put.

UPDATE tblToBill
SET new.FinalChargeRate = tblToBill.WaterRate * (new.Reading/old.Reading)
FROM tblToBill new, tblToBill old
WHERE new.[Unit Number] = old.[Unit Number]
AND new.ReadDate = old.ReadDate;

However I get this error:
"Syntax error(missing operator) in query expression
tblTobill.waterrate* (new.Reading/old.Reading)
FROM tblToBill new

 

[Golom]

I don't think that's going to cut it for you.

You are combining SELECT syntax by using a FROM clause with UPDATE syntax that doesn't have one. That's giving you the syntax error.

Your Where clause is going to select the same record in the two versions of the table which means the calculation reduces to

SET new.FinalChargeRate = New.WaterRate * 1

The SQL probably needs to be

UPDATE tblToBill new INNER JOIN tblToBill old
       ON New.[Unit Number] = Old.[Unit Number]

SET   new.FinalChargeRate =  New.WaterRate * (new.Reading-old.Reading)

WHERE  New.ReadDate > Old.ReadDate

       AND Old.ReadDate = 
       (Select MAX(ReadDate) From tblToBill X
        Where X.[Unit Number] = New.[Unit Number]
              AND X.ReadDate < New.ReadDate)

I assumed that you want to take the difference between readings rather than their ratio.