Determine Max Age of Unresolved Items For Last 30 Dys 1

[Du2good]

Can I get this?
MyTable has items [ItemID] added to it daily [ReceivedDt].
ItemID's may be resolved [ResolvedDt], on or after the [ReceivedDT].
Looking at the unresolved items, can I find out what the oldest item was, that was not yet been resolved, for each day there was an item that was resolved?
I believe a sub query is in order here but havn't had any success.

 

[PHV]

but havn't had any success
What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Du2good]

trying to use this subquery:
SELECT Max([ResolveDt]-[ReceiptDt]) AS Age, Max(MyTable.ReceiptDt) AS MaxOfReceiptDt, MyTable.ResolveDt, Count(MyTable.ItemID) AS CountOfItemID
FROM MyTable
GROUP BY MyTable.ResolveDt
HAVING (((MyTable.ResolveDt) Is Not Null));

With this outer query:
SELECT Max([ResolveDt]-[ReceiptDt]) AS Age, [resolveDt]-[receiptDt] AS MyRcptDt, Max(MyTable.ReceiptDt) AS MaxOfReceiptDt, MyTable.ResolveDt, Count(MyTable.ItemID) AS CountOfItemID
FROM MyTable
GROUP BY [resolveDt]-[receiptDt], MyTable.ResolveDt;


but I'm in over my head. At this point I'm not even sure which field should have the Where clause. It's a mess in process.

Thank you

 

[PHV]

A starting point:

SELECT E.ResolveDt,MIN(O.ReceiptDt) AS OldestNotYetResolved,E.ResolveDt-MIN(O.ReceiptDt) AS Age
FROM MyTable O
,(SELECT DISTINCT ResolveDt FROM MyTable WHERE ResolveDt IS NOT NULL) E
WHERE O.ReceiptDt<=E.ResolveDt AND (O.ResolveDt IS NULL OR O.ResolveDt>E.ResolveDt)
GROUP BY E.ResolveDt

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Du2good]

Thank you again PHV.

I'm surprised how bracketing affects the subquery, my actual table had poor design with spaces in the field names.
When I tried your solution, and added brackets to the field [Resolved Dt], Access gave me an error.
With a temp table, and field name as [ResolvedDt], your solution worked great.
Thank you.