Community Mental Health Care Minimum Data Set — Version 04.20
Main Content
CMHC version 04.20: Virtual Elements
| Name | Base | Title | SQL |
|---|---|---|---|
| ConAge | CON | Age at Contact | 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') |
| OrgIndigStPropNonAus | ORG | Organisation Indigenous born outside Australia Proportion | 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 |
| HrContParticProp | HR | State Participartion Proportion | 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 |
| RegContParticProp | REG | Region Participartion Proportion | 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 |
| OrgContParticProp | ORG | Organisation Participartion Proportion | 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 |
| HrPersSLAProp | HR | State in-state SLA Proportion | select State, sd_div_safe( coalesce(sum(CASE WHEN State != substr(PersSLA, 1, 1) AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from CON where PersSLA is not null group by State |
| RegPersSLAProp | REG | Region in-state SLA Proportion | select State, RegId, sd_div_safe( coalesce(sum(CASE WHEN State != substr(PersSLA, 1, 1) AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from CON where PersSLA is not null group by State, RegId |
| OrgPersSLAProp | ORG | Organisation in-state SLA Proportion | select State, RegId, OrgId, sd_div_safe( coalesce(sum(CASE WHEN State != substr(PersSLA, 1, 1) AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from CON where PersSLA is not null group by State, RegId, OrgId |
| HrLegalStPropRegistered | HR | State Missing Legal Status Proportion | select State, sd_div_safe( coalesce(sum(CASE WHEN LegalSt = '9' AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from CON where LegalSt is not null group by State |
| RegLegalStPropRegistered | REG | Region Missing Legal Status Proportion | select State, RegId, sd_div_safe( coalesce(sum(CASE WHEN LegalSt = '9' AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from CON where LegalSt is not null group by State, RegId |
| OrgLegalStPropRegistered | ORG | Organisation Missing Legal Status Proportion | select State, RegId, OrgId, sd_div_safe( coalesce(sum(CASE WHEN LegalSt = '9' AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from CON where LegalSt is not null group by State, RegId, OrgId |
| HrDoBFlagPropRegistered | HR | State Estimated DoB Proportion | select State, sd_div_safe( coalesce(sum(CASE WHEN DoBFlag in ('2','8','9') AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where DoBFlag is not null group by State |
| RegDoBFlagPropRegistered | REG | Region Estimated DoB Proportion | select State, RegId, sd_div_safe( coalesce(sum(CASE WHEN DoBFlag in ('2','8','9') AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where DoBFlag is not null group by State, RegId |
| OrgDoBFlagPropRegistered | ORG | Organisation Estimated DoB Proportion | select State, RegId, OrgId, sd_div_safe( coalesce(sum(CASE WHEN DoBFlag in ('2','8','9') AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where DoBFlag is not null group by State, RegId, OrgId |
| HrCoBPropNotAus | HR | State Birth Country not Australia Proportion | select State, sd_div_safe( coalesce(sum(CASE WHEN CoB not in ('1100','1101','1102','1199') AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where CoB is not null group by State |
| RegCoBPropNotAus | REG | Region Birth Country not Australia Proportion | select State, RegId, sd_div_safe( coalesce(sum(CASE WHEN CoB not in ('1100','1101','1102','1199') AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where CoB is not null group by State, RegId |
| OrgCoBPropNotAus | ORG | Organisation Birth Country not Australia Proportion | select State, RegId, OrgId, sd_div_safe( coalesce(sum(CASE WHEN CoB not in ('1100','1101','1102','1199') AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where CoB is not null group by State, RegId, OrgId |
| HrPersIdFlagProp | HR | State Dummy PersId Proportion | select State, sd_div_safe( coalesce(sum(CASE WHEN PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where PersIdFlag is not null group by State |
| RegPersIdFlagProp | REG | Region Dummy PersId Proportion | select State, RegId, sd_div_safe( coalesce(sum(CASE WHEN PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where PersIdFlag is not null group by State, RegId |
| OrgPersIdFlagProp | ORG | Organisation Dummy PersId Proportion | select State, RegId, OrgId, sd_div_safe( coalesce(sum(CASE WHEN PersIdFlag = '1' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where PersIdFlag is not null group by State, RegId, OrgId |
| HrCoBPropSuppRegistered | HR | State Birth Country is Supplementary | select State, sd_div_safe( coalesce(sum(CASE WHEN CoB ~ '^d{1,3} *' OR CoB LIKE '%00' AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where CoB is not null group by State |
| RegCoBPropSuppRegistered | REG | Region Birth Country is Supplementary | select State, RegId, sd_div_safe( coalesce(sum(CASE WHEN CoB ~ '^d{1,3} *' OR CoB LIKE '%00' AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where CoB is not null group by State, RegId |
| OrgCoBPropSuppRegistered | ORG | Organisation Birth Country is Supplementary | select State, RegId, OrgId, sd_div_safe( coalesce(sum(CASE WHEN CoB ~ '^d{1,3} *' OR CoB LIKE '%00' AND PersIdFlag = '0' THEN 1 ELSE 0 END), 0.0), count(*), 3) as prop from PER where CoB is not null group by State, RegId, OrgId |
| HrContDurTotal | HR | HR Total contact duration | 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) |
| RegContDurTotal | REG | REG Total contact duration | 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) |
| OrgContDurTotal | ORG | ORG Total contact duration | 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) |
| HrConCount | HR | HR Count for Con | 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) |
| HrPerCount | HR | HR Count for Per | 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) |
| RegConCount | REG | REG Count for Con | 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) |
| RegPerCount | REG | REG Count for Per | 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) |
| OrgConCount | ORG | ORG Count for Con | 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) |
| OrgPerCount | ORG | ORG Count for Per | 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) |
| OrgServCACount | ORG | SERV Child and Adolescent Count at ORG Level | 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) |
| OrgHasServCA | ORG | SERV Child and Adolescent below ORG | select State, RegId, OrgId, Count from OrgServCACount where Count > 0 |
| OrgServOldCount | ORG | SERV Older person Count at ORG Level | 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) |
| OrgHasServOld | ORG | SERV Older person below ORG | select State, RegId, OrgId, Count from OrgServOldCount where Count > 0 |
| OrgServForCount | ORG | SERV Forensic Count at ORG Level | 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) |
| OrgHasServFor | ORG | SERV Forensic below ORG | select State, RegId, OrgId, Count from OrgServForCount where Count > 0 |
| OrgServGenCount | ORG | SERV General Count at ORG Level | 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) |
| OrgHasServGen | ORG | SERV General below ORG | select State, RegId, OrgId, Count from OrgServGenCount where Count > 0 |
| HrPersIdFlagRealCount | HR | HR Count for PersIdFlag Real (0) | select State, coalesce(Count, 0) as Count from HR left join ( select State, count(*) as Count from PER where PersIdFlag = '0' group by State ) as foo using (State) |
| HrPersIdFlagRealRatio | HR | HR Ratio of PersIdFlag Real (0) | select State, sd_div_safe( HrPersIdFlagRealCount.Count, HrPerCount.Count, 3) as Ratio from HrPersIdFlagRealCount join HrPerCount using (State) |
| HrPersIdFlagDummyCount | HR | HR Count for PersIdFlag Dummy (1) | 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) |
| HrPersIdFlagDummyRatio | HR | HR Ratio of PersIdFlag Dummy (1) | select State, sd_div_safe( HrPersIdFlagDummyCount.Count, HrPerCount.Count, 3) as Ratio from HrPersIdFlagDummyCount join HrPerCount using (State) |
| HrLegalStInvolCount | HR | HR Count for LegalSt Invol (1) | 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) |
| HrLegalStInvolRatio | HR | HR Ratio of LegalSt Invol (1) | select State, sd_div_safe( HrLegalStInvolCount.Count, HrConCount.Count, 3) as Ratio from HrLegalStInvolCount join HrConCount using (State) |
| HrLegalStVolCount | HR | HR Count for LegalSt Vol (2) | 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) |
| HrLegalStVolRatio | HR | HR Ratio of LegalSt Vol (2) | select State, sd_div_safe( HrLegalStVolCount.Count, HrConCount.Count, 3) as Ratio from HrLegalStVolCount join HrConCount using (State) |
| RegPersIdFlagRealCount | REG | REG Count for PersIdFlag Real (0) | select State, RegId, coalesce(Count, 0) as Count from REG left join ( select State, RegId, count(*) as Count from PER where PersIdFlag = '0' group by State, RegId ) as foo using (State, RegId) |
| RegPersIdFlagRealRatio | REG | REG Ratio of PersIdFlag Real (0) | select State, RegId, sd_div_safe( RegPersIdFlagRealCount.Count, RegPerCount.Count, 3) as Ratio from RegPersIdFlagRealCount join RegPerCount using (State, RegId) |
| RegPersIdFlagDummyCount | REG | REG Count for PersIdFlag Dummy (1) | 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) |
| RegPersIdFlagDummyRatio | REG | REG Ratio of PersIdFlag Dummy (1) | select State, RegId, sd_div_safe( RegPersIdFlagDummyCount.Count, RegPerCount.Count, 3) as Ratio from RegPersIdFlagDummyCount join RegPerCount using (State, RegId) |
| RegLegalStInvolCount | REG | REG Count for LegalSt Invol (1) | 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 | REG | REG Ratio of LegalSt Invol (1) | select State, RegId, sd_div_safe( RegLegalStInvolCount.Count, RegConCount.Count, 3) as Ratio from RegLegalStInvolCount join RegConCount using (State, RegId) |
| RegLegalStVolCount | REG | REG Count for LegalSt Vol (2) | 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 | REG | REG Ratio of LegalSt Vol (2) | select State, RegId, sd_div_safe( RegLegalStVolCount.Count, RegConCount.Count, 3) as Ratio from RegLegalStVolCount join RegConCount using (State, RegId) |
| OrgPersIdFlagRealCount | ORG | ORG Count for PersIdFlag Real (0) | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from PER where PersIdFlag = '0' group by State, RegId, OrgId ) as foo using (State, RegId, OrgId) |
| OrgPersIdFlagRealRatio | ORG | ORG Ratio of PersIdFlag Real (0) | select State, RegId, OrgId, sd_div_safe( OrgPersIdFlagRealCount.Count, OrgPerCount.Count, 3) as Ratio from OrgPersIdFlagRealCount join OrgPerCount using (State, RegId, OrgId) |
| OrgPersIdFlagDummyCount | ORG | ORG Count for PersIdFlag Dummy (1) | 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) |
| OrgPersIdFlagDummyRatio | ORG | ORG Ratio of PersIdFlag Dummy (1) | select State, RegId, OrgId, sd_div_safe( OrgPersIdFlagDummyCount.Count, OrgPerCount.Count, 3) as Ratio from OrgPersIdFlagDummyCount join OrgPerCount using (State, RegId, OrgId) |
| OrgLegalStInvolCount | ORG | ORG Count for LegalSt Invol (1) | 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 | ORG | ORG Ratio of LegalSt Invol (1) | select State, RegId, OrgId, sd_div_safe( OrgLegalStInvolCount.Count, OrgConCount.Count, 3) as Ratio from OrgLegalStInvolCount join OrgConCount using (State, RegId, OrgId) |
| OrgLegalStVolCount | ORG | ORG Count for LegalSt Vol (2) | 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 | ORG | ORG Ratio of LegalSt Vol (2) | select State, RegId, OrgId, sd_div_safe( OrgLegalStVolCount.Count, OrgConCount.Count, 3) as Ratio from OrgLegalStVolCount join OrgConCount using (State, RegId, OrgId) |