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:

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

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

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)

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)

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

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)

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)

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

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

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

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

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)

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)

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)

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

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

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)

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)

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)

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)

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)

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)

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

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)

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)

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)

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

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

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)

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)

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)

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

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

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

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)

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)

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)

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)

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)

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)

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

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

OrgHasServCA

Base:

ORG

Title:

SERV Child and Adolescent below ORG

SQL:

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

OrgHasServFor

Base:

ORG

Title:

SERV Forensic below ORG

SQL:

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

OrgHasServGen

Base:

ORG

Title:

SERV General below ORG

SQL:

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

OrgHasServOld

Base:

ORG

Title:

SERV Older person below ORG

SQL:

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

OrgHasServYth

Base:

ORG

Title:

SERV Youth below ORG

SQL:

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

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

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)

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)

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)

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

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

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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

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)

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)

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)

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)

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

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)

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)

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)

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)

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)

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

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

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

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

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)

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)

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)

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)

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)

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)

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

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

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

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)

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)

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)

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)

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

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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

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)

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)

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)

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)

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