Dynamics Community Forum Thread Details

How to track the status reason changed in Opportunity using Audit and MS SQL

Hi Friends ,

I need a report to fetch the opportunity status changed by , changed date and some column of opportunity field using MS SQL query.

SELECT O.AccountIdName as Account, O.modifiedbyname , A.ChangeData,

O.createdon , O.modifiedon, O.createdbyname, O.modifiedbyname ,O.StatusCode , A.Action,

CASE

WHEN O.StatusCode = 1 THEN 'Progress'
WHEN O.StatusCode =2 THEN 'Hold'
WHEN O.StatusCode =3 THEN 'Won'
WHEN O.StatusCode =4 THEN 'Cancelled'
WHEN O.StatusCode =5 THEN 'Out Sold'
WHEN O.StatusCode = 240220000 THEN 'Completed Won'
WHEN O.StatusCode = 240220001 THEN 'Completed Won'
WHEN O.StatusCode = 240220002 THEN 'COMPLTED LOST'
WHEN O.StatusCode =240220002 THEN 'COMPLETED WON DEFEND'
END

AS [Status Reason]

FROM AUDIT A
INNER JOIN Opportunity O ON A.objectid = O.opportunityid

WHERE A.Action IN (2) AND o.StatusCode IN (240220002,240220003,240220000,240220001,1,2,3,4)
GROUP BY AccountIdName as Account, O.modifiedbyname , A.ChangeData,
O.createdon , O.modifiedon, O.createdbyname, O.modifiedbyname ,O.StatusCode , A.Action, O.StatusCode

ORDER BY ModifiedOn DESC

In this query it return all the data which are updated and have status code .

I dont know how i get only thoes data whoes status has been changed.

Please help

Hi WindyMill

I''m busy with the same requirement just with leads instead of accounts/opportunities...

In your where clause replace the "AND o.StatusCode IN (240220002,240220003,240220000,240220001,1,2,3,4)" with "AND A.AttributeMask LIKE '%54%'"

*remove the "" (double quotation marks)

AttributeMask column 54 is the StatusReason column within the Audit entity so you exclusively telling SQL to only return these updates

I hope this helps?

In my case the users want to see the status reason update per month on all active leads - so I would probably have to write 2 queries, 1 to pull all the lead records and 2 to only explicitly call the status reason updates in order to update the monthly columns

cheers

Quick Links

Leaderboard