Skip to content

Instantly share code, notes, and snippets.

@ebot

ebot/deleted_from_edm.sql

Last active August 29, 2015 14:16
Show Gist options
  • Save ebot/e1f8248e650921863b22 to your computer and use it in GitHub Desktop.
Save ebot/e1f8248e650921863b22 to your computer and use it in GitHub Desktop.
Download ZIP
Validation Queries
Raw
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
Raw
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
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment