Community Mental Health Care Minimum Data Set — Version 06.00
The full specification for CMHC 06.00 can be found on the documentation site.
Main Content
CMHC version 06.00: 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 |
| 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 |
| OrgUnknownContParticProp | ORG | Organisation Participartion Proportion | 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 |
| OrgUnknownContSessTypeProp | ORG | Organisation Session Type Proportion | 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 |
| 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 |
| HrResAreaProp | HR | State in-state SA2 Proportion | 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 |
| RegResAreaProp | REG | Region in-state SA2 Proportion | 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 |
| OrgResAreaProp | ORG | Organisation in-state SA2 Proportion | 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 |
| HrLegalStPropRegistered | HR | State Missing Legal Status Proportion | 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 |
| RegLegalStPropRegistered | REG | Region Missing Legal Status Proportion | 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 |
| OrgLegalStPropRegistered | ORG | Organisation Missing Legal Status Proportion | 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 |
| HrDoBFlagPropRegistered | HR | State Estimated DoB Proportion | 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 |
| RegDoBFlagPropRegistered | REG | Region Estimated DoB Proportion | 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 |
| OrgDoBFlagPropRegistered | ORG | Organisation Estimated DoB Proportion | 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 |
| 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 = '1' 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 = '1' 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 = '1' 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 = '2' 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 = '2' 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 = '2' 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 = '1' 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 = '1' 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 = '1' 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) |
| HrContDurChange | HR | HR Change in total contact duration | select State, (New.Total - Old.Total) as Change from HrContDurTotal as New join hist.HrContDurTotal as Old using (State) |
| HrContDurTotalGrowth | HR | HR Growth in total contact duration | 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) |
| 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) |
| RegContDurChange | REG | REG Change in total contact duration | select State, RegId, (New.Total - Old.Total) as Change from RegContDurTotal as New join hist.RegContDurTotal as Old using (State, RegId) |
| RegContDurTotalGrowth | REG | REG Growth in total contact duration | 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) |
| 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) |
| OrgContDurChange | ORG | ORG Change in total contact duration | select State, RegId, OrgId, (New.Total - Old.Total) as Change from OrgContDurTotal as New join hist.OrgContDurTotal as Old using (State, RegId, OrgId) |
| OrgContDurTotalGrowth | ORG | ORG Growth in total contact duration | 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) |
| 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) |
| HrConCountChange | HR | HR Change in count for Con | select State, (New.Count - Old.Count) as Change from HrConCount as New join hist.HrConCount as Old using (State) |
| HrConCountGrowth | HR | HR Growth in count for Con | 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) |
| 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) |
| HrPerCountChange | HR | HR Change in count for Per | select State, (New.Count - Old.Count) as Change from HrPerCount as New join hist.HrPerCount as Old using (State) |
| HrPerCountGrowth | HR | HR Growth in count for Per | 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) |
| 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) |
| RegConCountChange | REG | REG Change in count for Con | select State, RegId, (New.Count - Old.Count) as Change from RegConCount as New join hist.RegConCount as Old using (State, RegId) |
| RegConCountGrowth | REG | REG Growth in count for Con | 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) |
| 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) |
| RegPerCountChange | REG | REG Change in count for Per | select State, RegId, (New.Count - Old.Count) as Change from RegPerCount as New join hist.RegPerCount as Old using (State, RegId) |
| RegPerCountGrowth | REG | REG Growth in count for Per | 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) |
| 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) |
| OrgConCountChange | ORG | ORG Change in count for Con | select State, RegId, OrgId, (New.Count - Old.Count) as Change from OrgConCount as New join hist.OrgConCount as Old using (State, RegId, OrgId) |
| OrgConCountGrowth | ORG | ORG Growth in count for Con | 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) |
| 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) |
| OrgPerCountChange | ORG | ORG Change in count for Per | select State, RegId, OrgId, (New.Count - Old.Count) as Change from OrgPerCount as New join hist.OrgPerCount as Old using (State, RegId, OrgId) |
| OrgPerCountGrowth | ORG | ORG Growth in count for Per | 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) |
| 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 |
| OrgServYthCount | ORG | SERV Youth 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 = '5' group by State, RegId, OrgId ) as tmpinner using (State, RegId, OrgId) |
| OrgHasServYth | ORG | SERV Youth below ORG | select State, RegId, OrgId, Count from OrgServYthCount where Count > 0 |
| HrPersIdFlagRealCount | HR | HR Count for PersIdFlag Real (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) |
| HrPersIdFlagRealRatio | HR | HR Ratio of PersIdFlag Real (1) | select State, sd_div_safe( HrPersIdFlagRealCount.Count, HrPerCount.Count, 3) as Ratio from HrPersIdFlagRealCount join HrPerCount using (State) |
| HrPersIdFlagRealRatioChange | HR | HR Change in ratio of PersIdFlag Real | select State, (New.Ratio - Old.Ratio) as Change from HrPersIdFlagRealRatio as New join hist.HrPersIdFlagRealRatio as Old using (State) |
| HrPersIdFlagRealRatioGrowth | HR | HR Growth in ratio of PersIdFlag Real | 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) |
| HrPersIdFlagDummyCount | HR | HR Count for PersIdFlag Dummy (2) | 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) |
| HrPersIdFlagDummyRatio | HR | HR Ratio of PersIdFlag Dummy (2) | select State, sd_div_safe( HrPersIdFlagDummyCount.Count, HrPerCount.Count, 3) as Ratio from HrPersIdFlagDummyCount join HrPerCount using (State) |
| HrPersIdFlagDummyRatioChange | HR | HR Change in ratio of PersIdFlag Dummy | select State, (New.Ratio - Old.Ratio) as Change from HrPersIdFlagDummyRatio as New join hist.HrPersIdFlagDummyRatio as Old using (State) |
| HrPersIdFlagDummyRatioGrowth | HR | HR Growth in ratio of PersIdFlag Dummy | 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) |
| 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) |
| HrLegalStInvolRatioChange | HR | HR Change in ratio of LegalSt Invol | select State, (New.Ratio - Old.Ratio) as Change from HrLegalStInvolRatio as New join hist.HrLegalStInvolRatio as Old using (State) |
| HrLegalStInvolRatioGrowth | HR | HR Growth in ratio of LegalSt Invol | 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) |
| 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) |
| HrLegalStVolRatioChange | HR | HR Change in ratio of LegalSt Vol | select State, (New.Ratio - Old.Ratio) as Change from HrLegalStVolRatio as New join hist.HrLegalStVolRatio as Old using (State) |
| HrLegalStVolRatioGrowth | HR | HR Growth in ratio of LegalSt Vol | 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) |
| RegPersIdFlagRealCount | REG | REG Count for PersIdFlag Real (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) |
| RegPersIdFlagRealRatio | REG | REG Ratio of PersIdFlag Real (1) | select State, RegId, sd_div_safe( RegPersIdFlagRealCount.Count, RegPerCount.Count, 3) as Ratio from RegPersIdFlagRealCount join RegPerCount using (State, RegId) |
| RegPersIdFlagRealRatioChange | REG | REG Change in ratio of PersIdFlag Real | select State, RegId, (New.Ratio - Old.Ratio) as Change from RegPersIdFlagRealRatio as New join hist.RegPersIdFlagRealRatio as Old using (State, RegId) |
| RegPersIdFlagRealRatioGrowth | REG | REG Growth in ratio of PersIdFlag Real | 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) |
| RegPersIdFlagDummyCount | REG | REG Count for PersIdFlag Dummy (2) | 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 | REG | REG Ratio of PersIdFlag Dummy (2) | select State, RegId, sd_div_safe( RegPersIdFlagDummyCount.Count, RegPerCount.Count, 3) as Ratio from RegPersIdFlagDummyCount join RegPerCount using (State, RegId) |
| RegPersIdFlagDummyRatioChange | REG | REG Change in ratio of PersIdFlag Dummy | select State, RegId, (New.Ratio - Old.Ratio) as Change from RegPersIdFlagDummyRatio as New join hist.RegPersIdFlagDummyRatio as Old using (State, RegId) |
| RegPersIdFlagDummyRatioGrowth | REG | REG Growth in ratio of PersIdFlag Dummy | 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) |
| 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) |
| RegLegalStInvolRatioChange | REG | REG Change in ratio of LegalSt Invol | select State, RegId, (New.Ratio - Old.Ratio) as Change from RegLegalStInvolRatio as New join hist.RegLegalStInvolRatio as Old using (State, RegId) |
| RegLegalStInvolRatioGrowth | REG | REG Growth in ratio of LegalSt Invol | 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) |
| 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) |
| RegLegalStVolRatioChange | REG | REG Change in ratio of LegalSt Vol | select State, RegId, (New.Ratio - Old.Ratio) as Change from RegLegalStVolRatio as New join hist.RegLegalStVolRatio as Old using (State, RegId) |
| RegLegalStVolRatioGrowth | REG | REG Growth in ratio of LegalSt Vol | 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) |
| OrgPersIdFlagRealCount | ORG | ORG Count for PersIdFlag Real (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) |
| OrgPersIdFlagRealRatio | ORG | ORG Ratio of PersIdFlag Real (1) | select State, RegId, OrgId, sd_div_safe( OrgPersIdFlagRealCount.Count, OrgPerCount.Count, 3) as Ratio from OrgPersIdFlagRealCount join OrgPerCount using (State, RegId, OrgId) |
| OrgPersIdFlagRealRatioChange | ORG | ORG Change in ratio of PersIdFlag Real | select State, RegId, OrgId, (New.Ratio - Old.Ratio) as Change from OrgPersIdFlagRealRatio as New join hist.OrgPersIdFlagRealRatio as Old using (State, RegId, OrgId) |
| OrgPersIdFlagRealRatioGrowth | ORG | ORG Growth in ratio of PersIdFlag Real | 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) |
| OrgPersIdFlagDummyCount | ORG | ORG Count for PersIdFlag Dummy (2) | 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 | ORG | ORG Ratio of PersIdFlag Dummy (2) | select State, RegId, OrgId, sd_div_safe( OrgPersIdFlagDummyCount.Count, OrgPerCount.Count, 3) as Ratio from OrgPersIdFlagDummyCount join OrgPerCount using (State, RegId, OrgId) |
| OrgPersIdFlagDummyRatioChange | ORG | ORG Change in ratio of PersIdFlag Dummy | select State, RegId, OrgId, (New.Ratio - Old.Ratio) as Change from OrgPersIdFlagDummyRatio as New join hist.OrgPersIdFlagDummyRatio as Old using (State, RegId, OrgId) |
| OrgPersIdFlagDummyRatioGrowth | ORG | ORG Growth in ratio of PersIdFlag Dummy | 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) |
| 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) |
| OrgLegalStInvolRatioChange | ORG | ORG Change in ratio of LegalSt Invol | select State, RegId, OrgId, (New.Ratio - Old.Ratio) as Change from OrgLegalStInvolRatio as New join hist.OrgLegalStInvolRatio as Old using (State, RegId, OrgId) |
| OrgLegalStInvolRatioGrowth | ORG | ORG Growth in ratio of LegalSt Invol | 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) |
| 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) |
| OrgLegalStVolRatioChange | ORG | ORG Change in ratio of LegalSt Vol | select State, RegId, OrgId, (New.Ratio - Old.Ratio) as Change from OrgLegalStVolRatio as New join hist.OrgLegalStVolRatio as Old using (State, RegId, OrgId) |
| OrgLegalStVolRatioGrowth | ORG | ORG Growth in ratio of LegalSt Vol | 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) |