Finding the date difference (days) between rows in a database table

You just need to subtract one date from the other,

without your exisitng code or database structure it is difficult to proceed - please post some info

hi david,
If u want find no of miles on any day then u can
1)select miles  from tablename where date=#date#
if ur main intention is to find diff of dates then u can use datediff(d,date,date)
If are still unclear plz comment on this.I can send u the code

Use Datediff() function!

hi david
'this should work
rsdate.Open "Select sum(miles) from datetable where date between #12/3/01# and #12/4/01#", conSims, adOpenKeyset, adLockReadonly

u can substitute date with variables containing dates of ur choice

Sorry about the confusion: The table looks something like this: (mm/dd/yyyy). The data starts on 1/1/2000

Date               Miles
1/1/2000           1000
1/2/2000           1200
1/3/2000           1300
1/4/2000           2000

Miles per day 1/1/2000 = 0
Miles per day 1/2/2000 = 200
Miles per day 1/3/2000 = 100
Miles per day 1/4/2000 = 700

Thanks
David

david,
I did it crating a table of same type and it worked,my solution is above.
If u want it for any day then
use use select miles from tablename where date=#date#

if u want in between dates then my above solution should work

You can do it via a single sql statement

ssql = "SELECT tbl1.dDate as vDate, tbl1.miles-(select top 1 tbl2.miles  from tblmiles as tbl2 where cdate(tbl2.dDate) <cdate( tbl1.dDate) Order By cdate( tbl2.dDate)  DESC) AS vMiles
FROM tblMiles AS tbl1
WHERE (((CDate([tbl1].[ddate]))>(Select min(cdate(tbl3.ddate)) as expr1 from tblmiles as tbl3))) Order By cdate(tbl1.ddate)"

create your ado recordset using the string ssql to open recordset - then you will have a recordset with fields

rs!vDate & rs!vMiles

containing the date and the mileage for that date - you then move through the recordset, with the calculation being done for you.

NOTE - I CHANGED field date to be called ddate - date is a reserved word & could cause problems.

note

ssql = "SELECT tbl1.dDate AS vDate, tbl1.miles-(select top 1 tbl2.miles  from tblmiles as tbl2 " & _
" where cdate(tbl2.dDate) <cdate( tbl1.dDate) Order By cdate( tbl2.dDate)  DESC) AS vMiles " & _
" FROM tblMiles AS tbl1 " & _
" WHERE (((CDate([tbl1].[ddate]))>(Select min(cdate(tbl3.ddate)) " & _
" as expr1 from tblmiles as tbl3))) Order By cdate(tbl1.ddate)"

Thanks, we're on our way!

I would like to get the data into a Miles/day column in the same table. The Miles per day is set to zero for the first date and then calculated from there...

Date               Miles       Miles/day
1/1/2000           1000         0
1/2/2000           1200         200
1/3/2000           1300         100
1/4/2000           2000         700
1/9/2000           2100         20

Miles per day 1/1/2000 = 0
Miles per day 1/2/2000 = 200
Miles per day 1/3/2000 = 100
Miles per day 1/4/2000 = 700
Miles per day 1/9/2000 = 20

Last entry = 100 miles travelled in 5 days = 20

I've increased the points!
Thanks
David

I don't think the new column 'milesperday' would help you. because this column can be calculated from other data.

This time you add a entry for date 1/9/2000, that means the date may not be continuously. so the routine to calculate 'milesperday' should change to:
milesperday = miles - miles for pervious record
or milesperday = miles - miles (while date is the minimum in all the records)

so the sql statement changes accordingly(for Access):
Select x.date, (x.miles - y.miles) As milesperday
  From usrtbl x, usrtbl y
 Where y.date = (Select max(date)
                   From usrtbl
                  Where date < x.date)
    Or y.date = (Select date
                   From usrtbl
                  Where date = x.date
                    And x.date = (Select min(date)
                                    From usrtbl))

user this sql statement to create a query then you can get milesperday anytime you want.

hope this can help you.

B.rgds, King

Thanks for your help everyone....

I've increased the points to 200 if you can give me the whole code for an Access Database to get the miles per day into the original table. Miles per day is calculated from the miles difference divided by the date difference for consecutive dates in an unsorted table, with the earliest record showing N/A or zero.

The requirement is to get the miles per day calculated for each date and for each interval, not from the earliest date.

Sorry about the confusion.....

Thanks

David

David

Hope I understood what you want. following is the sql statement to update mileperday.

Update usrtbl AS x, usrtbl AS y
   Set x.MilesPerDay = x.Miles - y.Miles
 Where y.date = (Select max(date)
                   From usrtbl
                  Where date < x.date)
    Or y.date = (Select date
                   From usrtbl
                  Where date = x.date
                    And x.date =(Select min(date)
                   From usrtbl))

I have tested it and it worked properly.

Try and tell me the result.

B.rgds, King

Thanks King.

I am a novice in this and I need some pointers in order to get this working in my VB application.

My datatable is called "tblTest" . This table is queried to produce "adoPrimaryRS". I guess this is usrtbl in your code.

Here is how I started to enter the code...

Dim x As Recordset
Set x = New Recordset
Dim y As Recordset
Set y = New Recordset
adoPrimaryRS.Update = x
adoPrimaryRS.Update = y
Set x.MilesPerDay = x.Miles - y.Miles

VB didn't like
Where y.date = (Select max(date) ....
I don't have a clue how to proceed.....

Please let me have your comments :-)

Thanks

David


VB doesn't like the next line, which I modified to
adoPrimaryRS.update as x,adoPrimaryRS.update as y

King, Please ignore the last two lines in my comment

"VB doesn't like the next line, which I modified to
adoPrimaryRS.update as x,adoPrimaryRS.update as y "

Thanks

David

Private Sub Form_Load()

    Dim gADOconn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Dim dLast As Double
    Dim bFlag As Boolean

    Set gADOconn = New ADODB.Connection
    Set rs = New ADODB.Recordset


'depends what version of jet you've got
    gADOconn.Provider = "Microsoft.Jet.Oledb.4.0"

'your database is called db1.mdb & is in root of c in this case

    gADOconn.Open ("c:\db1.mdb")
   
    rs.Open "select * from tblTest order by cdate(date)", gADOconn, adOpenKeyset, adLockPessimistic
   
    Do Until rs.EOF
   
       
        If bFlag Then rs.Fields("miles/day") = rs.Fields("miles") - dLast
        dLast = rs.Fields("miles")
        bFlag = True
       
        rs.Update
       
        rs.MoveNext
   
    Loop
   
    rs.Close
    gADOconn.Close
   
    Set rs = Nothing
    Set gADOconn = Nothing

End Sub

I haven't tried Deighton's code yet, but it seems to me that it would return the miles difference between consecutive dates, and not the miles per day between consecutive dates. Also will it "blow up" on the earliest date or return a null....

Points increased for the aggravation!

David

to do updating using VB ado objects, there are several ways. for Access, I think the easiest way is to just execute the sql statement.

here is some sample code.

Dim conn As ADODB.Connection 'define a connection
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
'open ado connection
conn.Open "Provider=Microsoft.Jet.OLEDB.3.51;" & _
          "Persist Security Info=False;" & _
          "Data Source=C:\mytbl.mdb"  'c:\mytbl is where and what is your database

Dim sqlstr As String
'transfer sqlstatement to a string value
sqlstr = "Update usrtbl AS x, usrtbl AS y" & vbCrLf & _
         "   Set x.MilesPerDay = x.Miles - y.Miles" & vbCrLf & _
         " Where y.date = (Select max(date)" & vbCrLf & _
         "                   From usrtbl" & vbCrLf & _
         "                  Where date < x.date)" & vbCrLf & _
         "    Or y.date = (Select date" & vbCrLf & _
         "                   From usrtbl" & vbCrLf & _
         "                  Where Date = x.Date" & vbCrLf & _
         "                    And x.date =(Select min(date)" & vbCrLf & _
         "                   From usrtbl))"

'execute sql statement
conn.Execute sqlstr

'close connection
conn.Close

Thanks King...

I have got the code to work with the Database and appropriate table. The calculated values don't make sense at present. I will work on it tomorrow... Will let you know how I get on ...

David ...

Hi DrD,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Split points between: deighton and KingSun

DrD, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer

Unfortunately, the responses provided a solution to finding the miles difference between consecutive records, but not the miles per day which was what was required. This involved finding the miles AND dates between consecutive records and then performing a calculation. There would also need to be consideration of EOF and BOF conditions..

I eventually solved the problem by populating an array using VB instead of trying to derive a suitable SQL statement, which is what the responses seem to be focussing on.

I repeated my requirements a couple of times, but eventually gave up.

However, there was an ambiguity in the question, and I think it would be fair to award the points equally between deoghton and kingsun as you suggest..

Thanks to all of you...

David

Thanks for the input David.  A Moderator will take care of this in about 7 days.  -- Dan

Per recommendation, force-accepted.

Netminder
CS Moderator

deighton: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20335197