Common EDM Queries

estimate_encounter_size.sql

SELECT YEAR(ModifyDateTime) as document_year,
       COUNT(TotalKB) as document_count, 
       (SUM(TotalKB)/1024)/1024 as document_size_gb
FROM Documents as d
  INNER JOIN DocTypes as dt ON d.DocType = dt.DocType 
  INNER JOIN DocArchStg as das ON d.DocID = das.Docid
  INNER JOIN DocsOwners as do ON d.DocId = do.DocId
  INNER JOIN Encounters as e ON do.OwnerId = e.EncntrOwnerId
GROUP BY YEAR(ModifyDateTime) WITH ROLLUP

estimate_remit_size.sql

SELECT YEAR(ModifyDateTime) as document_year,
       COUNT(TotalKB) as document_count, 
       (SUM(TotalKB)/1024)/1024 as document_size_gb
FROM Documents as d
  INNER JOIN DocTypes as dt ON d.DocType = dt.DocType 
  INNER JOIN DocArchStg as das ON d.DocID = das.Docid
WHERE dt.DocTypeName in ('PT-PAY', 'RABLUEP', 'RACAIDP', 'RACAREP', 'RAUHC', 
                         'WORKCOMP', 'ZEROPAYS', 'RAAGENCY', 'EMPHLTH', 
                         'DISCINS', 'NONDISC')
GROUP BY YEAR(ModifyDateTime) WITH ROLLUP

select_encounter_docs.sql

SELECT DISTINCT DocTypeName, d.DocId, DocDateTime, CreateDateTime, 
                ModifyDateTime, DocTypeDesc, OwnerId 
FROM DocTypes as dt 
  INNER JOIN Documents as d ON dt.DocType = d.DocType 
  INNER JOIN Objects as o ON d.DocId = o.DocId 
  INNER JOIN FileFormats as ff ON o.FileFmtId = ff.FileFmtId 
  INNER JOIN DocsOwners as do ON d.DocId = do.DocId 
  INNER JOIN Encounters as e ON do.OwnerId = e.EncntrOwnerId 
WHERE Year(ModifyDateTime) = '2007'

select_remit_docs.sql

SELECT DISTINCT DocTypeName, d.DocId, DocDateTime, CreateDateTime, 
                ModifyDateTime, DocTypeDesc, OwnerId 
FROM DocTypes as dt 
  INNER JOIN Documents as d ON dt.DocType = d.DocType 
  INNER JOIN Objects as o ON d.DocId = o.DocId 
  INNER JOIN FileFormats as ff ON o.FileFmtId = ff.FileFmtId 
  INNER JOIN DocsOwners as do ON d.DocId = do.DocId 
WHERE dt.DocTypeName in ('PT-PAY', 'RABLUEP', 'RACAIDP', 'RACAREP', 'RAUHC', 
                         'WORKCOMP', 'ZEROPAYS', 'RAAGENCY', 'EMPHLTH', 
                         'DISCINS', 'NONDISC')
AND Year(ModifyDateTime) = '2007'