Document archive dates for custom reporting

So, you are doing some custom reporting and you need the archive date for a document. FileHold does not maintain an archive date directly in the document table, but it does have a field to indicate if the document is in the archive (IsArchive).

In FileHold, you can archive or unarchive a document as many times as you want if your permissions allow. The details of the archive action are maintained in the document usage log and that is there we will find the archive date. However, only the latest date will be relevant for an archive action when you want to know the archive date. Combined with documents where the IsArchive column equals 1, you can find the list of archived documents and dates.

Since we want only the latest archive date from the usage log, we will use a common table expression (supported in SQL Server since 2005) to rank order the list of archive actions in the log. The action id for an archive action is 15.

The complete list of action ids is available in the API reference guide. Search for "ActionType Enumeration" at http://api.filehold.com.

As a bonus, we will join with the document and metadata versions tables to get the document name for this example. Note that we are checking for records where the "is last" columns are set to 1.

use ch_librarymanager;
WITH ArchiveSummary AS (
    SELECT ul.UsageLogId, 
           ul.DocumentId,
           ul.ActionDate,
           ROW_NUMBER() OVER(PARTITION BY ul.DocumentId 
                                 ORDER BY ul.UsageLogId DESC) AS rank
    FROM UsageLog ul
    WHERE ul.ActionId=15)
select
    d.DocumentId
   ,mv.DocumentName
   ,[as].ActionDate as ArchiveDate
from Documents d
inner join ArchiveSummary [as] on [as].DocumentId = d.DocumentId and [as].rank = 1 and d.IsArchive = 1
inner join DocumentVersions dv on dv.DocumentId = d.DocumentId and IsLast = 1
inner join MetadataVersions mv on mv.DocumentVersionId = dv.DocumentVersionId and mv.IsLastForDocumentVersion = 1