cancel
Showing results for 
Search instead for 
Did you mean: 

get info from DA SQL DB

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello all,

If I want to have an overview of Case Name, Date created, Owner, Items on hold for DA cases which have Legal Hold set, can that be easily done using an SQL query?

Does anyone have such a query, and is willing to share?

Thanks.

Regards. Gertjan
1 ACCEPTED SOLUTION

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

Not exactly what you are after but should get you going in the right direction:

SELECT SearchID,

            CaseID,

            NumHits,

            PrincipalName AS ModifiedBy,

            tblIntSearches.Name AS SearchName,

            tblIntSearches.ModifiedDate AS DateModified,

            CreateDate,

            tblStatus.[Name] AS SearchType,

            CreationType.[Name] AS CreationType,

            SampleResultSize,

            NativeQuery,

            NativeLegacyQuery,

            XMLText

FROM tblIntSearches

      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[ModifiedByID] = tblPrincipal.[PrincipalID]

      INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]

      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]

WHERE tblIntSearches.[StatusID] = 858 AND PrincipalName IS NOT NULL

UNION ALL

SELECT  TypeID AS SearchID,

            tblAudit.CaseID,

            NumHits,

            PrincipalName AS ModifiedBy,

            tblIntSearches.Name AS SearchName,

            AuditDate AS DateModified,

            CreateDate,

            tblStatus.[Name] AS SearchType,

            CreationType.[Name] AS CreationType,

            SampleResultSize,

            NativeQuery,

            NativeLegacyQuery,

            XMLText

FROM tblIntSearches

      INNER JOIN tblAudit ON tblAudit.TypeID = tblintSearches.SearchID

            AND tblIntSearches.StatusID = 858

            AND tblAudit.AuditTypeID = 1052

      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[CreatedByID] = tblPrincipal.[PrincipalID]

      INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]

      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]

ORDER BY CreateDate DESC

View solution in original post

1 REPLY 1

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

Not exactly what you are after but should get you going in the right direction:

SELECT SearchID,

            CaseID,

            NumHits,

            PrincipalName AS ModifiedBy,

            tblIntSearches.Name AS SearchName,

            tblIntSearches.ModifiedDate AS DateModified,

            CreateDate,

            tblStatus.[Name] AS SearchType,

            CreationType.[Name] AS CreationType,

            SampleResultSize,

            NativeQuery,

            NativeLegacyQuery,

            XMLText

FROM tblIntSearches

      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[ModifiedByID] = tblPrincipal.[PrincipalID]

      INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]

      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]

WHERE tblIntSearches.[StatusID] = 858 AND PrincipalName IS NOT NULL

UNION ALL

SELECT  TypeID AS SearchID,

            tblAudit.CaseID,

            NumHits,

            PrincipalName AS ModifiedBy,

            tblIntSearches.Name AS SearchName,

            AuditDate AS DateModified,

            CreateDate,

            tblStatus.[Name] AS SearchType,

            CreationType.[Name] AS CreationType,

            SampleResultSize,

            NativeQuery,

            NativeLegacyQuery,

            XMLText

FROM tblIntSearches

      INNER JOIN tblAudit ON tblAudit.TypeID = tblintSearches.SearchID

            AND tblIntSearches.StatusID = 858

            AND tblAudit.AuditTypeID = 1052

      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[CreatedByID] = tblPrincipal.[PrincipalID]

      INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]

      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]

ORDER BY CreateDate DESC