SYMPTOMS
MSSQL script that provides a list of the documents last tagged with specific tags by a specific user.CAUSE
Not applicable
RESOLUTION
USE [ZyLABLegalReview]
SELECT MatterTable.id, MatterTable.family, MatterTable.name, MatterTable.[TAG_NAME],
Mat.ExecutionDate, Mat.Id, Mat.UserName, AuditDoc.RecordId FROM [dbo].[M_####] MatterTable
JOIN
(SELECT DocumentId, MAX(RecordId) AS RecordId FROM [ZyLABAudit].[dbo].[M_####_Document] GROUP BY DocumentId) AuditDoc ON AuditDoc.DocumentId = MatterTable.id
JOIN
SELECT MatterTable.id, MatterTable.family, MatterTable.name, MatterTable.[TAG_NAME],
Mat.ExecutionDate, Mat.Id, Mat.UserName, AuditDoc.RecordId FROM [dbo].[M_####] MatterTable
JOIN
(SELECT DocumentId, MAX(RecordId) AS RecordId FROM [ZyLABAudit].[dbo].[M_####_Document] GROUP BY DocumentId) AuditDoc ON AuditDoc.DocumentId = MatterTable.id
JOIN
(SELECT SearchId, MAX(ExecutionDate) AS ExecutionDate, Id, UserName, RecordType FROM [ZyLABAudit].[dbo].[M_####] GROUP BY SearchId, UserName, RecordType, Id) Mat ON Mat.Id = AuditDoc.RecordId
WHERE MatterTable.reviewed = 1 AND Mat.RecordType = 1 AND
MatterTable.[TAG_NAME] = 1 AND /* define specific tag */
Mat.UserName = [USER_NAME_ID] /* define specific user */
WHERE MatterTable.reviewed = 1 AND Mat.RecordType = 1 AND
MatterTable.[TAG_NAME] = 1 AND /* define specific tag */
Mat.UserName = [USER_NAME_ID] /* define specific user */
Where #### is the Legal Review Matter ID, TAG_NAME is the name of the tag applied to document(s), and USER_NAME_ID is the ID of the user who applied the tag.
APPLIES TO
3.11, 3.12
Comments
0 comments
Please sign in to leave a comment.