Community Mental Health Care Minimum Data Set — Version 05.51
The full specification for CMHC 05.51 can be found on the documentation site.
Main Content
CMHC version 05.51: 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) |