Help with computing date\Time between previous record

D

Dave

I am trying to calculate how long it has been from a previous visit. I am
tracking visit dates for customers. So I am sorting visit dates by customer.
I want to look at a record date and subtract the previous reocrds date from
it for all dates in the db.

So in the end there is another Field that shows how long it has been bewteen
customer visits?

Dave K
 
G

golfinray

Use in your query: Datediff("d",[firstdatefield],[seconddatefield])
The "D" is for days.
 
V

vanderghast

Bring the table TWICE, one will get an _1 appended to its name.

Change the query type to a Total query.

Join the two references through their clientID field.

Bring all the required fields from the first reference in the grid. Keep
GROUP BY under clientID and under the dateTimeStamp field, use LAST, or
FIRST, for any other (optionally required) field.

Bring only the dateTimeStamp field for the second table reference in the
grid, the _1 one, and change the GROUP BY to WHERE, and, under it, add the
criteria:

< [tableName].[dateTimeStampFieldName]


(tableName without the _1 in the criteria)

So, now, you made _1 the whole history (in relation of the not_1 reference).


Make a computed expression in a new grid's column:

[tableName].[dateTimeStamp] - MAX( [tableName_1].[dateTimeStamp]



That should do the job, since MAX recuperate only the maximum value of the
'said' history.


If you prefer to do it in SQL view:

SELECT a.clientID,
a.dateTimeStamp,
LAST(a.someOtherField),
a.dateTimeStamp)-MAX(b.dateTimeStamp)

FROM tableName As a INNER JOIN tableName AS b
ON a.clientID = b.clientID

WHERE b.dateTimeStamp < a.dateTimeStamp

GROUP BY a.clientID, a.dateTimeStamp



Sure, that does not show records having no previous record (for that
client). Modify the SQL statement like below, to get those:

SELECT a.clientID,
a.dateTimeStamp,
LAST(a.someOtherField),
a.dateTimeStamp)-MAX(b.dateTimeStamp)

FROM tableName As a LEFT JOIN tableName AS b
ON a.clientID = b.clientID
AND b.dateTimeStamp < a.dateTimeStamp

GROUP BY a.clientID, a.dateTimeStamp



where the INNER JOIN has become a LEFT JOIN, and the where clause included
in the join. The expression a.dateTimeStamp)-MAX(b.dateTimeStamp) will
return null for records without previous one.




Vanderghast, Access MVP
 
D

Dave

Thanks I will give this a try

Dave

vanderghast said:
Bring the table TWICE, one will get an _1 appended to its name.

Change the query type to a Total query.

Join the two references through their clientID field.

Bring all the required fields from the first reference in the grid. Keep
GROUP BY under clientID and under the dateTimeStamp field, use LAST, or
FIRST, for any other (optionally required) field.

Bring only the dateTimeStamp field for the second table reference in the
grid, the _1 one, and change the GROUP BY to WHERE, and, under it, add the
criteria:

< [tableName].[dateTimeStampFieldName]


(tableName without the _1 in the criteria)

So, now, you made _1 the whole history (in relation of the not_1 reference).


Make a computed expression in a new grid's column:

[tableName].[dateTimeStamp] - MAX( [tableName_1].[dateTimeStamp]



That should do the job, since MAX recuperate only the maximum value of the
'said' history.


If you prefer to do it in SQL view:

SELECT a.clientID,
a.dateTimeStamp,
LAST(a.someOtherField),
a.dateTimeStamp)-MAX(b.dateTimeStamp)

FROM tableName As a INNER JOIN tableName AS b
ON a.clientID = b.clientID

WHERE b.dateTimeStamp < a.dateTimeStamp

GROUP BY a.clientID, a.dateTimeStamp



Sure, that does not show records having no previous record (for that
client). Modify the SQL statement like below, to get those:

SELECT a.clientID,
a.dateTimeStamp,
LAST(a.someOtherField),
a.dateTimeStamp)-MAX(b.dateTimeStamp)

FROM tableName As a LEFT JOIN tableName AS b
ON a.clientID = b.clientID
AND b.dateTimeStamp < a.dateTimeStamp

GROUP BY a.clientID, a.dateTimeStamp



where the INNER JOIN has become a LEFT JOIN, and the where clause included
in the join. The expression a.dateTimeStamp)-MAX(b.dateTimeStamp) will
return null for records without previous one.




Vanderghast, Access MVP



Dave said:
I am trying to calculate how long it has been from a previous visit. I am
tracking visit dates for customers. So I am sorting visit dates by
customer.
I want to look at a record date and subtract the previous reocrds date
from
it for all dates in the db.

So in the end there is another Field that shows how long it has been
bewteen
customer visits?

Dave K
Click to expand...
Click to expand...
 
D

Dave

I have the SQL query you sent working, now how do i adjust the end result?

I want to compare the LED of the current record to the TCD of the next record.

How do I do that?

My present SQL:

SELECT a.[Item Number], a.[Serial Number], a.LSD, a.LED, Last(a.TCD) AS
LastOfTCD, [a].[LED]-Max(.[LED]) AS Days INTO FTFDays

FROM AOCTCA AS a
INNER JOIN AOCTCA AS b ON (a.[Serial Number] = b.[Serial Number]) AND
(a.[Item Number] = b.[Item Number])

WHERE (((b.LED)<[a].[LED]))

GROUP BY a.[Item Number], a.[Serial Number], a.LSD, a.LED;



vanderghast said:
Bring the table TWICE, one will get an _1 appended to its name.

Change the query type to a Total query.

Join the two references through their clientID field.

Bring all the required fields from the first reference in the grid. Keep
GROUP BY under clientID and under the dateTimeStamp field, use LAST, or
FIRST, for any other (optionally required) field.

Bring only the dateTimeStamp field for the second table reference in the
grid, the _1 one, and change the GROUP BY to WHERE, and, under it, add the
criteria:

< [tableName].[dateTimeStampFieldName]


(tableName without the _1 in the criteria)

So, now, you made _1 the whole history (in relation of the not_1 reference).


Make a computed expression in a new grid's column:

[tableName].[dateTimeStamp] - MAX( [tableName_1].[dateTimeStamp]



That should do the job, since MAX recuperate only the maximum value of the
'said' history.


If you prefer to do it in SQL view:

SELECT a.clientID,
a.dateTimeStamp,
LAST(a.someOtherField),
a.dateTimeStamp)-MAX(b.dateTimeStamp)

FROM tableName As a INNER JOIN tableName AS b
ON a.clientID = b.clientID

WHERE b.dateTimeStamp < a.dateTimeStamp

GROUP BY a.clientID, a.dateTimeStamp



Sure, that does not show records having no previous record (for that
client). Modify the SQL statement like below, to get those:

SELECT a.clientID,
a.dateTimeStamp,
LAST(a.someOtherField),
a.dateTimeStamp)-MAX(b.dateTimeStamp)

FROM tableName As a LEFT JOIN tableName AS b
ON a.clientID = b.clientID
AND b.dateTimeStamp < a.dateTimeStamp

GROUP BY a.clientID, a.dateTimeStamp



where the INNER JOIN has become a LEFT JOIN, and the where clause included
in the join. The expression a.dateTimeStamp)-MAX(b.dateTimeStamp) will
return null for records without previous one.




Vanderghast, Access MVP



Dave said:
I am trying to calculate how long it has been from a previous visit. I am
tracking visit dates for customers. So I am sorting visit dates by
customer.
I want to look at a record date and subtract the previous reocrds date
from
it for all dates in the db.

So in the end there is another Field that shows how long it has been
bewteen
customer visits?

Dave K
Click to expand...
Click to expand...
 
V

vanderghast

Maybe using a subquery is the easiest way, at this point (another
possibility would be to rank of LED values, per item number, thus rank-1 and
rank+1 would point to the previous and next record (in terms of 'date') ),
.... It is untested though ...


SELECT ..., ( SELECT c.tcd FROM aoctca AS c
WHERE c.[item number] = a.[item number]
AND c.[serial number]= a.[serial number]
AND c.led = (SELECT MIN(d.led) FROM aoctca AS d
WHERE d.[item number] = a.[item number]
AND d.[serial number]= a.[serial
number]
AND d.led > a.led ))

FROM ...



The inner most subquery returns the earliest date > a.led while the
subquery itself spot that record and return its tcd value.



May be quite slow, though.



Vanderghast, Access MVP


Dave said:
I have the SQL query you sent working, now how do i adjust the end result?

I want to compare the LED of the current record to the TCD of the next
record.

How do I do that?

My present SQL:

SELECT a.[Item Number], a.[Serial Number], a.LSD, a.LED, Last(a.TCD) AS
LastOfTCD, [a].[LED]-Max(.[LED]) AS Days INTO FTFDays

FROM AOCTCA AS a
INNER JOIN AOCTCA AS b ON (a.[Serial Number] = b.[Serial Number]) AND
(a.[Item Number] = b.[Item Number])

WHERE (((b.LED)<[a].[LED]))

GROUP BY a.[Item Number], a.[Serial Number], a.LSD, a.LED;



vanderghast said:
Bring the table TWICE, one will get an _1 appended to its name.

Change the query type to a Total query.

Join the two references through their clientID field.

Bring all the required fields from the first reference in the grid. Keep
GROUP BY under clientID and under the dateTimeStamp field, use LAST, or
FIRST, for any other (optionally required) field.

Bring only the dateTimeStamp field for the second table reference in the
grid, the _1 one, and change the GROUP BY to WHERE, and, under it, add
the
criteria:

< [tableName].[dateTimeStampFieldName]


(tableName without the _1 in the criteria)

So, now, you made _1 the whole history (in relation of the not_1
reference).


Make a computed expression in a new grid's column:

[tableName].[dateTimeStamp] - MAX( [tableName_1].[dateTimeStamp]



That should do the job, since MAX recuperate only the maximum value of
the
'said' history.


If you prefer to do it in SQL view:

SELECT a.clientID,
a.dateTimeStamp,
LAST(a.someOtherField),
a.dateTimeStamp)-MAX(b.dateTimeStamp)

FROM tableName As a INNER JOIN tableName AS b
ON a.clientID = b.clientID

WHERE b.dateTimeStamp < a.dateTimeStamp

GROUP BY a.clientID, a.dateTimeStamp



Sure, that does not show records having no previous record (for that
client). Modify the SQL statement like below, to get those:

SELECT a.clientID,
a.dateTimeStamp,
LAST(a.someOtherField),
a.dateTimeStamp)-MAX(b.dateTimeStamp)

FROM tableName As a LEFT JOIN tableName AS b
ON a.clientID = b.clientID
AND b.dateTimeStamp < a.dateTimeStamp

GROUP BY a.clientID, a.dateTimeStamp



where the INNER JOIN has become a LEFT JOIN, and the where clause
included
in the join. The expression a.dateTimeStamp)-MAX(b.dateTimeStamp) will
return null for records without previous one.




Vanderghast, Access MVP



Dave said:
I am trying to calculate how long it has been from a previous visit. I
am
tracking visit dates for customers. So I am sorting visit dates by
customer.
I want to look at a record date and subtract the previous reocrds date
from
it for all dates in the db.

So in the end there is another Field that shows how long it has been
bewteen
customer visits?

Dave K
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy previous record into form field 0
Dates 1
Counting in a query 4
Record AFTER latest by date an time, grouped by serial number 6
Recognize Innactivity between data time stamps 3
Excel PMT Function Help 2
Problem Sequentially Numbering Records 4
Date Help Request 1

Top