Community Mental Health Care Minimum Data Set — Version 04.10
Main Content
CMHC version 04.10: Rules
| Name | Class | Priority | Bulk | Message | Mark | Description | SQL |
|---|---|---|---|---|---|---|---|
| HrGenDtMissing | Missing | High | No | Missing data - GenDt $GenDt.q | HR.GenDt | Missing data - Data File Generation Date (GenDt) | select State, GenDt from HR where GenDt is null |
| RegRegNameMissing | Missing | High | No | Missing data - RegName $RegName.q | REG.RegName | Missing data - Region Name (RegName) | select State, RegId, RegName from REG where RegName is null |
| OrgOrgNameMissing | Missing | High | No | Missing data - OrgName $OrgName.q | ORG.OrgName | Missing data - Organisation Name (OrgName) | select State, RegId, OrgId, OrgName from ORG where OrgName is null |
| ClusClusNameMissing | Missing | High | No | Missing data - ClusName $ClusName.q | CLUS.ClusName | Missing data - Service Unit Cluster Name (ClusName) | select State, RegId, OrgId, ClusId, ClusName from CLUS where ClusName is null |
| ServSUNameMissing | Missing | High | No | Missing data - SUName $SUName.q | SERV.SUName | Missing data - Service Unit Name (SUName) | select State, RegId, OrgId, ClusId, SUId, SUName from SERV where SUName is null |
| ServSectorMissing | Missing | High | No | Missing data - Sector $Sector.q | SERV.Sector | Missing data - Service Unit Sector (Sector) | select State, RegId, OrgId, ClusId, SUId, Sector from SERV where Sector is null |
| ServTargetPopMissing | Missing | High | No | Missing data - TargetPop $TargetPop.q | SERV.TargetPop | Missing data - Target Population (TargetPop) | select State, RegId, OrgId, ClusId, SUId, TargetPop from SERV where TargetPop is null |
| PerSexMissing | Missing | High | No | Missing data - Sex $Sex.q | PER.Sex | Missing data - Sex (Sex) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, Sex from PER where Sex is null |
| PerDoBMissing | Missing | High | No | Missing data - DoB $DoB.q | PER.DoB | Missing data - Date of Birth (DoB) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, DoB from PER where DoB is null |
| PerDoBFlagMissing | Missing | High | No | Missing data - DoBFlag $DoBFlag.q | PER.DoBFlag | Missing data - Estimated Date Flag (DoBFlag) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, DoBFlag from PER where DoBFlag is null |
| PerIndigStMissing | Missing | High | No | Missing data - IndigSt $IndigSt.q | PER.IndigSt | Missing data - Indigenous Status (IndigSt) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, IndigSt from PER where IndigSt is null |
| ConContDtMissing | Missing | High | No | Missing data - ContDt $ContDt.q | CON.ContDt | Missing data - Service Contact Date (ContDt) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDt from CON where ContDt is null |
| ConLegalStMissing | Missing | High | No | Missing data - LegalSt $LegalSt.q | CON.LegalSt | Missing data - Mental Health Legal Status (LegalSt) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, LegalSt from CON where LegalSt is null |
| ConContDurMissing | Missing | High | No | Missing data - ContDur $ContDur.q | CON.ContDur | Missing data - Mental Health Service Contact Duration (ContDur) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur from CON where ContDur is null |
| ConContParticMissing | Missing | High | No | Missing data - ContPartic $ContPartic.q | CON.ContPartic | Missing data - Mental Health Service Contact-Patient/Client Participation Indicator (ContPartic) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContPartic from CON where ContPartic is null |
| ConContSessTypeMissing | Missing | High | No | Missing data - ContSessType $ContSessType.q | CON.ContSessType | Missing data - Mental Health Service Contact-Session Type (ContSessType) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContSessType from CON where ContSessType is null |
| ConMaritalStMissing | Missing | High | No | Missing data - MaritalSt $MaritalSt.q | CON.MaritalSt | Missing data - Marital Status (MaritalSt) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, MaritalSt from CON where MaritalSt is null |
| ConPersSLAMissing | Missing | High | No | Missing data - PersSLA $PersSLA.q | CON.PersSLA | Missing data - Area of Usual Residence (PersSLA) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, PersSLA from CON where PersSLA is null |
| PerCoBMissing | Missing | High | No | Missing data - CoB $CoB.q | PER.CoB | Missing data - Country of Birth (CoB) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, CoB from PER where CoB is null |
| ConDxPrincMissing | Missing | High | Yes | Missing data - DxPrinc $DxPrinc.q | CON.DxPrinc | Missing data - Principal Diagnosis (DxPrinc) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc from CON where DxPrinc is null |
| LowAge | Anomaly | Low | Yes | Age is less than 1 years ($Age) | CON | Age at Contact is less than 1 years | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age from ConAge where Age < 1 |
| HighAge | Anomaly | Low | Yes | Age is greater than 110 years | CON | Age at Contact is greater than 110 years | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age from ConAge where Age > 110 |
| LowAgeMarriageLow | Anomaly | Low | Yes | Age is 13 to 15 years and MaritalSt is $MaritalSt | CON.MaritalSt | Age at Contact is 13 to 15 years and Marital Status is not 1 or 6 (Never married or Not stated) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age, MaritalSt from CON join ConAge using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId) where Age < 16 and Age >= 13 and MaritalSt NOT IN ('1','6') |
| LowAgeMarriageHigh | Anomaly | Medium | Yes | Age is less than 13 years and MaritalSt is $MaritalSt | CON.MaritalSt | Age at Contact is less than 13 years and Marital Status is not 1 or 6 (Never married or Not stated) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age, MaritalSt from CON join ConAge using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId) where Age < 13 and MaritalSt NOT IN ('1','6') |
| BadSLAProp | Anomaly | Low | No | Greater than 5% ($prop.perc) of SLAs are invalid ($BadCount in total) | HR.State | Greater than 5% of SLAs (PersSLA) are invalid | select State, count(*) as AllCount, bad.BadCount, sd_div_safe(bad.BadCount, count(*), 3) as prop from CON cross join ( select count(*) as BadCount from error join rule on (rule.id = error.rule) where rule.name = 'Domain' and error.field = 'PersSLA' ) as bad group by State, bad.BadCount having sd_div_safe(bad.BadCount, count(*), 3) > 0.05 |
| SectorPrivate | Invalid | High | No | Sector is $Sector.qt (Private) | SERV.Sector | Service Unit Sector must be 1 (Public) | select State, RegId, OrgId, ClusId, SUId, Sector from SERV where Sector = '2' |
| LowContDur | Anomaly | Low | Yes | Duration is less than 5 minutes but greater than 0 minutes ($ContDur) | CON.ContDur | Contact Duration is less than 5 minutes but greater than 0 minutes | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur from CON where ContDur > 0 and ContDur < 5 |
| HighContDur | Anomaly | Low | Yes | Duration is greater than 300 minutes ($ContDur) | CON.ContDur | Contact Duration is greater than 300 minutes | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur from CON where ContDur > 300 |
| ContDtBeforeDoB | Inconsistent | High | No | ContDt ($ContDt.dmy) is before DoB ($DoB.dmy) | CON.ContDt | ContDt is before DoB | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDt, DoB from CON join PER using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId) where DoB != '9999-09-09' and ContDt < DoB |
| ServConCountByMonth | No | select State, RegId, OrgId, ClusId, SUId, extract(month FROM ContDt) AS ContDtMonth, extract(year FROM ContDt) AS ContDtYear, count(*) as ContDtCount from SERV join CON using (State, RegId, OrgId, ClusId, SUId) group by State, RegId, OrgId, ClusId, SUId, ContDtMonth, ContDtYear | |||||
| ServConCount | No | select State, RegId, OrgId, ClusId, SUId, sum(ContDtCount) as ServConCountTotal from ServConCountByMonth group by State, RegId, OrgId, ClusId, SUId | |||||
| StConCount | No | select sum(ServConCountTotal) as StConCountTotal from ServConCount | |||||
| PerDoBCount | No | select sum(DoBCount) as PerDoBCountTotal, avg(DoBCount) as PerDoBCountAvg from ( select count(*) as DoBCount from PER where DoB <> '9999-09-09' group by DoB ) as tmpperdobcount | |||||
| HighIndigNonAustProp | Anomaly | High | No | Proportion of IndigSt not born in Australia is greater than 5% ($prop.perc) | ORG | Proportion of Indigenous Australians not born in Australia is greater than 5% | select State, RegId, OrgId, prop from OrgIndigStPropNonAus where prop > 0.05 |
| BadDxPrincF | Inconsistent | High | No | Principal Diagnosis ($DxPrinc) and Sex ($Sex) is not female | CON.DxPrinc | The following diagnosis codes should only apply to females: 'F53 ', 'F53.0 ', 'F53.1 ', 'F53.8 ', 'F53.9 ', 'F32.01', 'F32.11', 'F32.21', 'F32.31', 'F32.81', 'F32.91', 'O99.3 ', 'F52.5 ' | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc, Sex from CON join PER using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId) where DxPrinc in ('F53 ', 'F53.0 ', 'F53.1 ', 'F53.8 ', 'F53.9 ', 'F32.01', 'F32.11', 'F32.21', 'F32.31', 'F32.81', 'F32.91', 'O99.3 ', 'F52.5 ') and Sex != '2' and Sex != '3' |
| BadDxPrincM | Inconsistent | High | No | Principal Diagnosis ($DxPrinc) and Sex ($Sex) is not male | CON.DxPrinc | The following diagnosis codes should only apply to males: 'F52.4 ' | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc, Sex from CON join PER using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId) where DxPrinc in ('F52.4 ') and Sex != '1' and Sex != '3' |
| BadDxPrincPpm | Inconsistent | Low | No | Principal Diagnosis ($DxPrinc) and Age ($Age) not between 15 and 55 | CON.DxPrinc | The following diagnosis codes should only apply to ages 15-55: 'F53 ', 'F53.0 ', 'F53.1 ', 'F53.8 ', 'F53.9 ' | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc, Age from CON join ConAge using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId) where DxPrinc in ('F53 ', 'F53.0 ', 'F53.1 ', 'F53.8 ', 'F53.9 ') and Age not between 15 and 55 |
| BadDxPrincAd | Inconsistent | Low | No | Principal Diagnosis ($DxPrinc) and Age ($Age) less than 15 | CON.DxPrinc | The following diagnosis codes should not apply to ages less than 15: 'F00.0 ', 'F00.1 ', 'F00.2 ', 'F00.9 ', 'F03 ', 'F52 ', 'F52.0 ', 'F52.1 ', 'F52.2 ', 'F52.3 ', 'F52.4 ', 'F52.5 ', 'F52.6 ', 'F52.7 ', 'F52.8 ', 'F52.9 ', 'F64.0 ', 'F64.1 ' | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc, Age from CON join ConAge using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId) where DxPrinc in ('F00.0 ', 'F00.1 ', 'F00.2 ', 'F00.9 ', 'F03 ', 'F52 ', 'F52.0 ', 'F52.1 ', 'F52.2 ', 'F52.3 ', 'F52.4 ', 'F52.5 ', 'F52.6 ', 'F52.7 ', 'F52.8 ', 'F52.9 ', 'F64.0 ', 'F64.1 ') and Age < 15 |
| BadDxPrincCh | Inconsistent | Low | No | Principal Diagnosis ($DxPrinc) and Age ($Age) greater than 16 | CON.DxPrinc | The following diagnosis codes should not apply to ages greater than 16: 'F64.2 ' | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc, Age from CON join ConAge using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId) where DxPrinc in ('F64.2 ') and Age > 16 |
| HighConCount | Anomaly | Low | Yes | Person has over 250 ($Count) contacts | PER | Person has over 250 contact records within a service unit | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, count(*) as Count from CON group by State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId having count(*) > 250 |
| MedConDayCount | Anomaly | Low | Yes | Person has over 10 but less than 16 ($Count) contacts on one day ($ContDt.dmy); TotalContDur $TotalContDur mins | PER | Person has over 10 but less than 16 contact records within a service unit on a single day | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt, TotalContDur, Count from PER join ( select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt, sum(ContDur) as TotalContDur, count(*) as Count from CON group by State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt having count(*) > 10 and count(*) < 16 ) tmpperconcount using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId) |
| HighConDayCount | Anomaly | Medium | No | Person has 16 or more ($Count) contacts on one day ($ContDt.dmy); TotalContDur $TotalContDur mins | PER | Person has 16 or more contact records within a service unit on a single day | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt, TotalContDur, Count from PER join ( select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt, sum(ContDur) as TotalContDur, count(*) as Count from CON group by State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt having count(*) > 15 ) tmpperconcount using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId) |
| LowPartic | Anomaly | Low | No | Greater than 10% ($prop.perc) of Contacts have no client participation | HR.State | Greater than 10% of Contacts have no client participation (ContPartic is not '1') | select State, prop from HrContParticProp where prop > 0.1 |
| HighExtSLAProp | Anomaly | Low | No | Greater than 5% ($prop.perc) of Contacts have a PersSLA out of state | HR.State | Greater than 5% of Contacts a PersSLA out of state. (This check does not apply to ACT) | select State, prop from HrPersSLAProp where State != '8' and prop > 0.05 |
| HighEstDoBFlagProp | Anomaly | Low | No | Greater than 15% ($prop.perc) of Persons have an Estimated Date of Birth | HR.State | Greater than 15% of Persons have an Estimated Date of Birth | select State, prop from HrDoBFlagPropRegistered where prop > 0.15 |
| HighExtCoBProp | Anomaly | High | No | Greater than 50% ($prop.perc) of Persons have a CoB other than Australia | HR.State | Greater than 50% of Persons have a CoB other than Australia | select State, prop from HrCoBPropNotAus where prop > 0.5 |
| HighMissingLegalStProp | Anomaly | Low | No | Greater than 5% ($prop.perc) of Contacts have Missing as a legal status | HR.State | Greater than 5% of Contacts have Missing legal status | select State, prop from HrLegalStPropRegistered where prop > 0.05 |
| HighPersIdFlagProp | Anomaly | Low | No | Greater than 10% ($prop.perc) of Persons have a dummy PersId | HR.State | Greater than 10% of Persons have a dummy PersId | select State, prop from HrPersIdFlagProp where prop > 0.1 |
| PerSexDiffers | Inconsistent | High | No | Person has $attr_count values for Sex ($attr_vals) | PER.Sex | Person has multiple values for Sex (Sex) across one organisation | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, attr_count, attr_vals from PER join ( select State, RegId, OrgId, PersIdFlag, PersId, count(DISTINCT Sex) as attr_count, string_agg(DISTINCT Sex::TEXT, ',') as attr_vals from PER group by State, RegId, OrgId, PersIdFlag, PersId having count(DISTINCT Sex) > 1 ) as foo using (State, RegId, OrgId, PersIdFlag, PersId) |
| PerDoBDiffers | Inconsistent | High | No | Person has $attr_count values for DoB ($attr_vals) | PER.DoB | Person has multiple values for DoB (Date of Birth) across one organisation | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, attr_count, attr_vals from PER join ( select State, RegId, OrgId, PersIdFlag, PersId, count(DISTINCT DoB) as attr_count, string_agg(DISTINCT DoB::TEXT, ',') as attr_vals from PER group by State, RegId, OrgId, PersIdFlag, PersId having count(DISTINCT DoB) > 1 ) as foo using (State, RegId, OrgId, PersIdFlag, PersId) |
| PerDoBFlagDiffers | Inconsistent | High | No | Person has $attr_count values for DoBFlag ($attr_vals) | PER.DoBFlag | Person has multiple values for DoBFlag (Estimated Date Flag) across one organisation | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, attr_count, attr_vals from PER join ( select State, RegId, OrgId, PersIdFlag, PersId, count(DISTINCT DoBFlag) as attr_count, string_agg(DISTINCT DoBFlag::TEXT, ',') as attr_vals from PER group by State, RegId, OrgId, PersIdFlag, PersId having count(DISTINCT DoBFlag) > 1 ) as foo using (State, RegId, OrgId, PersIdFlag, PersId) |
| PerCoBDiffers | Inconsistent | High | No | Person has $attr_count values for CoB ($attr_vals) | PER.CoB | Person has multiple values for CoB (Country of Birth) across one organisation | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, attr_count, attr_vals from PER join ( select State, RegId, OrgId, PersIdFlag, PersId, count(DISTINCT CoB) as attr_count, string_agg(DISTINCT CoB::TEXT, ',') as attr_vals from PER group by State, RegId, OrgId, PersIdFlag, PersId having count(DISTINCT CoB) > 1 ) as foo using (State, RegId, OrgId, PersIdFlag, PersId) |
| PerIndigStDiffers | Inconsistent | High | No | Person has $attr_count values for IndigSt ($attr_vals) | PER.IndigSt | Person has multiple values for IndigSt (Indigenous Status) across one organisation | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, attr_count, attr_vals from PER join ( select State, RegId, OrgId, PersIdFlag, PersId, count(DISTINCT IndigSt) as attr_count, string_agg(DISTINCT IndigSt::TEXT, ',') as attr_vals from PER group by State, RegId, OrgId, PersIdFlag, PersId having count(DISTINCT IndigSt) > 1 ) as foo using (State, RegId, OrgId, PersIdFlag, PersId) |
| BadHrLegalSt12Counts | Anomaly | Medium | No | LegalSt '1' count ($Val1Count) exceeds '2' count ($Val2Count) | HR | Mental Health Legal Status (LegalSt) '1' (Involuntary patient) count exceeds '2' (Voluntary patient) count | select State, Val1.Count as Val1Count, Val2.Count as Val2Count from HrLegalStInvolCount as Val1 join HrLegalStVolCount as Val2 using (State) where Val1.Count > Val2.Count |
| BadHrPersIdFlag10Counts | Anomaly | High | No | PersIdFlag '1' count ($Val1Count) exceeds '0' count ($Val0Count) | HR | Person Identifier Flag (PersIdFlag) '1' (Private) count exceeds '0' (Patient identifier is unique and stable for each individual) count | select State, Val1.Count as Val1Count, Val0.Count as Val0Count from HrPersIdFlagDummyCount as Val1 join HrPersIdFlagRealCount as Val0 using (State) where Val1.Count > Val0.Count |
| RegOpened | Historical | Medium | No | Reg opened, $RegName.qt (RegId: $RegId) not in historical data | REG | Region Opened - A matching Region was not found in the historical data | select State, RegId, REG.RegName from main.REG left join hist.REG as hist_entity using (State, RegId) where hist_entity.RegId is null |
| RegClosed | Historical | Medium | No | Reg closed, historical $hist_name.qt (RegId: $hist_RegId) no longer exists | HR.State | Region Closed - A historical Region was not found in current data | select State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.RegName as hist_name from hist.REG as hist_entity left join main.REG using (State, RegId) where REG.RegId is null |
| RegRenamed | Historical | Medium | No | Reg renamed from $hist_name.qt to $RegName.qt | REG.RegName | Region Renamed - Region Name differs between historical and current data | select State, RegId, REG.RegName, hist_entity.RegName as hist_name from REG join hist.REG as hist_entity using(State, RegId) where not sloppy_match(REG.RegName, hist_entity.RegName) |
| OrgOpened | Historical | Medium | No | Org opened, $OrgName.qt (OrgId: $OrgId) not in historical data | ORG | Organisation Opened - A matching Organisation was not found in the historical data | select State, RegId, OrgId, ORG.OrgName from main.ORG left join hist.ORG as hist_entity using (State, RegId, OrgId) where hist_entity.OrgId is null |
| OrgClosed | Historical | Medium | No | Org closed, historical $hist_name.qt (OrgId: $hist_OrgId) no longer exists | HR.State | Organisation Closed - A historical Organisation was not found in current data | select State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.OrgId as hist_OrgId, hist_entity.OrgName as hist_name from hist.ORG as hist_entity left join main.ORG using (State, RegId, OrgId) where ORG.OrgId is null |
| OrgRenamed | Historical | Medium | No | Org renamed from $hist_name.qt to $OrgName.qt | ORG.OrgName | Organisation Renamed - Organisation Name differs between historical and current data | select State, RegId, OrgId, ORG.OrgName, hist_entity.OrgName as hist_name from ORG join hist.ORG as hist_entity using(State, RegId, OrgId) where not sloppy_match(ORG.OrgName, hist_entity.OrgName) |
| ClusOpened | Historical | Medium | No | Clus opened, $ClusName.qt (ClusId: $ClusId) not in historical data | CLUS | Service Unit Cluster Opened - A matching Service Unit Cluster was not found in the historical data | select State, RegId, OrgId, ClusId, CLUS.ClusName from main.CLUS left join hist.CLUS as hist_entity using (State, RegId, OrgId, ClusId) where hist_entity.ClusId is null |
| ClusClosed | Historical | Medium | No | Clus closed, historical $hist_name.qt (ClusId: $hist_ClusId) no longer exists | HR.State | Service Unit Cluster Closed - A historical Service Unit Cluster was not found in current data | select State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.OrgId as hist_OrgId, hist_entity.ClusId as hist_ClusId, hist_entity.ClusName as hist_name from hist.CLUS as hist_entity left join main.CLUS using (State, RegId, OrgId, ClusId) where CLUS.ClusId is null |
| ClusRenamed | Historical | Medium | No | Clus renamed from $hist_name.qt to $ClusName.qt | CLUS.ClusName | Service Unit Cluster Renamed - Service Unit Cluster Name differs between historical and current data | select State, RegId, OrgId, ClusId, CLUS.ClusName, hist_entity.ClusName as hist_name from CLUS join hist.CLUS as hist_entity using(State, RegId, OrgId, ClusId) where not sloppy_match(CLUS.ClusName, hist_entity.ClusName) |
| ServOpened | Historical | Medium | No | Serv opened, $SUName.qt (SUId: $SUId) not in historical data | SERV | Service Unit Opened - A matching Service Unit was not found in the historical data | select State, RegId, OrgId, ClusId, SUId, SERV.SUName from main.SERV left join hist.SERV as hist_entity using (State, RegId, OrgId, ClusId, SUId) where hist_entity.SUId is null |
| ServClosed | Historical | Medium | No | Serv closed, historical $hist_name.qt (SUId: $hist_SUId) no longer exists | HR.State | Service Unit Closed - A historical Service Unit was not found in current data | select State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.OrgId as hist_OrgId, hist_entity.ClusId as hist_ClusId, hist_entity.SUId as hist_SUId, hist_entity.SUName as hist_name from hist.SERV as hist_entity left join main.SERV using (State, RegId, OrgId, ClusId, SUId) where SERV.SUId is null |
| ServRenamed | Historical | Medium | No | Serv renamed from $hist_name.qt to $SUName.qt | SERV.SUName | Service Unit Renamed - Service Unit Name differs between historical and current data | select State, RegId, OrgId, ClusId, SUId, SERV.SUName, hist_entity.SUName as hist_name from SERV join hist.SERV as hist_entity using(State, RegId, OrgId, ClusId, SUId) where not sloppy_match(SERV.SUName, hist_entity.SUName) |
| ServTargetPopChanged | Historical | High | No | Service Unit TargetPop changed from $hist_TargetPop to $TargetPop | SERV.TargetPop | Target Population Changed - Target Population value for Service Unit differs between historical and current data | select State, RegId, OrgId, ClusId, SUId, SERV.TargetPop, hist_SERV.TargetPop as hist_TargetPop from SERV join hist.SERV as hist_SERV using(State, RegId, OrgId, ClusId, SUId) where SERV.TargetPop != hist_SERV.TargetPop |
| ConInvolAndUnreg | Inconsistent | High | Yes | PersIdFlag is 1 (dummy) and LegalSt is 1 (involuntary) | CON.LegalSt | CON record with a dummy PersId (PersIdFlag 1) has an Involuntary legal status (LegalSt 1). Clients with an Involuntary legal status should be registered. PersIdFlag 1: Private LegalSt 1: Involuntary patient | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, LegalSt from CON where PersIdFlag = '1' and LegalSt = '1' |
| PerDoBFlagAndDoB | Inconsistent | High | Yes | DoBFlag is 8 and DoB is not 09099999 ($BadDoB.ddmmyyyy) | PER.DoB | If date of birth flag (DoBFlag) is '8' (dummy date), date of birth (DoB) must be '09099999'. DoBFlag 8: Date of birth is a "dummy" date (ie, 09099999) | select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, DoBFlag, DoB as BadDoB from PER where DoBFlag = '8' and DoB != '9999-09-09' |
| RegNotInSkl | Skeleton | High | No | Reg $name not in SKL data | Region not in skeleton reference data - A matching Ambulatory Region was not found in the skeleton data | ||
| RegInSklOnly | Skeleton | High | No | Reg $name expected from SKL is missing | Region appears in skeleton reference data only - A Region with matching Ids is expected based on the SKL data but is not present in this file | ||
| OrgNotInSkl | Skeleton | High | No | Org $name not in SKL data | Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data | ||
| OrgInSklOnly | Skeleton | High | No | Org $name expected from SKL is missing | Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file | ||
| OrgCANotInSkl | Skeleton | High | No | Org $name not in SKL data (TargetPop: CA) | Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: CA) | ||
| OrgCAInSklOnly | Skeleton | High | No | Org $name not in SKL data (TargetPop: CA) | Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file (TargetPop: CA) | ||
| OrgOldNotInSkl | Skeleton | High | No | Org $name not in SKL data (TargetPop: Old) | Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: Old) | ||
| OrgOldInSklOnly | Skeleton | High | No | Org $name not in SKL data (TargetPop: Old) | Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file (TargetPop: Old) | ||
| OrgYthNotInSkl | Skeleton | High | No | Org $name not in SKL data (TargetPop: Yth) | Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: Yth) | ||
| OrgYthInSklOnly | Skeleton | High | No | Org $name not in SKL data (TargetPop: Yth) | Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file (TargetPop: Yth) | ||
| OrgForNotInSkl | Skeleton | High | No | Org $name not in SKL data (TargetPop: For) | Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: For) | ||
| OrgForInSklOnly | Skeleton | High | No | Org $name not in SKL data (TargetPop: For) | Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file (TargetPop: For) | ||
| OrgGenNotInSkl | Skeleton | High | No | Org $name not in SKL data (TargetPop: Gen) | Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: Gen) | ||
| OrgGenInSklOnly | Skeleton | High | No | Org $name not in SKL data (TargetPop: Gen) | Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file (TargetPop: Gen) |