I need help on date subtraction

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I have a table that looks like the following:

ID ProgDate
1 1-1-04
1 1-15-04
1 2-4-04
2 1-12-04
2 1-29-04

I need to find out the number of days difference per
ID,i.e. for ID 1:
(1-15-04)-(1-1-04)
(2-1-04) - (1-15-04) and so on.
Any idea how can I accomplish this?
thank you
 
I have a table that looks like the following:

ID ProgDate
1 1-1-04
1 1-15-04
1 2-4-04
2 1-12-04
2 1-29-04

I need to find out the number of days difference per
ID,i.e. for ID 1:
(1-15-04)-(1-1-04)
(2-1-04) - (1-15-04) and so on.
Any idea how can I accomplish this?
thank you
Click to expand...

I'd use a Self Join query or a Subquery and the DateDiff function. If
these are the only two fields that you have, it's going to be a bit
complicated, since there is no intrinsic order to an Access table -
there is no "next record" or "previous record".

A couple of questions: What do you want to do at the end of a run? I
can see that DateDiff("d", #1-1-04#, #1-1-04#) would give you the time
elapsed for the second record - but what value do you want to see for
the first? NULL? What if there's only one date for an ID?

AS WRITTEN, you could try

SELECT T1.ID, T1.ProgDate AS DateStart, T2.ProgDate AS DateEnd,
DateDiff("d", DateStart, DateEnd) AS Elapsed
FROM yourtable AS T1
INNER JOIN yourtable AS T2
ON T1.ID = T2.ID
WHERE T2.ProgDate = DMin("[ProgDate]", "[yourtable]", "[ID] = " &
T1.ID & " AND [ProgDate] > #" & T1.ProgDate & "#");
 
I have a table that looks like the following:

ID ProgDate
1 1-1-04
1 1-15-04
1 2-4-04
2 1-12-04
2 1-29-04

I need to find out the number of days difference per
ID,i.e. for ID 1:
(1-15-04)-(1-1-04)
(2-1-04) - (1-15-04) and so on.
Any idea how can I accomplish this?
thank you
Click to expand...

Perhaps something like this will help:

SELECT TableName.ID, TableName.ProgDate,
Abs(DateDiff("d",[ProgDate],DMax("[ProdDate]","TableName","[ID] = " &
[ID] & " and [ProgDate]< #" & [ProgDate] & "#"))) AS Difference
FROM TableName
ORDER BY TableName.ID, TableName.ProgDate;

Change the table and field names as needed.
 
These dates are for progress notes that are being dectated.
I need to see the number of days between the dates to be
ploted later on, to show us how much it is taken to type
these notes.
-----Original Message-----
I have a table that looks like the following:

ID ProgDate
1 1-1-04
1 1-15-04
1 2-4-04
2 1-12-04
2 1-29-04

I need to find out the number of days difference per
ID,i.e. for ID 1:
(1-15-04)-(1-1-04)
(2-1-04) - (1-15-04) and so on.
Any idea how can I accomplish this?
thank you
Click to expand...

I'd use a Self Join query or a Subquery and the DateDiff function. If
these are the only two fields that you have, it's going to be a bit
complicated, since there is no intrinsic order to an Access table -
there is no "next record" or "previous record".

A couple of questions: What do you want to do at the end of a run? I
can see that DateDiff("d", #1-1-04#, #1-1-04#) would give you the time
elapsed for the second record - but what value do you want to see for
the first? NULL? What if there's only one date for an ID?

AS WRITTEN, you could try

SELECT T1.ID, T1.ProgDate AS DateStart, T2.ProgDate AS DateEnd,
DateDiff("d", DateStart, DateEnd) AS Elapsed
FROM yourtable AS T1
INNER JOIN yourtable AS T2
ON T1.ID = T2.ID
WHERE T2.ProgDate = DMin("[ProgDate]", "[yourtable]", "[ID] = " &
T1.ID & " AND [ProgDate] > #" & T1.ProgDate & "#");



.
Click to expand...
 
These dates are for progress notes that are being dectated.
I need to see the number of days between the dates to be
ploted later on, to show us how much it is taken to type
these notes.
Click to expand...

So I gathered. But that does not answer my questions.

Again: what is the timelag that you wish to see for the first record
for an ID?

What timelag would you want to see for an ID which has only one
record?

Did the DateDiff() expression I suggested work for you? If not, what
did you get?
 
Back
Top