Main Content

CMHC version 05.30: Rules

Table detailing the rules for specification: CMHC
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 - Ambulatory 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 - 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 of Birth 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
ConMaritalStMiscoded Invalid High Yes MaritalSt contains spaces instead of appropriate value CON.MaritalSt MaritalSt should not contain spaces. To indicate a missing value, the appropriate numeral should be given here select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, CON.MaritalSt from CON where CON.MaritalSt is null
ConResAreaMiscoded Invalid High Yes ResArea contains spaces instead of appropriate value CON.ResArea ResArea should not contain spaces. To indicate a missing value, the appropriate numeral should be given here select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, CON.ResArea from CON where CON.ResArea is null
PerCoBMiscoded Invalid High Yes CoB contains spaces instead of appropriate value PER.CoB CoB should not contain spaces. To indicate a missing value, the appropriate numeral should be given here select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, PER.CoB from PER where PER.CoB is null
ConContDurZero Anomaly High Yes Zero reported for ContDur CON.ContDur Zero reported for Mental Health Service Contact Duration select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur as value from CON where ContDur = 0
ContDtOutsideCollection Anomaly Low Yes Contact Date outside collection period ($ContDt) CON.ContDt Contact Date is outside the collection period of the file select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDt from CON JOIN HR using (State) where ContDt < HR.repstart OR ContDt > HR.repend
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 124 years CON Age at Contact is greater than 124 years select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age from ConAge where Age > 124
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')
BadSA2Prop Anomaly Low No Greater than 5% ($prop.perc) of SA2s are invalid ($BadCount in total) HR.State Greater than 5% of SA2s (ResArea) 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 = 'ResArea' ) 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 480 minutes ($ContDur) CON.ContDur Contact Duration is greater than 480 minutes select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur from CON where ContDur > 480
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 AND DoBFlag IN ('1', '2')
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
HighConDtMonthProp Anomaly Low No One month ($ContDtMonth) has over 10% of all contacts ($ConPercent.perc) HR.State One month has over 10% of all contacts, indicating it may be a default date or suffer from incomplete reporting select State, ContDtMonth, sd_div_safe(ContDtCountSum, StConCountTotal, 3) as ConPercent from StConCount, ( select State, ContDtMonth, sum(ContDtCount) as ContDtCountSum from ServConCountByMonth group by State, ContDtMonth ) as tmpservcontotals where sd_div_safe(ContDtCountSum, StConCountTotal, 3) > 0.10
HighConDtYearProp Anomaly Low No One half-financial-year ($ContDtYear) has over 60% of all contacts ($ConPercent.perc) HR.State One year (ie. one half of the financial year) has greater than 60% of all contacts, indicating it may include a default date or suffer from incomplete reporting. select State, ContDtYear, sd_div_safe(ContDtCountSum, StConCountTotal, 3) as ConPercent from StConCount, ( select State, ContDtYear, sum(ContDtCount) as ContDtCountSum from ServConCountByMonth group by State, ContDtYear ) as tmpservcontotals where sd_div_safe(ContDtCountSum, StConCountTotal, 3) > 0.60
HighConDtDayProp Anomaly Low No One day ($ContDtDay) has over 4% of all contacts HR.State One day has greater than 4% of all contacts ($ConPercent.perc), indicating it may be a default date or suffer from incomplete reporting select State, ContDtDay, sd_div_safe(ConCount, StConCountTotal, 3) as ConPercent from StConCount, ( select State, extract(day FROM ContDt) AS ContDtDay, count(*) as ConCount from CON group by State, ContDtDay ) as tmpservcontotals where sd_div_safe(ConCount, StConCountTotal, 3) > 0.04
AdultAgeInYthOrCAUnitLow Anomaly Low No Inappropriate Ages ($bad are between 25 and 34, $prop.perc) for unit (TP $TP) SERV Age at Contact is between 25 and 34 years, but unit target population is Youth or Child and Adolescent select State, RegId, OrgId, ClusId, SUId, TargetPop as TP, sum( (Age between 25 and 34) :: int ) as bad, sd_div_safe( sum( (Age between 25 and 34) :: int ), count(*), 3 ) as prop from SERV join ConAge using (State, RegId, OrgId, ClusId, SUId) where TargetPop in ('5','1') group by State, RegId, OrgId, ClusId, SUId, TargetPop having count(*) > 1000 and sd_div_safe( sum( (Age between 25 and 34) :: int ), count(*), 3 ) > 0.10
AdultAgeInYthOrCAUnitHigh Anomaly High No Inappropriate Ages ($bad are over 34, $prop.perc) for unit (TP $TP) SERV Age at Contact is over 34 years, but unit target population is Youth or Child and Adolescent select State, RegId, OrgId, ClusId, SUId, TargetPop as TP, sum( (Age >= 35) :: int ) as bad, sd_div_safe( sum( (Age >= 35) :: int ), count(*), 3 ) as prop from SERV join ConAge using (State, RegId, OrgId, ClusId, SUId) where TargetPop in ('5','1') group by State, RegId, OrgId, ClusId, SUId, TargetPop having count(*) > 1000 and sd_div_safe( sum( (Age >= 35) :: int ), count(*), 3 ) > 0.10
LowAgeInOldUnitLow Anomaly Low No Inappropriate Ages ($bad are between 25 and 34, $prop.perc) for unit (TP $TP) SERV Age at Contact is between 25 and 34 years, but unit target population is Older person select State, RegId, OrgId, ClusId, SUId, TargetPop as TP, sum( (Age between 25 and 34) :: int ) as bad, sd_div_safe( sum( (Age between 25 and 34) :: int ), count(*), 3 ) as prop from SERV join ConAge using (State, RegId, OrgId, ClusId, SUId) where TargetPop in ('2') group by State, RegId, OrgId, ClusId, SUId, TargetPop having count(*) > 1000 and sd_div_safe( sum( (Age between 25 and 34) :: int ), count(*), 3 ) > 0.10
LowAgeInOldUnitHigh Anomaly High No Inappropriate Ages ($bad are below 25, $prop.perc) for unit (TP $TP) SERV Age at Contact is below 25 years, but unit target population is Older person select State, RegId, OrgId, ClusId, SUId, TargetPop as TP, sum( (Age <= 24) :: int ) as bad, sd_div_safe( sum( (Age <= 24) :: int ), count(*), 3 ) as prop from SERV join ConAge using (State, RegId, OrgId, ClusId, SUId) where TargetPop in ('2') group by State, RegId, OrgId, ClusId, SUId, TargetPop having count(*) > 1000 and sd_div_safe( sum( (Age <= 24) :: int ), count(*), 3 ) > 0.10
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
RegIndigStGrowthVaries Historical Medium No Growth variation over 20% in IndigSt ($PercChange%) REG Proportion of Indigenous Australians has changed by more than 20% from the previous year select State, RegId, round(100::float * abs(New.prop - Old.prop)) as PercChange from RegIndigStPropNonAus as New join hist.RegIndigStPropNonAus as Old using(State, RegId) where abs(New.prop - Old.prop) > 0.20;
HighUnknownContParticProp Anomaly Low No Greater than 15% of contacts ($PercChange%) have Unknown ContPartic ORG Greater than 15% of contacts have Unknown client participation status select State, RegId, OrgId, round(100::float * abs(prop)) as PercChange from OrgUnknownContParticProp where abs(prop) > 0.15;
HighUnknownContSessTypeProp Anomaly Low No Greater than 15% of contacts ($PercChange%) have Unknown ContSessType ORG Greater than 15% of contacts have Unknown session type status select State, RegId, OrgId, round(100::float * abs(prop)) as PercChange from OrgUnknownContSessTypeProp where abs(prop) > 0.15;
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.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.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 10 and 60 CON.DxPrinc The following diagnosis codes should only apply to ages 10-60: 'F53 ', 'F53.0 ', 'F53.1 ', 'F53.8 ', 'F53.9 ', 'F32.01', 'F32.11', 'F32.21', 'F32.31', 'F32.81', 'F32.91', 'O99.3 ' 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 ', 'F32.01', 'F32.11', 'F32.21', 'F32.31', 'F32.81', 'F32.91', 'O99.3 ') and Age not between 10 and 60
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: 'F03 ', 'F01.0 ', 'F01.1 ', 'F01.2 ', 'F01.3 ', 'F01.8 ', 'F01.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 ('F03 ', 'F01.0 ', 'F01.1 ', 'F01.2 ', 'F01.3 ', 'F01.8 ', 'F01.9 ') and Age < 15
BadDxPrincLowAge Inconsistent Low No Principal Diagnosis ($DxPrinc) and Age ($Age) less than 1 CON.DxPrinc The following diagnosis codes should not apply to ages less than 1: 'F80.0 ', 'F80.1 ', 'F80.2 ', 'F80.3 ', 'F80.8 ', 'F80.9 ', 'F81.0 ', 'F81.1 ', 'F81.2 ', 'F81.3 ', 'F81.8 ', 'F81.9 ', 'F82 ', 'F83 ', 'F88 ', 'F89 ' 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 ('F80.0 ', 'F80.1 ', 'F80.2 ', 'F80.3 ', 'F80.8 ', 'F80.9 ', 'F81.0 ', 'F81.1 ', 'F81.2 ', 'F81.3 ', 'F81.8 ', 'F81.9 ', 'F82 ', 'F83 ', 'F88 ', 'F89 ') and Age < 1
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 Low Yes Person has 16 or more contact records within a service unit on $days days, each day totalling less than 600 minutes PER Person has 16 or more contact records within a service unit on one or more days, each day totalling less than 600 minutes select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, COUNT(*) as days 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 AND SUM(ContDur) <= 599 ) tmpperconcount using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId) GROUP BY State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId
HighBusyConDayCount Anomaly High No Person has 16 or more ($Count) contacts on one day ($ContDt.dmy) totalling more than 600 minutes ($TotalContDur mins) PER Person has 16 or more contact records within a service unit on a single day totalling more than 600 minutes 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 AND SUM(ContDur) >= 600 ) tmpperconcount using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId)
ContParticChange Historical Low No Client participation has changed by $PercChange percent. HR.State Variation over 15 percent in client participation select State, round(100::float * abs(New.prop - Old.prop)) as PercChange from HrContParticProp as New join hist.HrContParticProp as Old using(State) where abs(New.prop - Old.prop) > 0.15;
HighExtSA2Prop Anomaly Low No Greater than 5% ($prop.perc) of Contacts have a ResArea out of state HR.State Greater than 5% of Contacts a ResArea out of state. (This check does not apply to ACT) select State, prop from HrResAreaProp 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
HighSuppCoBProp Anomaly Low No Greater than 15% ($prop.perc) of Persons have a CoB with Supplementary code HR.State Greater than 15% of Persons have a CoB with Supplementary code select State, prop from HrCoBPropSuppRegistered where prop > 0.15
SussHrDoBCount Anomaly Low No More than double the average birthrate ($DoBCount vs $PerDoBCountAvg) on a suspicious date ($DoB.ddmmyyyy) HR.State There are greater than double the average number of births for a date which is likely to be a default or erroneous, selected from 9/9/9, 9/9/99, 1/1/1970, 1/1/11. Applied to "accurate" DoBFlag dates only. select State, DoB, count(*) AS DoBCount, round(PerDoBCountAvg,1) as PerDoBCountAvg from PER, PerDoBCount where DoB in ( '1970-01-01', '1911-01-01', '2011-01-01', '1909-09-09', '1999-09-09' ) AND PersIdFlag = '1' AND DoBFlag = '1' group by State, DoB, PerDoBCountAvg having count(*) > PerDoBCountAvg * 2
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 of Birth 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
BadHrPersIdFlag21Counts Anomaly High No PersIdFlag '2' count ($Val2Count) exceeds '1' count ($Val1Count) HR Person Identifier Flag (PersIdFlag) '2' (No, Patient identifier is for a non-uniquely identifiable...) count exceeds '1' (Yes, Patient identifier is for a uniquely identifiable...) count select State, Val2.Count as Val2Count, Val1.Count as Val1Count from HrPersIdFlagDummyCount as Val2 join HrPersIdFlagRealCount as Val1 using (State) where Val2.Count > Val1.Count
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 2 (dummy) and LegalSt is 1 (involuntary) CON.LegalSt CON record with a dummy PersId (PersIdFlag 2) has an Involuntary legal status (LegalSt 1). Clients with an Involuntary legal status should be registered. PersIdFlag 1: No, Patient identifier is for a non-uniquely identifiable... LegalSt 1: Involuntary patient select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, LegalSt from CON where PersIdFlag = '2' 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'
StConDxPrincMissingHighProp Historical High Yes DxPrinc Missing codes, as a proportional of all records, have increased by $prop% from the previous year's submission. ($h_prop% to $n_prop%) HR.State DxPrinc Missing codes have increased as a proportion of all records by 10% or more from the previous year's submission. with DxPrincMissingCounts as ( select State, count(*) as n_con, sum(case when DxPrinc is null then 1 else 0 end) as n_missing from CON group by State ), HistDxPrincMissingCounts as ( select State, count(*) as h_con, sum(case when DxPrinc is null then 1 else 0 end) as h_missing from hist.CON group by State ) select State, round(100.0 * h_missing / h_con, 1) as h_prop, round(100.0 * n_missing / n_con, 1) as n_prop, round( (100.0 * n_missing / n_con) - (100.0 * h_missing / h_con), 1) as prop from DxPrincMissingCounts join HistDxPrincMissingCounts using (State) where h_con > 0 and n_con > 0 and (100.0 * n_missing / n_con) - (100.0 * h_missing / h_con) >= 10.0
StConGrowthVaries Historical Medium No Growth variation over 20% ($Growth.perc) in total contact count HR The total number of contacts has changed by more than 20 per cent from the previous year. select State, round(Growth, 3) as Growth from HrConCountGrowth where abs(Growth) > 0.20
StContDurGrowthVaries Historical Medium No Growth variation over 15% ($Growth.perc) in total contact duration HR The total contact hours has changed by more than 15 per cent from the previous year. select State, round(Growth, 3) as Growth from HrContDurTotalGrowth where abs(Growth) > 0.15
OrgInvolGrowthVaries Historical Medium No Growth variation over 5% ($Growth.perc) in ratio of Involuntary legal status ORG Organisation-wide ratio of registered contacts with involuntary legal status has increased by more than 5 per cent from the previous year. select State, RegId, OrgId, round(Growth, 3) as Growth from OrgLegalStInvolRatioGrowth where (Growth) > 0.5
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)