AdultAgeInYthOrCAUnitHigh
- Class:
Anomaly
- Priority:
High
- Message:
Inappropriate Ages (
$badare over 34,$prop.perc) for unit (TP$TP)- Mark:
SERV
- Description:
Age at Contact is over 34 years, but unit target population is Youth or Child and Adolescent
- SQL:
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
- Data Elements:
- Virtual Elements:
AdultAgeInYthOrCAUnitLow
- Class:
Anomaly
- Priority:
Low
- Message:
Inappropriate Ages (
$badare between 25 and 34,$prop.perc) for unit (TP$TP)- Mark:
SERV
- Description:
Age at Contact is between 25 and 34 years, but unit target population is Youth or Child and Adolescent
- SQL:
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
- Data Elements:
- Virtual Elements:
BadDxPrincAd
- Class:
Inconsistent
- Priority:
Low
- Message:
Principal Diagnosis (
$DxPrinc) and Age ($Age) less than 15- Mark:
CON.DxPrinc
- Description:
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 ‘
- SQL:
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
- Data Elements:
- Virtual Elements:
BadDxPrincF
- Class:
Inconsistent
- Priority:
High
- Message:
Principal Diagnosis (
$DxPrinc) and Sex ($Sex) is not female- Mark:
CON.DxPrinc
- Description:
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 ‘
- SQL:
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 is not null and Sex != '2' and Sex != '3'
- Data Elements:
BadDxPrincLowAge
- Class:
Inconsistent
- Priority:
Low
- Message:
Principal Diagnosis (
$DxPrinc) and Age ($Age) less than 1- Mark:
CON.DxPrinc
- Description:
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 ‘
- SQL:
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
- Data Elements:
- Virtual Elements:
BadDxPrincM
- Class:
Inconsistent
- Priority:
High
- Message:
Principal Diagnosis (
$DxPrinc) and Sex ($Sex) is not male- Mark:
CON.DxPrinc
- Description:
The following diagnosis codes should only apply to males: ‘F52.4 ‘
- SQL:
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 is not null and Sex != '1' and Sex != '3'
- Data Elements:
BadDxPrincPpm
- Class:
Inconsistent
- Priority:
Low
- Message:
Principal Diagnosis (
$DxPrinc) and Age ($Age) not between 10 and 60- Mark:
CON.DxPrinc
- Description:
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 ‘
- SQL:
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
- Data Elements:
- Virtual Elements:
BadHrLegalSt12Counts
- Class:
Anomaly
- Priority:
Medium
- Message:
LegalSt ‘1’ count (
$Val1Count) exceeds ‘2’ count ($Val2Count)- Mark:
HR
- Description:
Mental Health Legal Status (LegalSt) ‘1’ (Involuntary patient) count exceeds ‘2’ (Voluntary patient) count
- SQL:
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
- Data Elements:
- Virtual Elements:
BadHrPersIdFlag21Counts
- Class:
Anomaly
- Priority:
High
- Message:
PersIdFlag ‘2’ count (
$Val2Count) exceeds ‘1’ count ($Val1Count)- Mark:
HR
- Description:
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
- SQL:
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
- Data Elements:
- Virtual Elements:
BadSA2Prop
- Class:
Anomaly
- Priority:
Low
- Message:
Greater than 5% (
$prop.perc) of SA2s are invalid ($BadCountin total)- Mark:
HR.State
- Description:
Greater than 5% of SA2s (ResArea) are invalid
- SQL:
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
- Data Elements:
ClusClusNameMissing
ConContDtMissing
- Class:
Missing
- Priority:
High
- Message:
Missing data - ContDt
$ContDt.q- Mark:
CON.ContDt
- Description:
Missing data - Service Contact Date (ContDt)
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDt from CON where ContDt is null
- Data Elements:
ConContDurMissing
- Class:
Missing
- Priority:
High
- Message:
Missing data - ContDur
$ContDur.q- Mark:
CON.ContDur
- Description:
Missing data - Mental Health Service Contact Duration (ContDur)
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur from CON where ContDur is null
- Data Elements:
ConContDurZero
- Class:
Anomaly
- Priority:
High
- Message:
Zero reported for ContDur
- Mark:
CON.ContDur
- Description:
Zero reported for Mental Health Service Contact Duration
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur as value from CON where ContDur = 0
- Data Elements:
ConContParticMissing
- Class:
Missing
- Priority:
High
- Message:
Missing data - ContPartic
$ContPartic.q- Mark:
CON.ContPartic
- Description:
Missing data - Mental Health Service Contact-Patient/Client Participation Indicator (ContPartic)
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContPartic from CON where ContPartic is null
- Data Elements:
ConContSessTypeMissing
- Class:
Missing
- Priority:
High
- Message:
Missing data - ContSessType
$ContSessType.q- Mark:
CON.ContSessType
- Description:
Missing data - Mental Health Service Contact-Session Type (ContSessType)
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContSessType from CON where ContSessType is null
- Data Elements:
ConInvolAndUnreg
- Class:
Inconsistent
- Priority:
High
- Message:
PersIdFlag is 2 (dummy) and LegalSt is 1 (involuntary)
- Mark:
CON.LegalSt
- Description:
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
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, LegalSt from CON where PersIdFlag = '2' and LegalSt = '1'
- Data Elements:
ConLegalStMissing
- Class:
Missing
- Priority:
High
- Message:
Missing data - LegalSt
$LegalSt.q- Mark:
CON.LegalSt
- Description:
Missing data - Mental Health Legal Status (LegalSt)
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, LegalSt from CON where LegalSt is null
- Data Elements:
ConMaritalStMiscoded
- Class:
Invalid
- Priority:
High
- Message:
MaritalSt contains spaces instead of appropriate value
- Mark:
CON.MaritalSt
- Description:
MaritalSt should not contain spaces. To indicate a missing value, the appropriate numeral should be given here
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, CON.MaritalSt from CON where CON.MaritalSt is null
- Data Elements:
ConResAreaMiscoded
- Class:
Invalid
- Priority:
High
- Message:
ResArea contains spaces instead of appropriate value
- Mark:
CON.ResArea
- Description:
ResArea should not contain spaces. To indicate a missing value, the appropriate numeral should be given here
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, CON.ResArea from CON where CON.ResArea is null
- Data Elements:
ContDtBeforeDoB
- Class:
Inconsistent
- Priority:
High
- Message:
ContDt (
$ContDt.dmy) is before DoB ($DoB.dmy)- Mark:
CON.ContDt
- Description:
ContDt is before DoB
- SQL:
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')
- Data Elements:
ContDtOutsideCollection
- Class:
Anomaly
- Priority:
Low
- Message:
Contact Date outside collection period (
$ContDt)- Mark:
CON.ContDt
- Description:
Contact Date is outside the collection period of the file
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDt from CON JOIN HR using (State) where ContDt < HR.repstart OR ContDt > HR.repend
- Data Elements:
ContParticChange
- Class:
Historical
- Priority:
Low
- Message:
Client participation has changed by
$PercChangepercent.- Mark:
HR.State
- Description:
Variation over 15 percent in client participation
- SQL:
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;
- Data Elements:
- Virtual Elements:
HighAge
- Class:
Anomaly
- Priority:
Low
- Message:
Age is greater than 124 years
- Mark:
CON
- Description:
Age at Contact is greater than 124 years
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age from ConAge where Age > 124
- Data Elements:
- Virtual Elements:
HighBusyConDayCount
- Class:
Anomaly
- Priority:
High
- Message:
Person has 16 or more (
$Count) contacts on one day ($ContDt.dmy) totalling more than 600 minutes ($TotalContDurmins)- Mark:
PER
- Description:
Person has 16 or more contact records within a service unit on a single day totalling more than 600 minutes
- SQL:
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)
- Data Elements:
HighConCount
- Class:
Anomaly
- Priority:
Low
- Message:
Person has over 250 (
$Count) contacts- Mark:
PER
- Description:
Person has over 250 contact records within a service unit
- SQL:
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
- Data Elements:
HighConDayCount
- Class:
Anomaly
- Priority:
Low
- Message:
Person has 16 or more contact records within a service unit on
$daysdays, each day totalling less than 600 minutes- Mark:
PER
- Description:
Person has 16 or more contact records within a service unit on one or more days, each day totalling less than 600 minutes
- SQL:
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
- Data Elements:
HighConDtDayProp
- Class:
Anomaly
- Priority:
Low
- Message:
One day (
$ContDtDay) has over 4% of all contacts- Mark:
HR.State
- Description:
One day has greater than 4% of all contacts ($ConPercent.perc), indicating it may be a default date or suffer from incomplete reporting
- SQL:
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
- Data Elements:
HighConDtMonthProp
- Class:
Anomaly
- Priority:
Low
- Message:
One month (
$ContDtMonth) has over 10% of all contacts ($ConPercent.perc)- Mark:
HR.State
- Description:
One month has over 10% of all contacts, indicating it may be a default date or suffer from incomplete reporting
- SQL:
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
- Data Elements:
HighConDtYearProp
- Class:
Anomaly
- Priority:
Low
- Message:
One half-financial-year (
$ContDtYear) has over 60% of all contacts ($ConPercent.perc)- Mark:
HR.State
- Description:
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.
- SQL:
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
- Data Elements:
HighContDur
- Class:
Anomaly
- Priority:
Low
- Message:
Duration is greater than 480 minutes (
$ContDur)- Mark:
CON.ContDur
- Description:
Contact Duration is greater than 480 minutes
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur from CON where ContDur > 480
- Data Elements:
HighEstDoBFlagProp
- Class:
Anomaly
- Priority:
Low
- Message:
Greater than 15% (
$prop.perc) of Persons have an Estimated Date of Birth- Mark:
HR.State
- Description:
Greater than 15% of Persons have an Estimated Date of Birth
- SQL:
select State, prop from HrDoBFlagPropRegistered where prop > 0.15
- Data Elements:
- Virtual Elements:
HighExtCoBProp
- Class:
Anomaly
- Priority:
High
- Message:
Greater than 50% (
$prop.perc) of Persons have a CoB other than Australia- Mark:
HR.State
- Description:
Greater than 50% of Persons have a CoB other than Australia
- SQL:
select State, prop from HrCoBPropNotAus where prop > 0.5
- Data Elements:
- Virtual Elements:
HighExtSA2Prop
- Class:
Anomaly
- Priority:
Low
- Message:
Greater than 5% (
$prop.perc) of Contacts have a ResArea out of state- Mark:
HR.State
- Description:
Greater than 5% of Contacts a ResArea out of state. (This check does not apply to ACT)
- SQL:
select State, prop from HrResAreaProp where State != '8' and prop > 0.05
- Data Elements:
- Virtual Elements:
HighIndigNonAustProp
- Class:
Anomaly
- Priority:
High
- Message:
Proportion of IndigSt not born in Australia is greater than 5% (
$prop.perc)- Mark:
ORG
- Description:
Proportion of Indigenous Australians not born in Australia is greater than 5%
- SQL:
select State, RegId, OrgId, prop from OrgIndigStPropNonAus where prop > 0.05
- Data Elements:
- Virtual Elements:
HighMissingLegalStProp
- Class:
Anomaly
- Priority:
Low
- Message:
Greater than 5% (
$prop.perc) of Contacts have Missing as a legal status- Mark:
HR.State
- Description:
Greater than 5% of Contacts have Missing legal status
- SQL:
select State, prop from HrLegalStPropRegistered where prop > 0.05
- Data Elements:
- Virtual Elements:
HighPersIdFlagProp
- Class:
Anomaly
- Priority:
Low
- Message:
Greater than 10% (
$prop.perc) of Persons have a dummy PersId- Mark:
HR.State
- Description:
Greater than 10% of Persons have a dummy PersId
- SQL:
select State, prop from HrPersIdFlagProp where prop > 0.1
- Data Elements:
- Virtual Elements:
HighSuppCoBProp
- Class:
Anomaly
- Priority:
Low
- Message:
Greater than 15% (
$prop.perc) of Persons have a CoB with Supplementary code- Mark:
HR.State
- Description:
Greater than 15% of Persons have a CoB with Supplementary code
- SQL:
select State, prop from HrCoBPropSuppRegistered where prop > 0.15
- Data Elements:
- Virtual Elements:
HighUnknownContParticProp
- Class:
Anomaly
- Priority:
Low
- Message:
Greater than 15% of contacts (
$PercChange%) have Unknown ContPartic- Mark:
ORG
- Description:
Greater than 15% of contacts have Unknown client participation status
- SQL:
select State, RegId, OrgId, round(100::float * abs(prop)) as PercChange from OrgUnknownContParticProp where abs(prop) > 0.15;
- Data Elements:
- Virtual Elements:
HighUnknownContSessTypeProp
- Class:
Anomaly
- Priority:
Low
- Message:
Greater than 15% of contacts (
$PercChange%) have Unknown ContSessType- Mark:
ORG
- Description:
Greater than 15% of contacts have Unknown session type status
- SQL:
select State, RegId, OrgId, round(100::float * abs(prop)) as PercChange from OrgUnknownContSessTypeProp where abs(prop) > 0.15;
- Data Elements:
- Virtual Elements:
HrGenDtMissing
LowAge
- Class:
Anomaly
- Priority:
Low
- Message:
Age is less than 1 years (
$Age)- Mark:
CON
- Description:
Age at Contact is less than 1 years
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age from ConAge where Age < 1
- Data Elements:
- Virtual Elements:
LowAgeInOldUnitHigh
- Class:
Anomaly
- Priority:
High
- Message:
Inappropriate Ages (
$badare below 25,$prop.perc) for unit (TP$TP)- Mark:
SERV
- Description:
Age at Contact is below 25 years, but unit target population is Older person
- SQL:
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
- Data Elements:
- Virtual Elements:
LowAgeInOldUnitLow
- Class:
Anomaly
- Priority:
Low
- Message:
Inappropriate Ages (
$badare between 25 and 34,$prop.perc) for unit (TP$TP)- Mark:
SERV
- Description:
Age at Contact is between 25 and 34 years, but unit target population is Older person
- SQL:
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
- Data Elements:
- Virtual Elements:
LowAgeMarriageHigh
- Class:
Anomaly
- Priority:
Medium
- Message:
Age is less than 13 years and MaritalSt is
$MaritalSt- Mark:
CON.MaritalSt
- Description:
Age at Contact is less than 13 years and Marital Status is not 1 or 6 (Never married or Not stated)
- SQL:
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')
- Data Elements:
- Virtual Elements:
LowAgeMarriageLow
- Class:
Anomaly
- Priority:
Low
- Message:
Age is 13 to 15 years and MaritalSt is
$MaritalSt- Mark:
CON.MaritalSt
- Description:
Age at Contact is 13 to 15 years and Marital Status is not 1 or 6 (Never married or Not stated)
- SQL:
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')
- Data Elements:
- Virtual Elements:
LowContDur
- Class:
Anomaly
- Priority:
Low
- Message:
Duration is less than 5 minutes but greater than 0 minutes (
$ContDur)- Mark:
CON.ContDur
- Description:
Contact Duration is less than 5 minutes but greater than 0 minutes
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur from CON where ContDur > 0 and ContDur < 5
- Data Elements:
MedConDayCount
- Class:
Anomaly
- Priority:
Low
- Message:
Person has over 10 but less than 16 (
$Count) contacts on one day ($ContDt.dmy); TotalContDur$TotalContDurmins- Mark:
PER
- Description:
Person has over 10 but less than 16 contact records within a service unit on a single day
- SQL:
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)
- Data Elements:
OrgCAInSklOnly
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data (TargetPop: CA)- Description:
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)
OrgCANotInSkl
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data (TargetPop: CA)- Description:
Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: CA)
OrgForInSklOnly
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data (TargetPop: For)- Description:
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)
OrgForNotInSkl
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data (TargetPop: For)- Description:
Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: For)
OrgGenInSklOnly
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data (TargetPop: Gen)- Description:
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)
OrgGenNotInSkl
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data (TargetPop: Gen)- Description:
Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: Gen)
OrgInSklOnly
- Class:
Skeleton
- Priority:
High
- Message:
Org
$nameexpected from SKL is missing- Description:
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
OrgInvolGrowthVaries
- Class:
Historical
- Priority:
Medium
- Message:
Growth variation over 5% (
$Growth.perc) in ratio of Involuntary legal status- Mark:
ORG
- Description:
Organisation-wide ratio of registered contacts with involuntary legal status has increased by more than 5 per cent from the previous year.
- SQL:
select State, RegId, OrgId, round(Growth, 3) as Growth from OrgLegalStInvolRatioGrowth where (Growth) > 0.5
- Data Elements:
- Virtual Elements:
OrgNotInSkl
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data- Description:
Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data
OrgOldInSklOnly
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data (TargetPop: Old)- Description:
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)
OrgOldNotInSkl
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data (TargetPop: Old)- Description:
Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: Old)
OrgOrgNameMissing
OrgYthInSklOnly
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data (TargetPop: Yth)- Description:
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)
OrgYthNotInSkl
- Class:
Skeleton
- Priority:
High
- Message:
Org
$namenot in SKL data (TargetPop: Yth)- Description:
Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: Yth)
PerCoBDiffers
- Class:
Inconsistent
- Priority:
High
- Message:
Person has
$attr_countvalues for CoB ($attr_vals)- Mark:
PER.CoB
- Description:
Person has multiple values for CoB (Country of Birth) across one organisation
- SQL:
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)
- Data Elements:
PerCoBMiscoded
- Class:
Invalid
- Priority:
High
- Message:
CoB contains spaces instead of appropriate value
- Mark:
PER.CoB
- Description:
CoB should not contain spaces. To indicate a missing value, the appropriate numeral should be given here
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, PER.CoB from PER where PER.CoB is null
- Data Elements:
PerDoBCount
- SQL:
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
- Data Elements:
DoB
PerDoBDiffers
- Class:
Inconsistent
- Priority:
High
- Message:
Person has
$attr_countvalues for DoB ($attr_vals)- Mark:
PER.DoB
- Description:
Person has multiple values for DoB (Date of Birth) across one organisation
- SQL:
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)
- Data Elements:
PerDoBFlagAndDoB
- Class:
Inconsistent
- Priority:
High
- Message:
DoBFlag is 8 and DoB is not 09099999 (
$BadDoB.ddmmyyyy)- Mark:
PER.DoB
- Description:
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)
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, DoBFlag, DoB as BadDoB from PER where DoBFlag = '8' and DoB != '9999-09-09'
- Data Elements:
PerDoBFlagDiffers
- Class:
Inconsistent
- Priority:
High
- Message:
Person has
$attr_countvalues for DoBFlag ($attr_vals)- Mark:
PER.DoBFlag
- Description:
Person has multiple values for DoBFlag (Estimated Date of Birth Flag) across one organisation
- SQL:
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)
- Data Elements:
PerDoBFlagMissing
- Class:
Missing
- Priority:
High
- Message:
Missing data - DoBFlag
$DoBFlag.q- Mark:
PER.DoBFlag
- Description:
Missing data - Estimated Date of Birth Flag (DoBFlag)
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, DoBFlag from PER where DoBFlag is null
- Data Elements:
PerDoBMissing
PerIndigStDiffers
- Class:
Inconsistent
- Priority:
High
- Message:
Person has
$attr_countvalues for IndigSt ($attr_vals)- Mark:
PER.IndigSt
- Description:
Person has multiple values for IndigSt (Indigenous Status) across one organisation
- SQL:
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)
- Data Elements:
PerIndigStMissing
- Class:
Missing
- Priority:
High
- Message:
Missing data - IndigSt
$IndigSt.q- Mark:
PER.IndigSt
- Description:
Missing data - Indigenous Status (IndigSt)
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, IndigSt from PER where IndigSt is null
- Data Elements:
PerSexDiffers
- Class:
Inconsistent
- Priority:
High
- Message:
Person has
$attr_countvalues for Sex ($attr_vals)- Mark:
PER.Sex
- Description:
Person has multiple values for Sex (Sex) across one organisation
- SQL:
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)
- Data Elements:
PerSexGenderMissing
- Class:
Missing
- Priority:
High
- Message:
Missing data - at least one of Sex
$Sex.qor Gender$Gender.qrequired- Mark:
PER
- Description:
Missing data - at least one of Sex (Sex) or Gender (Gender) required
- SQL:
select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, Sex, Gender from PER where (coalesce(cast(Sex in ('1','2','3','9') as integer),0) + coalesce(cast(Gender in ('1','2','3','4','5','9') as integer),0) + 0) = 0
- Data Elements:
RegInSklOnly
- Class:
Skeleton
- Priority:
High
- Message:
Reg
$nameexpected from SKL is missing- Description:
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
RegIndigStGrowthVaries
- Class:
Historical
- Priority:
Medium
- Message:
Growth variation over 20% in IndigSt (
$PercChange%)- Mark:
REG
- Description:
Proportion of Indigenous Australians has changed by more than 20% from the previous year
- SQL:
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;
- Data Elements:
- Virtual Elements:
RegNotInSkl
- Class:
Skeleton
- Priority:
High
- Message:
Reg
$namenot in SKL data- Description:
Region not in skeleton reference data - A matching Ambulatory Region was not found in the skeleton data
RegRegNameMissing
SectorPrivate
ServClosed
- Class:
Historical
- Priority:
Medium
- Message:
Serv closed, historical
$hist_name.qt(SUId:$hist_SUId) no longer exists- Mark:
HR.State
- Description:
Service Unit Closed - A historical Service Unit was not found in current data
- SQL:
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
- Data Elements:
ServConCount
- SQL:
select State, RegId, OrgId, ClusId, SUId, sum(ContDtCount) as ServConCountTotal from ServConCountByMonth group by State, RegId, OrgId, ClusId, SUId
- Data Elements:
ClusId
OrgId
RegId
SUId
State
ServConCountByMonth
- SQL:
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
- Data Elements:
ClusId
ContDt
OrgId
RegId
SUId
State
ServOpened
- Class:
Historical
- Priority:
Medium
- Message:
Serv opened,
$SUName.qt(SUId:$SUId) not in historical data- Mark:
SERV
- Description:
Service Unit Opened - A matching Service Unit was not found in the historical data
- SQL:
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
- Data Elements:
ServRenamed
- Class:
Historical
- Priority:
Medium
- Message:
Serv renamed from
$hist_name.qtto$SUName.qt- Mark:
SERV.SUName
- Description:
Service Unit Renamed - Service Unit Name differs between historical and current data
- SQL:
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)
- Data Elements:
ServSUNameMissing
ServSectorMissing
ServTargetPopChanged
- Class:
Historical
- Priority:
High
- Message:
Service Unit TargetPop changed from
$hist_TargetPopto$TargetPop- Mark:
SERV.TargetPop
- Description:
Target Population Changed - Target Population value for Service Unit differs between historical and current data
- SQL:
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
- Data Elements:
ServTargetPopMissing
- Class:
Missing
- Priority:
High
- Message:
Missing data - TargetPop
$TargetPop.q- Mark:
SERV.TargetPop
- Description:
Missing data - Target Population (TargetPop)
- SQL:
select State, RegId, OrgId, ClusId, SUId, TargetPop from SERV where TargetPop is null
- Data Elements:
StConCount
- SQL:
select sum(ServConCountTotal) as StConCountTotal from ServConCount
StConDxPrincMissingHighProp
- Class:
Historical
- Priority:
High
- Message:
DxPrinc Missing codes, as a proportional of all records, have increased by
$prop% from the previous year’s submission. ($h_prop% to$n_prop%)- Mark:
HR.State
- Description:
DxPrinc Missing codes have increased as a proportion of all records by 10% or more from the previous year’s submission.
- SQL:
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
- Data Elements:
StConGrowthVaries
- Class:
Historical
- Priority:
Medium
- Message:
Growth variation over 20% (
$Growth.perc) in total contact count- Mark:
HR
- Description:
The total number of contacts has changed by more than 20 per cent from the previous year.
- SQL:
select State, round(Growth, 3) as Growth from HrConCountGrowth where abs(Growth) > 0.20
- Data Elements:
- Virtual Elements:
StContDurGrowthVaries
- Class:
Historical
- Priority:
Medium
- Message:
Growth variation over 15% (
$Growth.perc) in total contact duration- Mark:
HR
- Description:
The total contact hours has changed by more than 15 per cent from the previous year.
- SQL:
select State, round(Growth, 3) as Growth from HrContDurTotalGrowth where abs(Growth) > 0.15
- Data Elements:
- Virtual Elements:
StHighConProp
- Class:
Exceptional
- Priority:
High
- Message:
Total proportion of F99 as principle dx is greater than 30% for the jurisdiction (
$Prop.perc)- Mark:
HR.State
- Description:
Proportion of principle dx = F99 is more than 30% for the jurisdiction
- SQL:
SELECT dxprinc_total.State, ConCount, ConDxPrincCount, sd_div_safe(ConDxPrincCount, ConCount, 3) as Prop FROM ( select count(*) as ConCount from CON join HR using (State) ) state_total, ( select State, count(*) as ConDxPrincCount, DxPrinc from CON join HR using (State) group by State, DxPrinc ) dxprinc_total where DxPrinc = 'F99' and sd_div_safe(ConDxPrincCount, ConCount, 3) > 0.3
- Data Elements:
SussHrDoBCount
- Class:
Anomaly
- Priority:
Low
- Message:
More than double the average birthrate (
$DoBCountvs$PerDoBCountAvg) on a suspicious date ($DoB.ddmmyyyy)- Mark:
HR.State
- Description:
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.
- SQL:
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
- Data Elements: