Residential Mental Health Care Minimum Data Set — Version 05.51
The full specification for RMHC 05.51 can be found on the documentation site.
Main Content
RMHC version 05.51: Virtual Elements
| Name | Base | Title | SQL |
|---|---|---|---|
| EpiAge | EPI | Age | select State, RegId, OrgId, ClusId, SUId, PersId, RecordId, FLOOR((EpiEndDt - DoB) / 365.25) as Age from EPI join PER using (State, RegId, OrgId, ClusId, SUId, PersId) where DoB != '9999-09-09' and DoBFlag in ('1','2') |
| EpiDays | EPI | Episode Days | select State, RegId, OrgId, ClusId, SUId, PersId, RecordId, (EpiEndDt - EpiStartDt) - NLeaveDays as Days from EPI |
| EpiPrevGap | EPI | Days Since End of Previous Episode | select State, RegId, OrgId, ClusId, SUId, PersId, RecordId, EpiStartDt, lag(EpiEndDt) over PerEpis as prevEpiEndDt, EpiStartDt - lag(EpiEndDt) over PerEpis from EPI window PerEpis as ( partition by State, RegId, OrgId, ClusId, SUId, PersId order by EpiStartDt, EpiEndDt, RecordId) |
| HrResAreaProp | HR | State in-state ResArea Proportion | select State, sum( (State != substr(ResArea, 1, 1))::INT ) / count(*)::FLOAT as prop from EPI where ResArea is not null group by State |
| RegResAreaProp | REG | Region in-state ResArea Proportion | select State, RegId, sum( (State != substr(ResArea, 1, 1))::INT ) / count(*)::FLOAT as prop from EPI where ResArea is not null group by State, RegId |
| OrgResAreaProp | ORG | Organisation in-state ResArea Proportion | select State, RegId, OrgId, sum( (State != substr(ResArea, 1, 1))::INT ) / count(*)::FLOAT as prop from EPI where ResArea is not null group by State, RegId, OrgId |
| ServResAreaProp | SERV | Service Unit in-state ResArea Proportion | select State, RegId, OrgId, ClusId, SUId, sum( (State != substr(ResArea, 1, 1))::INT ) / count(*)::FLOAT as prop from EPI where ResArea is not null group by State, RegId, OrgId, ClusId, SUId |
| HrLegalStProp | HR | State Missing Legal Status Proportion | select State, sum( (LegalSt = '9')::INT ) / count(*)::FLOAT as prop from EPI where LegalSt is not null group by State |
| RegLegalStProp | REG | Region Missing Legal Status Proportion | select State, RegId, sum( (LegalSt = '9')::INT ) / count(*)::FLOAT as prop from EPI where LegalSt is not null group by State, RegId |
| OrgLegalStProp | ORG | Organisation Missing Legal Status Proportion | select State, RegId, OrgId, sum( (LegalSt = '9')::INT ) / count(*)::FLOAT as prop from EPI where LegalSt is not null group by State, RegId, OrgId |
| ServLegalStProp | SERV | Service Unit Missing Legal Status Proportion | select State, RegId, OrgId, ClusId, SUId, sum( (LegalSt = '9')::INT ) / count(*)::FLOAT as prop from EPI where LegalSt is not null group by State, RegId, OrgId, ClusId, SUId |
| HrLegalSt1Prop | HR | State Involuntary Legal Status Proportion | select State, sum( (LegalSt = '1')::INT ) / count(*)::FLOAT as prop from EPI where LegalSt is not null group by State |
| RegLegalSt1Prop | REG | Region Involuntary Legal Status Proportion | select State, RegId, sum( (LegalSt = '1')::INT ) / count(*)::FLOAT as prop from EPI where LegalSt is not null group by State, RegId |
| OrgLegalSt1Prop | ORG | Organisation Involuntary Legal Status Proportion | select State, RegId, OrgId, sum( (LegalSt = '1')::INT ) / count(*)::FLOAT as prop from EPI where LegalSt is not null group by State, RegId, OrgId |
| ServLegalSt1Prop | SERV | Service Unit Involuntary Legal Status Proportion | select State, RegId, OrgId, ClusId, SUId, sum( (LegalSt = '1')::INT ) / count(*)::FLOAT as prop from EPI where LegalSt is not null group by State, RegId, OrgId, ClusId, SUId |
| HrDoBFlagProp | HR | State Estimated DoB Proportion | select State, sum( (DoBFlag in ('2','8','9'))::INT ) / count(*)::FLOAT as prop from PER where DoBFlag is not null group by State |
| RegDoBFlagProp | REG | Region Estimated DoB Proportion | select State, RegId, sum( (DoBFlag in ('2','8','9'))::INT ) / count(*)::FLOAT as prop from PER where DoBFlag is not null group by State, RegId |
| OrgDoBFlagProp | ORG | Organisation Estimated DoB Proportion | select State, RegId, OrgId, sum( (DoBFlag in ('2','8','9'))::INT ) / count(*)::FLOAT as prop from PER where DoBFlag is not null group by State, RegId, OrgId |
| ServDoBFlagProp | SERV | Service Unit Estimated DoB Proportion | select State, RegId, OrgId, ClusId, SUId, sum( (DoBFlag in ('2','8','9'))::INT ) / count(*)::FLOAT as prop from PER where DoBFlag is not null group by State, RegId, OrgId, ClusId, SUId |
| HrCoBPropNotAus | HR | State Birth Country not Australia Proportion | select State, sum( (CoB not in ('1100','1101','1102','1199'))::INT ) / count(*)::FLOAT as prop from PER where CoB is not null group by State |
| RegCoBPropNotAus | REG | Region Birth Country not Australia Proportion | select State, RegId, sum( (CoB not in ('1100','1101','1102','1199'))::INT ) / count(*)::FLOAT 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, sum( (CoB not in ('1100','1101','1102','1199'))::INT ) / count(*)::FLOAT as prop from PER where CoB is not null group by State, RegId, OrgId |
| ServCoBPropNotAus | SERV | Service Unit Birth Country not Australia Proportion | select State, RegId, OrgId, ClusId, SUId, sum( (CoB not in ('1100','1101','1102','1199'))::INT ) / count(*)::FLOAT as prop from PER where CoB is not null group by State, RegId, OrgId, ClusId, SUId |
| HrCoBPropSupp | HR | State Birth Country is Supplementary | select State, sum( ((CoB ~ '^\d+ *' AND CoB::INT < 1000) OR CoB LIKE '%00')::INT ) / count(*)::FLOAT as prop from PER where CoB is not null group by State |
| RegCoBPropSupp | REG | Region Birth Country is Supplementary | select State, RegId, sum( ((CoB ~ '^\d+ *' AND CoB::INT < 1000) OR CoB LIKE '%00')::INT ) / count(*)::FLOAT as prop from PER where CoB is not null group by State, RegId |
| OrgCoBPropSupp | ORG | Organisation Birth Country is Supplementary | select State, RegId, OrgId, sum( ((CoB ~ '^\d+ *' AND CoB::INT < 1000) OR CoB LIKE '%00')::INT ) / count(*)::FLOAT as prop from PER where CoB is not null group by State, RegId, OrgId |
| ServCoBPropSupp | SERV | Service Unit Birth Country is Supplementary | select State, RegId, OrgId, ClusId, SUId, sum( ((CoB ~ '^\d+ *' AND CoB::INT < 1000) OR CoB LIKE '%00')::INT ) / count(*)::FLOAT as prop from PER where CoB is not null group by State, RegId, OrgId, ClusId, SUId |
| HrIndigStPropNonAus | HR | State Indigenous Born Outside Australia Proportion | select State, 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 |
| RegIndigStPropNonAus | REG | Region Indigenous Born Outside Australia Proportion | 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 |
| 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 |
| ServIndigStPropNonAus | SERV | Service Unit Indigenous Born Outside Australia Proportion | select State, RegId, OrgId, ClusId, SUId, 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, ClusId, SUId |
| HrRefProp9Mode4 | HR | State Referral Unknown Code 9 Used with End Mode 4 | select State, sum( (ReferralMHRC = '9')::INT ) / count(*)::FLOAT as prop from EPI where EpiEndMode = '4' group by State |
| RegRefProp9Mode4 | REG | Region Referral Unknown Code 9 Used with End Mode 4 | select State, RegId, sum( (ReferralMHRC = '9')::INT ) / count(*)::FLOAT as prop from EPI where EpiEndMode = '4' group by State, RegId |
| OrgRefProp9Mode4 | ORG | Organisation Referral Unknown Code 9 Used with End Mode 4 | select State, RegId, OrgId, sum( (ReferralMHRC = '9')::INT ) / count(*)::FLOAT as prop from EPI where EpiEndMode = '4' group by State, RegId, OrgId |
| ServRefProp9Mode4 | SERV | Service Unit Referral Unknown Code 9 Used with End Mode 4 | select State, RegId, OrgId, ClusId, SUId, sum( (ReferralMHRC = '9')::INT ) / count(*)::FLOAT as prop from EPI where EpiEndMode = '4' group by State, RegId, OrgId, ClusId, SUId |
| HrIndigSt1Count | HR | HR Count for IndigSt 1 | select State, coalesce(Count, 0) as Count from HR left join ( select State, count(*) as Count from PER where IndigSt = '1' group by State ) counts using (State) |
| HrIndigSt2Count | HR | HR Count for IndigSt 2 | select State, coalesce(Count, 0) as Count from HR left join ( select State, count(*) as Count from PER where IndigSt = '2' group by State ) counts using (State) |
| HrIndigSt3Count | HR | HR Count for IndigSt 3 | select State, coalesce(Count, 0) as Count from HR left join ( select State, count(*) as Count from PER where IndigSt = '3' group by State ) counts using (State) |
| HrLegalSt1Count | HR | HR Count for LegalSt 1 | select State, coalesce(Count, 0) as Count from HR left join ( select State, count(*) as Count from EPI where LegalSt = '1' group by State ) counts using (State) |
| HrLegalSt2Count | HR | HR Count for LegalSt 2 | select State, coalesce(Count, 0) as Count from HR left join ( select State, count(*) as Count from EPI where LegalSt = '2' group by State ) counts using (State) |
| HrSector1Count | HR | HR Count for Sector 1 | select State, coalesce(Count, 0) as Count from HR left join ( select State, count(*) as Count from SERV where Sector = '1' group by State ) counts using (State) |
| HrSector2Count | HR | HR Count for Sector 2 | select State, coalesce(Count, 0) as Count from HR left join ( select State, count(*) as Count from SERV where Sector = '2' group by State ) counts using (State) |
| RegIndigSt1Count | REG | REG Count for IndigSt 1 | select State, RegId, coalesce(Count, 0) as Count from REG left join ( select State, RegId, count(*) as Count from PER where IndigSt = '1' group by State, RegId ) counts using (State, RegId) |
| RegIndigSt2Count | REG | REG Count for IndigSt 2 | select State, RegId, coalesce(Count, 0) as Count from REG left join ( select State, RegId, count(*) as Count from PER where IndigSt = '2' group by State, RegId ) counts using (State, RegId) |
| RegIndigSt3Count | REG | REG Count for IndigSt 3 | select State, RegId, coalesce(Count, 0) as Count from REG left join ( select State, RegId, count(*) as Count from PER where IndigSt = '3' group by State, RegId ) counts using (State, RegId) |
| RegLegalSt1Count | REG | REG Count for LegalSt 1 | select State, RegId, coalesce(Count, 0) as Count from REG left join ( select State, RegId, count(*) as Count from EPI where LegalSt = '1' group by State, RegId ) counts using (State, RegId) |
| RegLegalSt2Count | REG | REG Count for LegalSt 2 | select State, RegId, coalesce(Count, 0) as Count from REG left join ( select State, RegId, count(*) as Count from EPI where LegalSt = '2' group by State, RegId ) counts using (State, RegId) |
| RegSector1Count | REG | REG Count for Sector 1 | select State, RegId, coalesce(Count, 0) as Count from REG left join ( select State, RegId, count(*) as Count from SERV where Sector = '1' group by State, RegId ) counts using (State, RegId) |
| RegSector2Count | REG | REG Count for Sector 2 | select State, RegId, coalesce(Count, 0) as Count from REG left join ( select State, RegId, count(*) as Count from SERV where Sector = '2' group by State, RegId ) counts using (State, RegId) |
| OrgIndigSt1Count | ORG | ORG Count for IndigSt 1 | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from PER where IndigSt = '1' group by State, RegId, OrgId ) counts using (State, RegId, OrgId) |
| OrgIndigSt2Count | ORG | ORG Count for IndigSt 2 | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from PER where IndigSt = '2' group by State, RegId, OrgId ) counts using (State, RegId, OrgId) |
| OrgIndigSt3Count | ORG | ORG Count for IndigSt 3 | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from PER where IndigSt = '3' group by State, RegId, OrgId ) counts using (State, RegId, OrgId) |
| OrgLegalSt1Count | ORG | ORG Count for LegalSt 1 | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from EPI where LegalSt = '1' group by State, RegId, OrgId ) counts using (State, RegId, OrgId) |
| OrgLegalSt2Count | ORG | ORG Count for LegalSt 2 | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from EPI where LegalSt = '2' group by State, RegId, OrgId ) counts using (State, RegId, OrgId) |
| OrgSector1Count | ORG | ORG Count for Sector 1 | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from SERV where Sector = '1' group by State, RegId, OrgId ) counts using (State, RegId, OrgId) |
| OrgSector2Count | ORG | ORG Count for Sector 2 | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from SERV where Sector = '2' group by State, RegId, OrgId ) counts using (State, RegId, OrgId) |
| ResiStayDays | EPI | Residential Stay Days | select State, RegId, OrgId, ClusId, SUId, PersId, RecordId, EpiEndDt - ResStartDt - NLeaveDays as Days from EPI |