Validation Queries

deleted_from_edm.sql

SELECT ob.DocId AS deleted_doc_id, ob.OBId AS deleted_ob_id
FROM on_base_docs AS ob
  LEFT OUTER JOIN Documents AS d WITH (NOLOCK) ON ob.DocId = d.DocId
WHERE d.DocId IS NULL

identify_missing_by_folder.sql

Select OwnerTypeName, Count(*) as doc_count
From on_base_missing as m
  INNER JOIN DocsOwners as do on m.DocId = do.DocId
  INNER JOIN Owners as o on do.OwnerId = o.OwnerId
  INNER JOIN OwnerTypes as ot on o.OwnerType = ot.OwnerType
where Supplements = 0 AND xml_pages = 0
Group By OwnerTypeName

missing_from_destination.sql

SELECT d.DocId, OwnerTypeName as folder,
  (SELECT COUNT(*) FROM Supplements AS sup WITH (NOLOCK) WHERE sup.DocId = d.DocId) AS Supplements,
  (SELECT COUNT(*) FROM Deficiencies AS def WITH (NOLOCK) WHERE def.DocId = d.DocId) AS Deficiencies,
  (SELECT COUNT(*) FROM Objects AS ob WITH (NOLOCK) 
   INNER JOIN FileFormats AS ff WITH (NOLOCK) ON ob.FileFmtId = ff.FileFmtId
   WHERE ob.DocId = d.DocId and FileExtension = 'XML') AS xml_pages
FROM Documents AS d WITH (NOLOCK)
  LEFT OUTER JOIN on_base_docs AS ob ON d.DocId = ob.DocId
  INNER JOIN DocTypes AS dt WITH (NOLOCK) ON d.DocType = dt.DocType
  INNER JOIN DocsOwners AS do WITH (NOLOCK) ON d.DocId = do.DocId
  INNER JOIN Owners AS o WITH (NOLOCK) ON do.OwnerId = o.OwnerId
  INNER JOIN OwnerTypes AS ot WITH (NOLOCK) ON o.OwnerType = ot.OwnerType
WHERE ob.DocId IS NULL
  AND ModifyDateTime >= '1/1/2013 12:00 AM' AND ModifyDateTime < '4/1/2015 12:00 AM'
  AND dt.DocTypeName not in ('837IEDIT', '837PEDIT')
  -- Comment Out Below for All Docs - This Filters for Extracts
  --AND (SELECT COUNT(*) FROM Supplements AS sup WITH (NOLOCK) WHERE sup.DocId = d.DocId) = 0
  AND (SELECT COUNT(*) FROM Deficiencies AS def WITH (NOLOCK) WHERE def.DocId = d.DocId) = 0
  AND (SELECT COUNT(*) FROM Objects AS ob WITH (NOLOCK)
       INNER JOIN FileFormats AS ff WITH (NOLOCK) ON ob.FileFmtId = ff.FileFmtId
       WHERE ob.DocId = d.DocId and FileExtension = 'XML') = 0