Calculate change in field value between records

Results 1 to 7 of 7

Thread: Calculate change in field value between records

  1. #1
    Join Date
    Dec 1969
    Posts
    61

    Default Calculate change in field value between records

    Hi, <BR>How can I create a column in a grid which calculates the change in a numeric or date value in a field from record to record? Consider a table which has a new record each day showing the amount of gas in a car&#039;s gas tank and the reading of the odometer. How could you make a grid that would show 1) the gas consumed each day and 2) the mpg achieved each day? The calculation is easy, but it involves creating a calculated field that refers to both the current record&#039;s value and the previous record&#039;s value. Does anyone have an idea how to do this? <BR><BR>I would really appreciate any ideas. <BR><BR>Thank you, <BR><BR>Charlie <BR>charlie@remotereporting.com <BR>www.remotereporting.com

  2. #2
    Join Date
    Dec 1969
    Posts
    96,118

    Default In SQL? Or in ASP?

    In ASP, of course, it&#039;s a piece of cake.<BR><BR>&#060;%<BR>Set RS = ...<BR><BR>priorValue = 0<BR>Do Until RS.EOF<BR>&nbsp; &nbsp; curValue = RS("Odometer")<BR>&nbsp; &nbsp; Response.Write "Odometer: " & curValue & ", difference " & (curValue-priorValue)<BR>&nbsp; &nbsp; priorValue = curValue<BR>&nbsp; &nbsp; ...<BR>&nbsp; &nbsp; RS.MoveNext<BR>Loop<BR>%&#062;<BR><BR>In SQL? Hmmm...dunno any way off top of my head except to do something similar in a Stored Procedure.<BR><BR>Hmmm...But if you needed the data in the DB, why not just calculate it and add it as you added each record???<BR><BR><BR>

  3. #3
    Chalie Guest

    Default RE: In SQL? Or in ASP?

    This should do the trick. Thanks very much for your help.

  4. #4
    Join Date
    Dec 1969
    Posts
    61

    Default value appears in wrong row

    This idea worked very well. However, the "difference" value is popping up one row below where it should. We sort the database by descending date. The difference is showing, in effect, how much fuel the car will burn tomorrow, not how much it has burned in the past day. I am scratching my head but can&#039;t see the way to shift the value to another row. Any ideas?<BR><BR>Thanks very much, this really is a breakthrough for me on this project.


  5. #5
    Join Date
    Dec 1969
    Posts
    96,118

    Default One easy fix...

    Well, the "trick" is to "remember" *all* the data of the current row, go get the reading from the next row, and *then* output the current row.<BR><BR>The easiest way to do that would be by using an array and GetRows, instead of do RS.MoveNext. <BR><BR>SO:<BR><BR>&#060;%<BR>Set RS = ...<BR><BR>records = RS.GetRows<BR><BR>For row = 0 To UBound(records,2)<BR>&nbsp; &nbsp; If row &#060;&#062; UBound(records,2) Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; difference = records( odometerColumn, row+1 ) - records( odometerColumn, row ) )<BR>&nbsp; &nbsp; Else<BR>&nbsp; &nbsp; &nbsp; &nbsp; &#039; ??? what do you do on last row???<BR>&nbsp; &nbsp; End If<BR>&nbsp; &nbsp; ... output the row ...<BR>Next<BR>%&#062;<BR><BR>This means using column numbers (field numbers) from the recordset instead of field names. Real easy if you *explicitly* do:<BR>&nbsp; &nbsp; &nbsp; &nbsp; Select field1, field2, ... odometer, fieldN, ...<BR>instead of just<BR>&nbsp; &nbsp; &nbsp; &nbsp; Select *<BR><BR>since then the order of the fields is clearly sitting there in front of you.<BR><BR>When I do that, I tend to *also* put in:<BR><BR>&#060;%<BR>CONST FLD_NAME = 0<BR>CONST FLD_ODOMETER = 1<BR>...<BR>%&#062;<BR><BR>That is, give pseudo-names to the fields, so that I can do<BR>&nbsp; &nbsp; &nbsp; &nbsp; records(FLD_ODOMETER,row)<BR>instead of using a simple number. The code is now self-documenting, a *huge* benefit if anybody ever has to change it in the future!<BR><BR>

  6. #6
    Join Date
    Dec 1969
    Posts
    61

    Default RE: One easy fix...

    Thanks, I actually started trying something like that already, using RS.movenext. Here is what I came up with:<BR><BR>do until RSVessel.EOF<BR>tdate = RSVessel("tdate")<BR>FOCurr = RSVessel("FO")<BR>RSVessel.MoveNext<BR><BR>If RSVessel.EOF = "False" Then DeltaFO = RSVessel("FO") - FOCurr else DeltaFO = "N/A" end if<BR>%&#062;<BR> <BR> &#060;tr&#062;<BR> &#060;td align="middle"&#062;&#060;/td&#062;<BR> &#060;td align="middle"&#062;&#060;%=tdate%&#062;&#060;/td&#062;<BR> &#060;td align="middle"&#062;&#060;%=FOCurr%&#062;&#060;/td&#062; <BR> &#060;td align="middle"&#062;&#060;%=DeltaFO%&#062;&#060;/td&#062;<BR> &#060;/tr&#062;<BR> &#060;% <BR> loop <BR>(small confession. This has nothing to do with cars, I just put it like that so my question could be understood. We track ships on our website (www.remotereporting.com), "FO" stands for Fuel Oil. We get daily reports from vessels at sea which include fuel level information.<BR><BR>This is how it looks so far: http://www.fleetrack.tzo.com/Fleetrack2/bunkerexp.asp<BR><BR>We are now doing this type of query in Access, but as the tables get bigger, we are seeing sloooow responses. So I am wanting to do the analysis in ASP.<BR><BR>I am just now trying to expand the conditional statement so that DeltaFo = "N/A" in other situations--when DeltaFo &#060; 0, and when Isnumeric(DeltaFO) is false (the data coming from vessels at sea is not always perfect). It seems tricky to run the if..then statement inside the loop--every time I add conditions the browser returns an error.<BR><BR>I would appreciate having your details on file if you are available for freelance consulting. My business is quickly running past my limited experience and we will need a good database person in the future.<BR><BR>Thanks so much,<BR><BR>charlie@remotereporting.com

  7. #7
    Join Date
    Dec 1969
    Posts
    96,118

    Default only freebies...

    I don&#039;t do paid consulting becuz (1) I&#039;d have to get permission from my employer and (2) I&#039;d have to get a business license, liability insurance, etc., etc., and it wouldn&#039;t be worth it at this point.<BR><BR>My home email address (for now) is junco@premier1.net. If we move that, you can always find us via www.JuncoJunction.com<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •