Question

I want to purge attachments for the bugs closed more than 6 months ago. How do I do that?

See Also:

• Deleting cases
• Deleting old cases
• Deleting old email attachments

Answer 1

Just add a join on Bug and change the WHERE clause to use the case's dtClosed instead of the date of the BugEvent

For FogBugz 7 and earlier:

MS SQL Server:

DELETE FROM Attachment WHERE ixAttachment IN
(SELECT Attachment.ixAttachment
        FROM Attachment
        INNER JOIN BugEvent
            ON Attachment.ixBugEvent = BugEvent.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBug = BugEvent.ixBug
        WHERE DATEDIFF(mm, Bug.dtClosed, GetDate()) > 6 AND fEmail = 1
)

MySQL:

DELETE FROM Attachment WHERE ixAttachment IN
(SELECT Attachment.ixAttachment
        FROM Attachment
        INNER JOIN BugEvent
            ON Attachment.ixBugEvent = BugEvent.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBug = BugEvent.ixBug
        WHERE (DATEDIFF(Bug.dtClosed, CurDate()) / 30 <= 6) AND fEmail = 1
)

For FogBugz 8 and later:

MS SQL Server:

DELETE FROM Attachment WHERE ixAttachment IN
(SELECT AttachmentReference.ixAttachment
        FROM AttachmentReference
        INNER JOIN BugEvent
            ON AttachmentReference.ixBugEvent = BugEvent.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBug = BugEvent.ixBug
        WHERE DATEDIFF(mm, Bug.dtClosed, GetDate()) > 6 AND fEmail = 1
)

DELETE FROM AttachmentReference WHERE ixAttachmentReference IN
(SELECT AttachmentReference.ixAttachmentReference
        FROM AttachmentReference
        INNER JOIN BugEvent
            ON AttachmentReference.ixBugEvent = BugEvent.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBug = BugEvent.ixBug
        WHERE DATEDIFF(mm, Bug.dtClosed, GetDate()) > 6 AND fEmail = 1
)

MySQL:

DELETE FROM Attachment WHERE ixAttachment IN
(SELECT AttachmentReference.ixAttachment
        FROM AttachmentReference
        INNER JOIN BugEvent
            ON AttachmentReference.ixBugEvent = BugEvent.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBug = BugEvent.ixBug
        WHERE (DATEDIFF(Bug.dtClosed, CurDate()) / 30 <= 6) AND fEmail = 1
)

DELETE ar FROM AttachmentReference ar
 INNER JOIN BugEvent
         ON ar.ixBugEvent = BugEvent.ixBugEvent
 INNER JOIN Bug
         ON Bug.ixBug = BugEvent.ixBug
      WHERE (DATEDIFF(Bug.dtClosed, CurDate()) / 30 <= 6) AND fEmail = 1