4. Appendix B: Rules and Virtual Elements

4.1. Rules

4.1.1. AdultAgeInYthOrCAUnitHigh

Class:

Anomaly

Priority:

High

Message:

Inappropriate Ages ($bad are 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:

ConAge

4.1.2. AdultAgeInYthOrCAUnitLow

Class:

Anomaly

Priority:

Low

Message:

Inappropriate Ages ($bad are 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:

ConAge

4.1.3. 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:

ConAge

4.1.4. 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:

4.1.5. 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:

ConAge

4.1.6. 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:

4.1.7. 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:

ConAge

4.1.8. 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:

State

Virtual Elements:

4.1.9. 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:

State

Virtual Elements:

4.1.10. BadSA2Prop

Class:

Anomaly

Priority:

Low

Message:

Greater than 5% ($prop.perc) of SA2s are invalid ($BadCount in 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:

4.1.11. ClusClusNameMissing

Class:

Missing

Priority:

High

Message:

Missing data - ClusName $ClusName.q

Mark:

CLUS.ClusName

Description:

Missing data - Service Unit Cluster Name (ClusName)

SQL:

select State,
       RegId,
       OrgId,
       ClusId,
       ClusName
  from CLUS
 where ClusName is null

Data Elements:

4.1.12. 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:

4.1.13. 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:

4.1.14. 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:

4.1.15. 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:

4.1.16. 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:

4.1.17. 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:

4.1.18. 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:

4.1.19. 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:

4.1.20. 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:

4.1.21. 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:

4.1.22. 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:

4.1.23. ContParticChange

Class:

Historical

Priority:

Low

Message:

Client participation has changed by $PercChange percent.

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:

State

Virtual Elements:

HrContParticProp

4.1.24. 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:

ConAge

4.1.25. HighBusyConDayCount

Class:

Anomaly

Priority:

High

Message:

Person has 16 or more ($Count) contacts on one day ($ContDt.dmy) totalling more than 600 minutes ($TotalContDur mins)

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:

4.1.26. 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:

4.1.27. HighConDayCount

Class:

Anomaly

Priority:

Low

Message:

Person has 16 or more contact records within a service unit on $days days, 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:

4.1.28. 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:

ContDt
State

4.1.29. 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:

State

4.1.30. 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:

State

4.1.31. 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:

4.1.32. 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:

State

Virtual Elements:

HrDoBFlagPropRegistered

4.1.33. 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:

State

Virtual Elements:

HrCoBPropNotAus

4.1.34. 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:

State

Virtual Elements:

HrResAreaProp

4.1.35. 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:

OrgIndigStPropNonAus

4.1.36. 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:

State

Virtual Elements:

HrLegalStPropRegistered

4.1.37. 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:

State

Virtual Elements:

HrPersIdFlagProp

4.1.38. 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:

State

Virtual Elements:

HrCoBPropSuppRegistered

4.1.39. 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:

OrgUnknownContParticProp

4.1.40. 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:

OrgUnknownContSessTypeProp

4.1.41. HrGenDtMissing

Class:

Missing

Priority:

High

Message:

Missing data - GenDt $GenDt.q

Mark:

HR.GenDt

Description:

Missing data - Data File Generation Date (GenDt)

SQL:

select State,
       GenDt
  from HR
 where GenDt is null

Data Elements:

GenDt
State

4.1.42. 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:

ConAge

4.1.43. LowAgeInOldUnitHigh

Class:

Anomaly

Priority:

High

Message:

Inappropriate Ages ($bad are 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:

ConAge

4.1.44. LowAgeInOldUnitLow

Class:

Anomaly

Priority:

Low

Message:

Inappropriate Ages ($bad are 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:

ConAge

4.1.45. 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:

ConAge

4.1.46. 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:

ConAge

4.1.47. 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:

4.1.48. MedConDayCount

Class:

Anomaly

Priority:

Low

Message:

Person has over 10 but less than 16 ($Count) contacts on one day ($ContDt.dmy); TotalContDur $TotalContDur mins

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:

4.1.49. OrgCAInSklOnly

Class:: Skeleton
Priority:: High
Message:: Org $name not 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)

4.1.50. OrgCANotInSkl

Class:: Skeleton
Priority:: High
Message:: Org $name not 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)

4.1.51. OrgForInSklOnly

Class:: Skeleton
Priority:: High
Message:: Org $name not 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)

4.1.52. OrgForNotInSkl

Class:: Skeleton
Priority:: High
Message:: Org $name not 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)

4.1.53. OrgGenInSklOnly

Class:: Skeleton
Priority:: High
Message:: Org $name not 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)

4.1.54. OrgGenNotInSkl

Class:: Skeleton
Priority:: High
Message:: Org $name not 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)

4.1.55. OrgInSklOnly

Class:: Skeleton
Priority:: High
Message:: Org $name expected 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

4.1.56. 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:

OrgLegalStInvolRatioGrowth

4.1.57. OrgNotInSkl

Class:: Skeleton
Priority:: High
Message:: Org $name not in SKL data
Description:: Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data

4.1.58. OrgOldInSklOnly

Class:: Skeleton
Priority:: High
Message:: Org $name not 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)

4.1.59. OrgOldNotInSkl

Class:: Skeleton
Priority:: High
Message:: Org $name not 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)

4.1.60. OrgOrgNameMissing

Class:

Missing

Priority:

High

Message:

Missing data - OrgName $OrgName.q

Mark:

ORG.OrgName

Description:

Missing data - Organisation Name (OrgName)

SQL:

select State,
       RegId,
       OrgId,
       OrgName
  from ORG
 where OrgName is null

Data Elements:

4.1.61. OrgYthInSklOnly

Class:: Skeleton
Priority:: High
Message:: Org $name not 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)

4.1.62. OrgYthNotInSkl

Class:: Skeleton
Priority:: High
Message:: Org $name not 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)

4.1.63. PerCoBDiffers

Class:

Inconsistent

Priority:

High

Message:

Person has $attr_count values 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:

4.1.64. 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:

4.1.65. 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

4.1.66. PerDoBDiffers

Class:

Inconsistent

Priority:

High

Message:

Person has $attr_count values 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:

4.1.67. 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:

4.1.68. PerDoBFlagDiffers

Class:

Inconsistent

Priority:

High

Message:

Person has $attr_count values 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:

4.1.69. 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:

4.1.70. PerDoBMissing

Class:

Missing

Priority:

High

Message:

Missing data - DoB $DoB.q

Mark:

PER.DoB

Description:

Missing data - Date of Birth (DoB)

SQL:

select State,
       RegId,
       OrgId,
       ClusId,
       SUId,
       PersIdFlag,
       PersId,
       DoB
  from PER
 where DoB is null

Data Elements:

4.1.71. PerIndigStDiffers

Class:

Inconsistent

Priority:

High

Message:

Person has $attr_count values 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:

4.1.72. 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:

4.1.73. PerSexDiffers

Class:

Inconsistent

Priority:

High

Message:

Person has $attr_count values 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:

4.1.74. PerSexGenderMissing

Class:

Missing

Priority:

High

Message:

Missing data - at least one of Sex $Sex.q or Gender $Gender.q required

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:

4.1.75. RegInSklOnly

Class:: Skeleton
Priority:: High
Message:: Reg $name expected 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

4.1.76. 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:

RegId
State

Virtual Elements:

RegIndigStPropNonAus

4.1.77. RegNotInSkl

Class:: Skeleton
Priority:: High
Message:: Reg $name not in SKL data
Description:: Region not in skeleton reference data - A matching Ambulatory Region was not found in the skeleton data

4.1.78. RegRegNameMissing

Class:

Missing

Priority:

High

Message:

Missing data - RegName $RegName.q

Mark:

REG.RegName

Description:

Missing data - Region Name (RegName)

SQL:

select State,
       RegId,
       RegName
  from REG
 where RegName is null

Data Elements:

4.1.79. SectorPrivate

Class:

Invalid

Priority:

High

Message:

Sector is $Sector.qt (Private)

Mark:

SERV.Sector

Description:

Service Unit Sector must be 1 (Public)

SQL:

select State,
       RegId,
       OrgId,
       ClusId,
       SUId,
       Sector
  from SERV
 where Sector = '2'

Data Elements:

4.1.80. 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:

4.1.81. 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

4.1.82. 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

4.1.83. 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:

4.1.84. ServRenamed

Class:

Historical

Priority:

Medium

Message:

Serv renamed from $hist_name.qt to $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:

4.1.85. ServSUNameMissing

Class:

Missing

Priority:

High

Message:

Missing data - SUName $SUName.q

Mark:

SERV.SUName

Description:

Missing data - Ambulatory Service Unit Name (SUName)

SQL:

select State,
       RegId,
       OrgId,
       ClusId,
       SUId,
       SUName
  from SERV
 where SUName is null

Data Elements:

4.1.86. ServSectorMissing

Class:

Missing

Priority:

High

Message:

Missing data - Sector $Sector.q

Mark:

SERV.Sector

Description:

Missing data - Sector (Sector)

SQL:

select State,
       RegId,
       OrgId,
       ClusId,
       SUId,
       Sector
  from SERV
 where Sector is null

Data Elements:

4.1.87. ServTargetPopChanged

Class:

Historical

Priority:

High

Message:

Service Unit TargetPop changed from $hist_TargetPop to $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:

4.1.88. 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:

4.1.89. StConCount

SQL:

select sum(ServConCountTotal) as StConCountTotal
  from ServConCount

4.1.90. 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:

4.1.91. 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:

State

Virtual Elements:

HrConCountGrowth

4.1.92. 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:

State

Virtual Elements:

HrContDurTotalGrowth

4.1.93. 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:

4.1.94. SussHrDoBCount

Class:

Anomaly

Priority:

Low

Message:

More than double the average birthrate ($DoBCount vs $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:

4.2. Virtual Elements

4.2.1. ConAge

Base:

CON

Title:

Age at Contact

SQL:

select State,
       RegId,
       OrgId,
       ClusId,
       SUId,
       PersIdFlag,
       PersId,
       RecordId,
       FLOOR((ContDt - DoB) / 365.25) as Age
  from CON
  join PER using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId)
 where DoB != '9999-09-09'
   and DoBFlag in ('1', '2')

Rules:

4.2.2. HrCoBPropNotAus

Base:

HR

Title:

State Birth Country not Australia Proportion

SQL:

select State,
       sd_div_safe(coalesce(sum(CASE WHEN CoB not in ('1100','1101','1102','1199') AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where CoB is not null
 group by State

Rules:

HighExtCoBProp

4.2.3. HrCoBPropSuppRegistered

Base:

HR

Title:

State Birth Country is Supplementary

SQL:

select State,
       sd_div_safe(coalesce(sum(CASE WHEN CoB ~ '^d{1,3} *' OR CoB LIKE '%00' AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where CoB is not null
 group by State

Rules:

HighSuppCoBProp

4.2.4. HrConCount

Base:

HR

Title:

HR Count for Con

SQL:

select State,
       coalesce(Count, 0) as Count
  from HR
  left join (
        select State,
               count(*) as Count
          from CON
         group by State
       ) as foo using (State)

4.2.5. HrConCountChange

Base:

HR

Title:

HR Change in count for Con

SQL:

select State,
       (New.Count - Old.Count) as Change
  from HrConCount as New
  join hist.HrConCount as Old using (State)

4.2.6. HrConCountGrowth

Base:

HR

Title:

HR Growth in count for Con

SQL:

select State,
       sd_div_safe(New.Count - Old.Count, Old.Count, 3) as Growth
  from HrConCount as New
  join hist.HrConCount as Old using (State)

Rules:

StConGrowthVaries

4.2.7. HrContDurChange

Base:

HR

Title:

HR Change in total contact duration

SQL:

select State,
       (New.Total - Old.Total) as Change
  from HrContDurTotal as New
  join hist.HrContDurTotal as Old using (State)

4.2.8. HrContDurTotal

Base:

HR

Title:

HR Total contact duration

SQL:

select State,
       coalesce(Total, 0) as Total
  from HR
  left join (
        select State,
               sum(ContDur) as Total
          from CON
         group by State
       ) as foo using (State)

4.2.9. HrContDurTotalGrowth

Base:

HR

Title:

HR Growth in total contact duration

SQL:

select State,
       sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth
  from HrContDurTotal as New
  join hist.HrContDurTotal as Old using (State)

Rules:

StContDurGrowthVaries

4.2.10. HrContParticProp

Base:

HR

Title:

State Participartion Proportion

SQL:

select State,
       sd_div_safe(coalesce(sum(CASE WHEN ContPartic != '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where ContPartic is not null
 group by State

Rules:

ContParticChange

4.2.11. HrDoBFlagPropRegistered

Base:

HR

Title:

State Estimated DoB Proportion

SQL:

select State,
       sd_div_safe(coalesce(sum(CASE WHEN DoBFlag in ('2','8','9') AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where DoBFlag is not null
 group by State

Rules:

HighEstDoBFlagProp

4.2.12. HrLegalStInvolCount

Base:

HR

Title:

HR Count for LegalSt Invol (1)

SQL:

select State,
       coalesce(Count, 0) as Count
  from HR
  left join (
        select State,
               count(*) as Count
          from CON
         where LegalSt = '1'
         group by State
       ) as foo using (State)

Rules:

BadHrLegalSt12Counts

4.2.13. HrLegalStInvolRatio

Base:

HR

Title:

HR Ratio of LegalSt Invol (1)

SQL:

select State,
       sd_div_safe(HrLegalStInvolCount.Count, HrConCount.Count, 3) as Ratio
  from HrLegalStInvolCount
  join HrConCount using (State)

4.2.14. HrLegalStInvolRatioChange

Base:

HR

Title:

HR Change in ratio of LegalSt Invol

SQL:

select State,
       (New.Ratio - Old.Ratio) as Change
  from HrLegalStInvolRatio as New
  join hist.HrLegalStInvolRatio as Old using (State)

4.2.15. HrLegalStInvolRatioGrowth

Base:

HR

Title:

HR Growth in ratio of LegalSt Invol

SQL:

select State,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from HrLegalStInvolRatio as New
  join hist.HrLegalStInvolRatio as Old using (State)

4.2.16. HrLegalStPropRegistered

Base:

HR

Title:

State Missing Legal Status Proportion

SQL:

select State,
       sd_div_safe(coalesce(sum(CASE WHEN LegalSt = '9' AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where LegalSt is not null
 group by State

Rules:

HighMissingLegalStProp

4.2.17. HrLegalStVolCount

Base:

HR

Title:

HR Count for LegalSt Vol (2)

SQL:

select State,
       coalesce(Count, 0) as Count
  from HR
  left join (
        select State,
               count(*) as Count
          from CON
         where LegalSt = '2'
         group by State
       ) as foo using (State)

Rules:

BadHrLegalSt12Counts

4.2.18. HrLegalStVolRatio

Base:

HR

Title:

HR Ratio of LegalSt Vol (2)

SQL:

select State,
       sd_div_safe(HrLegalStVolCount.Count, HrConCount.Count, 3) as Ratio
  from HrLegalStVolCount
  join HrConCount using (State)

4.2.19. HrLegalStVolRatioChange

Base:

HR

Title:

HR Change in ratio of LegalSt Vol

SQL:

select State,
       (New.Ratio - Old.Ratio) as Change
  from HrLegalStVolRatio as New
  join hist.HrLegalStVolRatio as Old using (State)

4.2.20. HrLegalStVolRatioGrowth

Base:

HR

Title:

HR Growth in ratio of LegalSt Vol

SQL:

select State,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from HrLegalStVolRatio as New
  join hist.HrLegalStVolRatio as Old using (State)

4.2.21. HrPerCount

Base:

HR

Title:

HR Count for Per

SQL:

select State,
       coalesce(Count, 0) as Count
  from HR
  left join (
        select State,
               count(*) as Count
          from PER
         group by State
       ) as foo using (State)

4.2.22. HrPerCountChange

Base:

HR

Title:

HR Change in count for Per

SQL:

select State,
       (New.Count - Old.Count) as Change
  from HrPerCount as New
  join hist.HrPerCount as Old using (State)

4.2.23. HrPerCountGrowth

Base:

HR

Title:

HR Growth in count for Per

SQL:

select State,
       sd_div_safe(New.Count - Old.Count, Old.Count, 3) as Growth
  from HrPerCount as New
  join hist.HrPerCount as Old using (State)

4.2.24. HrPersIdFlagDummyCount

Base:

HR

Title:

HR Count for PersIdFlag Dummy (2)

SQL:

select State,
       coalesce(Count, 0) as Count
  from HR
  left join (
        select State,
               count(*) as Count
          from PER
         where PersIdFlag = '2'
         group by State
       ) as foo using (State)

Rules:

BadHrPersIdFlag21Counts

4.2.25. HrPersIdFlagDummyRatio

Base:

HR

Title:

HR Ratio of PersIdFlag Dummy (2)

SQL:

select State,
       sd_div_safe(HrPersIdFlagDummyCount.Count, HrPerCount.Count, 3) as Ratio
  from HrPersIdFlagDummyCount
  join HrPerCount using (State)

4.2.26. HrPersIdFlagDummyRatioChange

Base:

HR

Title:

HR Change in ratio of PersIdFlag Dummy

SQL:

select State,
       (New.Ratio - Old.Ratio) as Change
  from HrPersIdFlagDummyRatio as New
  join hist.HrPersIdFlagDummyRatio as Old using (State)

4.2.27. HrPersIdFlagDummyRatioGrowth

Base:

HR

Title:

HR Growth in ratio of PersIdFlag Dummy

SQL:

select State,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from HrPersIdFlagDummyRatio as New
  join hist.HrPersIdFlagDummyRatio as Old using (State)

4.2.28. HrPersIdFlagProp

Base:

HR

Title:

State Dummy PersId Proportion

SQL:

select State,
       sd_div_safe(coalesce(sum(CASE WHEN PersIdFlag = '2' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where PersIdFlag is not null
 group by State

Rules:

HighPersIdFlagProp

4.2.29. HrPersIdFlagRealCount

Base:

HR

Title:

HR Count for PersIdFlag Real (1)

SQL:

select State,
       coalesce(Count, 0) as Count
  from HR
  left join (
        select State,
               count(*) as Count
          from PER
         where PersIdFlag = '1'
         group by State
       ) as foo using (State)

Rules:

BadHrPersIdFlag21Counts

4.2.30. HrPersIdFlagRealRatio

Base:

HR

Title:

HR Ratio of PersIdFlag Real (1)

SQL:

select State,
       sd_div_safe(HrPersIdFlagRealCount.Count, HrPerCount.Count, 3) as Ratio
  from HrPersIdFlagRealCount
  join HrPerCount using (State)

4.2.31. HrPersIdFlagRealRatioChange

Base:

HR

Title:

HR Change in ratio of PersIdFlag Real

SQL:

select State,
       (New.Ratio - Old.Ratio) as Change
  from HrPersIdFlagRealRatio as New
  join hist.HrPersIdFlagRealRatio as Old using (State)

4.2.32. HrPersIdFlagRealRatioGrowth

Base:

HR

Title:

HR Growth in ratio of PersIdFlag Real

SQL:

select State,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from HrPersIdFlagRealRatio as New
  join hist.HrPersIdFlagRealRatio as Old using (State)

4.2.33. HrResAreaProp

Base:

HR

Title:

State in-state SA2 Proportion

SQL:

select State,
       sd_div_safe(coalesce(sum(CASE WHEN State != substr(ResArea, 1, 1) AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where ResArea is not null
 group by State

Rules:

HighExtSA2Prop

4.2.34. OrgCoBPropNotAus

Base:

ORG

Title:

Organisation Birth Country not Australia Proportion

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(coalesce(sum(CASE WHEN CoB not in ('1100','1101','1102','1199') AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where CoB is not null
 group by State,
          RegId,
          OrgId

4.2.35. OrgCoBPropSuppRegistered

Base:

ORG

Title:

Organisation Birth Country is Supplementary

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(coalesce(sum(CASE WHEN CoB ~ '^d{1,3} *' OR CoB LIKE '%00' AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where CoB is not null
 group by State,
          RegId,
          OrgId

4.2.36. OrgConCount

Base:

ORG

Title:

ORG Count for Con

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from CON
         group by State,
                  RegId,
                  OrgId
       ) as foo using (State, RegId, OrgId)

4.2.37. OrgConCountChange

Base:

ORG

Title:

ORG Change in count for Con

SQL:

select State,
       RegId,
       OrgId,
       (New.Count - Old.Count) as Change
  from OrgConCount as New
  join hist.OrgConCount as Old using (State, RegId, OrgId)

4.2.38. OrgConCountGrowth

Base:

ORG

Title:

ORG Growth in count for Con

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(New.Count - Old.Count, Old.Count, 3) as Growth
  from OrgConCount as New
  join hist.OrgConCount as Old using (State, RegId, OrgId)

4.2.39. OrgContDurChange

Base:

ORG

Title:

ORG Change in total contact duration

SQL:

select State,
       RegId,
       OrgId,
       (New.Total - Old.Total) as Change
  from OrgContDurTotal as New
  join hist.OrgContDurTotal as Old using (State, RegId, OrgId)

4.2.40. OrgContDurTotal

Base:

ORG

Title:

ORG Total contact duration

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Total, 0) as Total
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               sum(ContDur) as Total
          from CON
         group by State,
                  RegId,
                  OrgId
       ) as foo using (State, RegId, OrgId)

4.2.41. OrgContDurTotalGrowth

Base:

ORG

Title:

ORG Growth in total contact duration

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth
  from OrgContDurTotal as New
  join hist.OrgContDurTotal as Old using (State, RegId, OrgId)

4.2.42. OrgContParticProp

Base:

ORG

Title:

Organisation Participartion Proportion

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(coalesce(sum(CASE WHEN ContPartic != '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where ContPartic is not null
 group by State,
          RegId,
          OrgId

4.2.43. OrgDoBFlagPropRegistered

Base:

ORG

Title:

Organisation Estimated DoB Proportion

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(coalesce(sum(CASE WHEN DoBFlag in ('2','8','9') AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where DoBFlag is not null
 group by State,
          RegId,
          OrgId

4.2.44. OrgHasServCA

Base:

ORG

Title:

SERV Child and Adolescent below ORG

SQL:

select State,
       RegId,
       OrgId,
       Count
  from OrgServCACount
 where Count > 0

4.2.45. OrgHasServFor

Base:

ORG

Title:

SERV Forensic below ORG

SQL:

select State,
       RegId,
       OrgId,
       Count
  from OrgServForCount
 where Count > 0

4.2.46. OrgHasServGen

Base:

ORG

Title:

SERV General below ORG

SQL:

select State,
       RegId,
       OrgId,
       Count
  from OrgServGenCount
 where Count > 0

4.2.47. OrgHasServOld

Base:

ORG

Title:

SERV Older person below ORG

SQL:

select State,
       RegId,
       OrgId,
       Count
  from OrgServOldCount
 where Count > 0

4.2.48. OrgHasServYth

Base:

ORG

Title:

SERV Youth below ORG

SQL:

select State,
       RegId,
       OrgId,
       Count
  from OrgServYthCount
 where Count > 0

4.2.49. OrgIndigStPropNonAus

Base:

ORG

Title:

Organisation Indigenous born outside Australia Proportion

SQL:

select State,
       RegId,
       OrgId,
       sum(CoB not in ('1100','1101','1102','1199')::INT) / count(*)::FLOAT as prop
  from PER
 where IndigSt in ('1','2','3')
   and CoB is not null
 group by State,
          RegId,
          OrgId

Rules:

HighIndigNonAustProp

4.2.50. OrgLegalStInvolCount

Base:

ORG

Title:

ORG Count for LegalSt Invol (1)

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from CON
         where LegalSt = '1'
         group by State,
                  RegId,
                  OrgId
       ) as foo using (State, RegId, OrgId)

4.2.51. OrgLegalStInvolRatio

Base:

ORG

Title:

ORG Ratio of LegalSt Invol (1)

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(OrgLegalStInvolCount.Count, OrgConCount.Count, 3) as Ratio
  from OrgLegalStInvolCount
  join OrgConCount using (State, RegId, OrgId)

4.2.52. OrgLegalStInvolRatioChange

Base:

ORG

Title:

ORG Change in ratio of LegalSt Invol

SQL:

select State,
       RegId,
       OrgId,
       (New.Ratio - Old.Ratio) as Change
  from OrgLegalStInvolRatio as New
  join hist.OrgLegalStInvolRatio as Old using (State, RegId, OrgId)

4.2.53. OrgLegalStInvolRatioGrowth

Base:

ORG

Title:

ORG Growth in ratio of LegalSt Invol

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from OrgLegalStInvolRatio as New
  join hist.OrgLegalStInvolRatio as Old using (State, RegId, OrgId)

Rules:

OrgInvolGrowthVaries

4.2.54. OrgLegalStPropRegistered

Base:

ORG

Title:

Organisation Missing Legal Status Proportion

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(coalesce(sum(CASE WHEN LegalSt = '9' AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where LegalSt is not null
 group by State,
          RegId,
          OrgId

4.2.55. OrgLegalStVolCount

Base:

ORG

Title:

ORG Count for LegalSt Vol (2)

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from CON
         where LegalSt = '2'
         group by State,
                  RegId,
                  OrgId
       ) as foo using (State, RegId, OrgId)

4.2.56. OrgLegalStVolRatio

Base:

ORG

Title:

ORG Ratio of LegalSt Vol (2)

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(OrgLegalStVolCount.Count, OrgConCount.Count, 3) as Ratio
  from OrgLegalStVolCount
  join OrgConCount using (State, RegId, OrgId)

4.2.57. OrgLegalStVolRatioChange

Base:

ORG

Title:

ORG Change in ratio of LegalSt Vol

SQL:

select State,
       RegId,
       OrgId,
       (New.Ratio - Old.Ratio) as Change
  from OrgLegalStVolRatio as New
  join hist.OrgLegalStVolRatio as Old using (State, RegId, OrgId)

4.2.58. OrgLegalStVolRatioGrowth

Base:

ORG

Title:

ORG Growth in ratio of LegalSt Vol

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from OrgLegalStVolRatio as New
  join hist.OrgLegalStVolRatio as Old using (State, RegId, OrgId)

4.2.59. OrgPerCount

Base:

ORG

Title:

ORG Count for Per

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from PER
         group by State,
                  RegId,
                  OrgId
       ) as foo using (State, RegId, OrgId)

4.2.60. OrgPerCountChange

Base:

ORG

Title:

ORG Change in count for Per

SQL:

select State,
       RegId,
       OrgId,
       (New.Count - Old.Count) as Change
  from OrgPerCount as New
  join hist.OrgPerCount as Old using (State, RegId, OrgId)

4.2.61. OrgPerCountGrowth

Base:

ORG

Title:

ORG Growth in count for Per

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(New.Count - Old.Count, Old.Count, 3) as Growth
  from OrgPerCount as New
  join hist.OrgPerCount as Old using (State, RegId, OrgId)

4.2.62. OrgPersIdFlagDummyCount

Base:

ORG

Title:

ORG Count for PersIdFlag Dummy (2)

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from PER
         where PersIdFlag = '2'
         group by State,
                  RegId,
                  OrgId
       ) as foo using (State, RegId, OrgId)

4.2.63. OrgPersIdFlagDummyRatio

Base:

ORG

Title:

ORG Ratio of PersIdFlag Dummy (2)

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(OrgPersIdFlagDummyCount.Count, OrgPerCount.Count, 3) as Ratio
  from OrgPersIdFlagDummyCount
  join OrgPerCount using (State, RegId, OrgId)

4.2.64. OrgPersIdFlagDummyRatioChange

Base:

ORG

Title:

ORG Change in ratio of PersIdFlag Dummy

SQL:

select State,
       RegId,
       OrgId,
       (New.Ratio - Old.Ratio) as Change
  from OrgPersIdFlagDummyRatio as New
  join hist.OrgPersIdFlagDummyRatio as Old using (State, RegId, OrgId)

4.2.65. OrgPersIdFlagDummyRatioGrowth

Base:

ORG

Title:

ORG Growth in ratio of PersIdFlag Dummy

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from OrgPersIdFlagDummyRatio as New
  join hist.OrgPersIdFlagDummyRatio as Old using (State, RegId, OrgId)

4.2.66. OrgPersIdFlagProp

Base:

ORG

Title:

Organisation Dummy PersId Proportion

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(coalesce(sum(CASE WHEN PersIdFlag = '2' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where PersIdFlag is not null
 group by State,
          RegId,
          OrgId

4.2.67. OrgPersIdFlagRealCount

Base:

ORG

Title:

ORG Count for PersIdFlag Real (1)

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from PER
         where PersIdFlag = '1'
         group by State,
                  RegId,
                  OrgId
       ) as foo using (State, RegId, OrgId)

4.2.68. OrgPersIdFlagRealRatio

Base:

ORG

Title:

ORG Ratio of PersIdFlag Real (1)

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(OrgPersIdFlagRealCount.Count, OrgPerCount.Count, 3) as Ratio
  from OrgPersIdFlagRealCount
  join OrgPerCount using (State, RegId, OrgId)

4.2.69. OrgPersIdFlagRealRatioChange

Base:

ORG

Title:

ORG Change in ratio of PersIdFlag Real

SQL:

select State,
       RegId,
       OrgId,
       (New.Ratio - Old.Ratio) as Change
  from OrgPersIdFlagRealRatio as New
  join hist.OrgPersIdFlagRealRatio as Old using (State, RegId, OrgId)

4.2.70. OrgPersIdFlagRealRatioGrowth

Base:

ORG

Title:

ORG Growth in ratio of PersIdFlag Real

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from OrgPersIdFlagRealRatio as New
  join hist.OrgPersIdFlagRealRatio as Old using (State, RegId, OrgId)

4.2.71. OrgResAreaProp

Base:

ORG

Title:

Organisation in-state SA2 Proportion

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(coalesce(sum(CASE WHEN State != substr(ResArea, 1, 1) AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where ResArea is not null
 group by State,
          RegId,
          OrgId

4.2.72. OrgServCACount

Base:

ORG

Title:

SERV Child and Adolescent Count at ORG Level

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from SERV
         where TargetPop = '1'
         group by State,
                  RegId,
                  OrgId
       ) as tmpinner using (State, RegId, OrgId)

4.2.73. OrgServForCount

Base:

ORG

Title:

SERV Forensic Count at ORG Level

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from SERV
         where TargetPop = '3'
         group by State,
                  RegId,
                  OrgId
       ) as tmpinner using (State, RegId, OrgId)

4.2.74. OrgServGenCount

Base:

ORG

Title:

SERV General Count at ORG Level

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from SERV
         where TargetPop = '4'
         group by State,
                  RegId,
                  OrgId
       ) as tmpinner using (State, RegId, OrgId)

4.2.75. OrgServOldCount

Base:

ORG

Title:

SERV Older person Count at ORG Level

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from SERV
         where TargetPop = '2'
         group by State,
                  RegId,
                  OrgId
       ) as tmpinner using (State, RegId, OrgId)

4.2.76. OrgServYthCount

Base:

ORG

Title:

SERV Youth Count at ORG Level

SQL:

select State,
       RegId,
       OrgId,
       coalesce(Count, 0) as Count
  from ORG
  left join (
        select State,
               RegId,
               OrgId,
               count(*) as Count
          from SERV
         where TargetPop = '5'
         group by State,
                  RegId,
                  OrgId
       ) as tmpinner using (State, RegId, OrgId)

4.2.77. OrgUnknownContParticProp

Base:

ORG

Title:

Organisation Participartion Proportion

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(coalesce(sum(CASE WHEN ContPartic = '8' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where ContPartic is not null
 group by State,
          RegId,
          OrgId

Rules:

HighUnknownContParticProp

4.2.78. OrgUnknownContSessTypeProp

Base:

ORG

Title:

Organisation Session Type Proportion

SQL:

select State,
       RegId,
       OrgId,
       sd_div_safe(coalesce(sum(CASE WHEN ContSessType = '8' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where ContSessType is not null
 group by State,
          RegId,
          OrgId

Rules:

HighUnknownContSessTypeProp

4.2.79. RegCoBPropNotAus

Base:

REG

Title:

Region Birth Country not Australia Proportion

SQL:

select State,
       RegId,
       sd_div_safe(coalesce(sum(CASE WHEN CoB not in ('1100','1101','1102','1199') AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where CoB is not null
 group by State,
          RegId

4.2.80. RegCoBPropSuppRegistered

Base:

REG

Title:

Region Birth Country is Supplementary

SQL:

select State,
       RegId,
       sd_div_safe(coalesce(sum(CASE WHEN CoB ~ '^d{1,3} *' OR CoB LIKE '%00' AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where CoB is not null
 group by State,
          RegId

4.2.81. RegConCount

Base:

REG

Title:

REG Count for Con

SQL:

select State,
       RegId,
       coalesce(Count, 0) as Count
  from REG
  left join (
        select State,
               RegId,
               count(*) as Count
          from CON
         group by State,
                  RegId
       ) as foo using (State, RegId)

4.2.82. RegConCountChange

Base:

REG

Title:

REG Change in count for Con

SQL:

select State,
       RegId,
       (New.Count - Old.Count) as Change
  from RegConCount as New
  join hist.RegConCount as Old using (State, RegId)

4.2.83. RegConCountGrowth

Base:

REG

Title:

REG Growth in count for Con

SQL:

select State,
       RegId,
       sd_div_safe(New.Count - Old.Count, Old.Count, 3) as Growth
  from RegConCount as New
  join hist.RegConCount as Old using (State, RegId)

4.2.84. RegContDurChange

Base:

REG

Title:

REG Change in total contact duration

SQL:

select State,
       RegId,
       (New.Total - Old.Total) as Change
  from RegContDurTotal as New
  join hist.RegContDurTotal as Old using (State, RegId)

4.2.85. RegContDurTotal

Base:

REG

Title:

REG Total contact duration

SQL:

select State,
       RegId,
       coalesce(Total, 0) as Total
  from REG
  left join (
        select State,
               RegId,
               sum(ContDur) as Total
          from CON
         group by State,
                  RegId
       ) as foo using (State, RegId)

4.2.86. RegContDurTotalGrowth

Base:

REG

Title:

REG Growth in total contact duration

SQL:

select State,
       RegId,
       sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth
  from RegContDurTotal as New
  join hist.RegContDurTotal as Old using (State, RegId)

4.2.87. RegContParticProp

Base:

REG

Title:

Region Participartion Proportion

SQL:

select State,
       RegId,
       sd_div_safe(coalesce(sum(CASE WHEN ContPartic != '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where ContPartic is not null
 group by State,
          RegId

4.2.88. RegDoBFlagPropRegistered

Base:

REG

Title:

Region Estimated DoB Proportion

SQL:

select State,
       RegId,
       sd_div_safe(coalesce(sum(CASE WHEN DoBFlag in ('2','8','9') AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where DoBFlag is not null
 group by State,
          RegId

4.2.89. RegIndigStPropNonAus

Base:

REG

Title:

Region Indigenous Born Outside Australia Proportion

SQL:

select State,
       RegId,
       sum(CoB not in ('1100','1101','1102','1199')::INT) / count(*)::FLOAT as prop
  from PER
 where IndigSt in ('1','2','3')
   and CoB is not null
 group by State,
          RegId

Rules:

RegIndigStGrowthVaries

4.2.90. RegLegalStInvolCount

Base:

REG

Title:

REG Count for LegalSt Invol (1)

SQL:

select State,
       RegId,
       coalesce(Count, 0) as Count
  from REG
  left join (
        select State,
               RegId,
               count(*) as Count
          from CON
         where LegalSt = '1'
         group by State,
                  RegId
       ) as foo using (State, RegId)

4.2.91. RegLegalStInvolRatio

Base:

REG

Title:

REG Ratio of LegalSt Invol (1)

SQL:

select State,
       RegId,
       sd_div_safe(RegLegalStInvolCount.Count, RegConCount.Count, 3) as Ratio
  from RegLegalStInvolCount
  join RegConCount using (State, RegId)

4.2.92. RegLegalStInvolRatioChange

Base:

REG

Title:

REG Change in ratio of LegalSt Invol

SQL:

select State,
       RegId,
       (New.Ratio - Old.Ratio) as Change
  from RegLegalStInvolRatio as New
  join hist.RegLegalStInvolRatio as Old using (State, RegId)

4.2.93. RegLegalStInvolRatioGrowth

Base:

REG

Title:

REG Growth in ratio of LegalSt Invol

SQL:

select State,
       RegId,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from RegLegalStInvolRatio as New
  join hist.RegLegalStInvolRatio as Old using (State, RegId)

4.2.94. RegLegalStPropRegistered

Base:

REG

Title:

Region Missing Legal Status Proportion

SQL:

select State,
       RegId,
       sd_div_safe(coalesce(sum(CASE WHEN LegalSt = '9' AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where LegalSt is not null
 group by State,
          RegId

4.2.95. RegLegalStVolCount

Base:

REG

Title:

REG Count for LegalSt Vol (2)

SQL:

select State,
       RegId,
       coalesce(Count, 0) as Count
  from REG
  left join (
        select State,
               RegId,
               count(*) as Count
          from CON
         where LegalSt = '2'
         group by State,
                  RegId
       ) as foo using (State, RegId)

4.2.96. RegLegalStVolRatio

Base:

REG

Title:

REG Ratio of LegalSt Vol (2)

SQL:

select State,
       RegId,
       sd_div_safe(RegLegalStVolCount.Count, RegConCount.Count, 3) as Ratio
  from RegLegalStVolCount
  join RegConCount using (State, RegId)

4.2.97. RegLegalStVolRatioChange

Base:

REG

Title:

REG Change in ratio of LegalSt Vol

SQL:

select State,
       RegId,
       (New.Ratio - Old.Ratio) as Change
  from RegLegalStVolRatio as New
  join hist.RegLegalStVolRatio as Old using (State, RegId)

4.2.98. RegLegalStVolRatioGrowth

Base:

REG

Title:

REG Growth in ratio of LegalSt Vol

SQL:

select State,
       RegId,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from RegLegalStVolRatio as New
  join hist.RegLegalStVolRatio as Old using (State, RegId)

4.2.99. RegPerCount

Base:

REG

Title:

REG Count for Per

SQL:

select State,
       RegId,
       coalesce(Count, 0) as Count
  from REG
  left join (
        select State,
               RegId,
               count(*) as Count
          from PER
         group by State,
                  RegId
       ) as foo using (State, RegId)

4.2.100. RegPerCountChange

Base:

REG

Title:

REG Change in count for Per

SQL:

select State,
       RegId,
       (New.Count - Old.Count) as Change
  from RegPerCount as New
  join hist.RegPerCount as Old using (State, RegId)

4.2.101. RegPerCountGrowth

Base:

REG

Title:

REG Growth in count for Per

SQL:

select State,
       RegId,
       sd_div_safe(New.Count - Old.Count, Old.Count, 3) as Growth
  from RegPerCount as New
  join hist.RegPerCount as Old using (State, RegId)

4.2.102. RegPersIdFlagDummyCount

Base:

REG

Title:

REG Count for PersIdFlag Dummy (2)

SQL:

select State,
       RegId,
       coalesce(Count, 0) as Count
  from REG
  left join (
        select State,
               RegId,
               count(*) as Count
          from PER
         where PersIdFlag = '2'
         group by State,
                  RegId
       ) as foo using (State, RegId)

4.2.103. RegPersIdFlagDummyRatio

Base:

REG

Title:

REG Ratio of PersIdFlag Dummy (2)

SQL:

select State,
       RegId,
       sd_div_safe(RegPersIdFlagDummyCount.Count, RegPerCount.Count, 3) as Ratio
  from RegPersIdFlagDummyCount
  join RegPerCount using (State, RegId)

4.2.104. RegPersIdFlagDummyRatioChange

Base:

REG

Title:

REG Change in ratio of PersIdFlag Dummy

SQL:

select State,
       RegId,
       (New.Ratio - Old.Ratio) as Change
  from RegPersIdFlagDummyRatio as New
  join hist.RegPersIdFlagDummyRatio as Old using (State, RegId)

4.2.105. RegPersIdFlagDummyRatioGrowth

Base:

REG

Title:

REG Growth in ratio of PersIdFlag Dummy

SQL:

select State,
       RegId,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from RegPersIdFlagDummyRatio as New
  join hist.RegPersIdFlagDummyRatio as Old using (State, RegId)

4.2.106. RegPersIdFlagProp

Base:

REG

Title:

Region Dummy PersId Proportion

SQL:

select State,
       RegId,
       sd_div_safe(coalesce(sum(CASE WHEN PersIdFlag = '2' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from PER
 where PersIdFlag is not null
 group by State,
          RegId

4.2.107. RegPersIdFlagRealCount

Base:

REG

Title:

REG Count for PersIdFlag Real (1)

SQL:

select State,
       RegId,
       coalesce(Count, 0) as Count
  from REG
  left join (
        select State,
               RegId,
               count(*) as Count
          from PER
         where PersIdFlag = '1'
         group by State,
                  RegId
       ) as foo using (State, RegId)

4.2.108. RegPersIdFlagRealRatio

Base:

REG

Title:

REG Ratio of PersIdFlag Real (1)

SQL:

select State,
       RegId,
       sd_div_safe(RegPersIdFlagRealCount.Count, RegPerCount.Count, 3) as Ratio
  from RegPersIdFlagRealCount
  join RegPerCount using (State, RegId)

4.2.109. RegPersIdFlagRealRatioChange

Base:

REG

Title:

REG Change in ratio of PersIdFlag Real

SQL:

select State,
       RegId,
       (New.Ratio - Old.Ratio) as Change
  from RegPersIdFlagRealRatio as New
  join hist.RegPersIdFlagRealRatio as Old using (State, RegId)

4.2.110. RegPersIdFlagRealRatioGrowth

Base:

REG

Title:

REG Growth in ratio of PersIdFlag Real

SQL:

select State,
       RegId,
       sd_div_safe(New.Ratio - Old.Ratio, Old.Ratio, 3) as Growth
  from RegPersIdFlagRealRatio as New
  join hist.RegPersIdFlagRealRatio as Old using (State, RegId)

4.2.111. RegResAreaProp

Base:

REG

Title:

Region in-state SA2 Proportion

SQL:

select State,
       RegId,
       sd_div_safe(coalesce(sum(CASE WHEN State != substr(ResArea, 1, 1) AND PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop
  from CON
 where ResArea is not null
 group by State,
          RegId