Advanced Search

Announcement

Collapse
No announcement yet.

SQL - Find all annotations

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL - Find all annotations

    Warnings
    One or more of the below resolutions require knowledge of T-SQL and/or SQL Management Studio; consult a qualified SQL administrator for assistance with these procedures. Back up any targeted SQL databases before executing any SQL statements. Assisting with these procedures is outside the scope of normal Ipro application support.



    Here is an example of what the query will return: Capture.PNG









    EDIT 2019/05/02 - The query was updated to include what the redaction name is, if the 'annotation type' is 'redaction', as well as including the imagekey (page), if applicable. 2019-05-02 11_05_13.png







    EDIT 2019/06/16 - Cleaned up the query by separating the annotation type from the text. The new column contains any related annotation information. Capture99.PNG





    Code:
    
    
    /*
     
    If you do not know the Eclipse case database name, run the below query first against your ADD/Nucleus database to get the case and database name.
     
    use --ADD/Nucleus database
    select [Name], DatabaseName from
    [Enterprise].[CaseProductEnvironment]
    where ProductId=3
    order by [Name] asc
     
    */
     
    
    
    use --Eclipse Case Database
     
    select
     D.DocId
    ,D.DocumentKey
    ,ISNULL(DP.ImageKey,'') as 'ImageKey (Page)'
    
    ,CASE
           when (DA.AnnotationType is null AND DA.RedactionCatId is not null) then 'REDACTION'
           when (DA.AnnotationType = 0 AND DA.RedactionCatId is null) then 'HIGHLIGHT'
           when  DA.AnnotationType = 1 then 'STICKY NOTE'
           when  DA.AnnotationType = 2 then 'EMBEDDED TEXT'
           when  DA.AnnotationType = 3 then 'MARKUP'
        end as "Annotation Type"
    ,CASE
           when (DA.AnnotationType is null AND DA.RedactionCatId is not null) then RC.Label
           when  DA.AnnotationType = 1 then DA.AnnotationText
           when  DA.AnnotationType = 2 then DA.AnnotationText
           else ''
        end as "Annotation Text"
     
    from DocumentAnnotations DA
    
        left join Documents D
        on D.DocId=DA.DocId
    
        left join DocumentPages DP
        on DP.PageId = DA.PageId
    
        left join dbo.RedactionCategories RC
        on RC.RedactionCatId=DA.RedactionCatId
    
    
     
    ORDER BY D.DocumentKey asc
    Last edited by Andrew Coody; 06-13-2019, 06:37 AM.
    ​​​​​​​Andrew Coody
    Technical Support Engineer

  • #2
    Nice work Andrew.

    Comment

    Working...
    X