DateDiff on dates in the same column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am not sure if this can be done but...
I need to get the difference from dates in the same column. The dates
represent when something has been updated.

Ticket UpdatedTime
AAA 5/17/05 0
AAA 5/23/05 6
AAA 5/26/05 3

So above it was open on 5/17 & someone updated it on 5/23 6 days passed and
then on 5/26 someone updated it again and 3 more days had passed.

I do have a Opentime field and I can do a datediff against the UpdatedTime
field but the difference in days is as a whole and not when someone has made
an update.

Ticket OpenTime UpdatedTime
AAA 5/17/05 5/17/05 0
AAA 5/17/05 5/23/05 6
AAA 5/17/05 5/26/05 9

Thanks for any suggestions
 
Heather said:
I am not sure if this can be done but...
I need to get the difference from dates in the same column. The dates
represent when something has been updated.

Ticket UpdatedTime
AAA 5/17/05 0
AAA 5/23/05 6
AAA 5/26/05 3

So above it was open on 5/17 & someone updated it on 5/23 6 days passed and
then on 5/26 someone updated it again and 3 more days had passed.

I do have a Opentime field and I can do a datediff against the UpdatedTime
field but the difference in days is as a whole and not when someone has made
an update.

Ticket OpenTime UpdatedTime
AAA 5/17/05 5/17/05 0
AAA 5/17/05 5/23/05 6
AAA 5/17/05 5/26/05 9
Click to expand...


Try this kind of calculated field:

Diff: DateDiff("d", (SELECT Max(X.updated) FROM thetable As
X WHERE X.ticket = thetable.Ticket AND X.Updated <
thetable.updated), updated)
 
I am not sure if this can be done but...
I need to get the difference from dates in the same column. The dates
represent when something has been updated.

Ticket UpdatedTime
AAA 5/17/05 0
AAA 5/23/05 6
AAA 5/26/05 3

So above it was open on 5/17 & someone updated it on 5/23 6 days passed and
then on 5/26 someone updated it again and 3 more days had passed.

I do have a Opentime field and I can do a datediff against the UpdatedTime
field but the difference in days is as a whole and not when someone has made
an update.

Ticket OpenTime UpdatedTime
AAA 5/17/05 5/17/05 0
AAA 5/17/05 5/23/05 6
AAA 5/17/05 5/26/05 9

Thanks for any suggestions
Click to expand...

You'll need a "Self Join" query. Create a query adding the table to
the query grid twice; join the two instances by [Ticket], and put a
criterion on the second instance of UpdatedTime such as
[firstinstance].[UpdatedTime]
Click to expand...

I'm not sure I understand your second question though. "Difference in
days is as a whole"???

John W. Vinson[MVP]
 
Thank You

Marshall Barton said:
Try this kind of calculated field:

Diff: DateDiff("d", (SELECT Max(X.updated) FROM thetable As
X WHERE X.ticket = thetable.Ticket AND X.Updated <
thetable.updated), updated)
Click to expand...
 
Back
Top