How to gather data on open and closed deficiencies

01_README.markdown

1. Get the the list of active deficiencies by running the 02_open_deficiencies.sql.
   1. Def Assignment TAT = DeficiencyCreateDateTime - DocumentCreateDateTime
2. Get the list of completed deficiencies by running the 03_completed_deficiencies.sql.
   1. Parse the ListData field's xml.
      1. If the DocId is not listed in the deficiencies table in the operational database and the DocType is valid:
         1. Def Assignment TAT = //Deficiency/CreateDateTime - Document.CreateDateTime
         2. Def Completed TAT = Event.StartDateTime - Document.CreateDateTime

02_open_deficiencies.sql

SELECT DefId, DefTypeName, def.CreateDateTime AS DeficiencyCreateDateTime, 
       DefStatusName, d.DocId, DocTypeName, 
       d.CreateDateTime AS DocumentCreateDateTime, EnrolleeName AS Provider, 
       dept.MiscCfgDesc AS ProviderDepartment, role.MiscCfgDesc AS ProviderRole, 
       EncounterNo, EncntrClass
FROM Deficiencies AS def
  INNER JOIN  DeficiencyTypes AS deft ON def.DefType = deft.DefType
  INNER JOIN  DeficiencyStatus AS defs ON def.DefStatus = defs.DefStatus
  -- Get Provider Data
  INNER JOIN  Users AS u ON def.RespPartyOwnerId = u.UserOwnerId
  INNER JOIN  Employees AS emp ON u.EmpOwnerId = emp.EmpOwnerId
  INNER JOIN  Enrollees AS enr ON emp.EnrolleeOwnerId = enr.EnrolleeOwnerId
  INNER JOIN  MiscConfig AS dept ON emp.EmpDeptMiscCfgId = dept.MiscCfgId AND emp.EmpDeptMiscCfgType = dept.MiscCfgType
  INNER JOIN  MiscConfig AS role ON u.RoleMiscCfgId = role.MiscCfgId AND u.RoleMiscCfgType = role.MiscCfgType
  -- Get Document Data
  INNER JOIN  Documents AS d ON def.DocId = d.DocId
  INNER JOIN  DocTypes AS dt ON d.DocType = dt.DocType
  -- Get Encounter Data
  INNER JOIN  DocsOwners AS do ON d.DOcId = do.DocId
  INNER JOIN  Encounters AS enc ON do.OwnerId = enc.EncntrOwnerId
WHERE DefTypeName = 'Signature'

03_completed_deficiencies.sql

SELECT UserId, StartDateTime, CompleteDateTime, ListData, MedRecNo, EncounterNo
FROM Events AS e
  INNER JOIN  ActionDescs AS ad ON e.ActionId = ad.ActionId
WHERE ActionName IN ('Complete Signature Deficiency', 'Complete Deficiency')
      AND StartDateTime >= '10/17/2011'
      AND StartDateTime < '10/18/2011'

04_event_list_data.xml

<Deficiency DefId="FE7644CE-0AFD-11E0-A0B9-001517625D7B">
  <Status>A</Status>
  <DefType DefTypeName="Signature" Id="-4">-4</DefType>
  <RespPartyId>039CA9BBB63B7398</RespPartyId>
  <PrevRespPartyId>0000000000000000</PrevRespPartyId>
  <DocId>FE7644CD-0AFD-11E0-A0B9-001517625D7B</DocId>
  <FolderId>1E5CB9DDBAF049A8</FolderId>
  <DueDate>01 JAN 2011 00:00:00</DueDate>
  <FolderOnHoldDate></FolderOnHoldDate>
  <RespPartOnHoldDate></RespPartOnHoldDate>
  <CreateUser>1A5C9E61ACC288E3</CreateUser>
  <CreateDateTime>18 DEC 2010 23:24:00 GMT</CreateDateTime>
  <ExtraInfo>
    <Anno type="deficiency">
      <DefChainId>9</DefChainId>
      <AutoPosition Mode="Bottom" Always="1" />
      <ChainAutoCreated>1</ChainAutoCreated>
    </Anno>
  </ExtraInfo>
  <ObjectId>1</ObjectId>
  <Active>1</Active>
  <RespPartyType>E</RespPartyType>
  <RespPartyConfig>-2</RespPartyConfig>
  <History>
    <Note DefId="FE7644CE-0AFD-11E0-A0B9-001517625D7B" SeqNo="1">
      <DefNotes></DefNotes>
      <CreateDateTime>18 DEC 2010 23:24:00 GMT</CreateDateTime>
      <CreateUser>395C91F46FA79442</CreateUser>
    </Note>
  </History>
  <AgingType>0</AgingType>
  <AgingBaseDate>17 DEC 2010 00:00:00</AgingBaseDate>
  <AgingDelinqDays>15</AgingDelinqDays>
  <DefPriority>1</DefPriority>
  <PredecessorList></PredecessorList>
  <SuccessorList></SuccessorList>
  <TextLine>Signature ED Summary</TextLine>
</Deficiency>