Audit trail accumulates all activities happened on the system or movements happened on the item. It is not that easy to extract from the audit trail the last movement of all the items, unless you individually query on each item and get the max date then transfer it to another table, a temporary maybe, then go to the next item and repeat the process until you have the last item. But I found a way to query it on just a single query string:
SELECT A.ProductID, B.Description,
LTRIM(RIGHT(MAX(CONVERT(VARCHAR(19), A.AuditTime, 120) +
RIGHT(REPLICATE(' ',100) + Activity,100)),100)) AS LastActivity FROM AuditTrail A INNER JOIN Products B ON A.ProductID= B.ProductID GROUP BY A.ProductID, B.Description
Here is how this works:
- Convert the Audit Time to YYYY-MM-DD HH:NN:SS format, this will ensure the order of activities according to time it was written.
- RIGHT(REPLICATE(' ',100) + Activity,100)) This will put leading spaces on activity to make the string a 100 character. eg. '10 items sold' will become ' 10 items sold' with 87 leading spaces.
- Concatenate the 19 character datetime to the 100 character Activity making it a sum of 119 characters
- Get the MAX value of the 119 character field that we have created. This will get the Max value we want since it will be alphabetically arranged.
- Now its time to remove the date and time that we don't want to appear then trim again the leading spaces we added. this is the function of LTRIM(RIGHT( ---,100)) where the function right gets the 100 character activity we created ant LTRIM removes leading spaces.
No comments:
Post a Comment