Hello,
I am trying to write a query that lists only the most current record for each provider ID. The query does not work when 2 statuses have the same effective date. In this case I need the Max date and the Max Order.
I have tried several iterations for the query, using a top 1, a subquery for order etc... But nothing seems to work.
Below is the query that selects the most current date record and the results.
SELECT A.ProviderID, A.StatusUpdateID, A.Status, A.StatusDate, A.Notes, B.StatusCategory, B.StatusName, B.Order
FROM tblStatus AS B RIGHT JOIN tblProviderStatus AS A ON B.StatusID = A.Status
WHERE( ((A.StatusDate)=(Select MAX(StatusDate) From ProviderStatus As X
Where X.ProviderID = A.ProviderID)))
ORDER BY A.ProviderID, A.StatusDate DESC , B.Order DESC;
ProviderID StatusUpdateID Status StatusDate Notes StatusCategory StatusName Order
794 1266 Sent to Contractor 3/21/2006 Sent to Contractor to pursue. Open Sent to Contractor 2
794 1265 Form Received 3/21/2006 Form Received Open Form Received 1
What I need is the first record to display (order = 2)
Any help would be appreciated.
I am trying to write a query that lists only the most current record for each provider ID. The query does not work when 2 statuses have the same effective date. In this case I need the Max date and the Max Order.
I have tried several iterations for the query, using a top 1, a subquery for order etc... But nothing seems to work.
Below is the query that selects the most current date record and the results.
SELECT A.ProviderID, A.StatusUpdateID, A.Status, A.StatusDate, A.Notes, B.StatusCategory, B.StatusName, B.Order
FROM tblStatus AS B RIGHT JOIN tblProviderStatus AS A ON B.StatusID = A.Status
WHERE( ((A.StatusDate)=(Select MAX(StatusDate) From ProviderStatus As X
Where X.ProviderID = A.ProviderID)))
ORDER BY A.ProviderID, A.StatusDate DESC , B.Order DESC;
ProviderID StatusUpdateID Status StatusDate Notes StatusCategory StatusName Order
794 1266 Sent to Contractor 3/21/2006 Sent to Contractor to pursue. Open Sent to Contractor 2
794 1265 Form Received 3/21/2006 Form Received Open Form Received 1
What I need is the first record to display (order = 2)
Any help would be appreciated.