Time difference from end time of record to now.

[Gareth Jones]

Hi all,

I have a database that records issues and incidents based on different priorities.

I am trying to write a query to show the time difference since the last incident depending on priority.

I.e. difference between End date/time and Now where priority = 1 and the same where the priority = 2.

What I have so far is:

Datediff: DateDiff("n",[date resolved],Date())

Timediff: DateDiff("n",[time resolved],now())

Total mins: ([timediff]+[datediff])

I am having difficulty in making this work by incorporating the priorites however.

Can this be done?

Thanks
Gareth

[Rabbit]

I don't know what you mean. Some sample data would go a long way.

[Gareth Jones]

I've got it. I made it work by subtracting the time from the Date/Time resolved of the last incident to Now()

I sorted it descending so the latest incident was at the top and then only returned the top 1 row.

[NeoPa]

That's good thinking Gareth but will cause issues for multiple groups.

What you need to be looking at is a GROUPed query with aggrgates where you use Min() of the value of (Now() - [DateinRecord]) and GROUP BY [Priority].