Mental Health Establishments National Minimum Data Set — Version 02.40
The full specification for MHE 02.40 can be found on the documentation site.
Main Content
MHE version 02.40: Virtual Elements
| Name | Base | Title | SQL |
|---|---|---|---|
| StRevTotal | ST | Total Revenue at State/Territory Level | select State, RevDVA + RevRecov + RevStateHealth + RevCwlthOther + RevPatients + RevOther + RevStateOther as Total from ST |
| StExpNerTotal | ST | Total Residual Expenditure at State/Territory Level | select State, ExpNerAcademic + ExpNerTraining + ExpNerInsur + ExpNerMHAct + ExpNerPromo + ExpNerResearch + ExpNerTransp + ExpNerProgAdmin + ExpNerPropLease + ExpNerServDev + ExpNerSuper + ExpNerWorkComp + ExpNerOther as Total from ST |
| OrgRevTotal | ORG | Total Revenue at Organisation Level | select State, RegId, OrgId, RevDVA + RevRecov + RevStateHealth + RevCwlthOther + RevPatients + RevOther + RevStateOther as Total from ORG |
| OrgExpNerTotal | ORG | Total Residual Expenditure at Organisation Level | select State, RegId, OrgId, ExpNerAcademic + ExpNerTraining + ExpNerInsur + ExpNerMHAct + ExpNerPromo + ExpNerResearch + ExpNerTransp + ExpNerProgAdmin + ExpNerPropLease + ExpNerServDev + ExpNerSuper + ExpNerSuppServ + ExpNerWorkComp + ExpNerOther as Total from ORG |
| OrgExpSalTotal | ORG | Total Salary and Wages Expenditure at Organisation Level | select State, RegId, OrgId, ExpSalCnsltPsych + ExpSalPsyReg + ExpSalMedOther + ExpSalNursesReg + ExpSalNursesEnrl + ExpSalOT + ExpSalSocialWk + ExpSalPsychol + ExpSalDHPOther + ExpSalAdmin + ExpSalDomest + ExpSalCarerWrkr + ExpSalConsrWrkr + ExpSalPCare as Total from ORG |
| OrgExpNonSalTotal | ORG | Total Non-salary Expenditure at Organisation Level | select State, RegId, OrgId, ExpNonSalAdmin + ExpNonSalDomest + ExpNonSalDrug + ExpNonSalFood + ExpNonSalInterest + ExpNonSalMedSuppl + ExpNonSalTransp + ExpNonSalVMO + ExpNonSalRepairs + ExpNonSalSuper + ExpNonSalOther as Total from ORG |
| OrgFteTotal | ORG | Total FTE at Organisation Level | select State, RegId, OrgId, FteCnsltPsych + FtePsyReg + FteMedOther + FteNursesReg + FteNursesEnrl + FteOT + FteSocialWk + FtePsychol + FteDHPOther + FteAdmin + FteDomest + FteCarerWrkr + FteConsrWrkr + FtePCare as Total from ORG |
| FteorgFteTotal | FTEORG | Total FTE at Organisation Full-time Equivalent Staff by Setting/TargetPop Level | select State, RegId, OrgId, Setting, TargetPop, FteMed + FteNurses + FteDHP + FteAdmin + FteDomest + FteCCWrkr + FtePCare as Total from FTEORG |
| FteorgDCareTotal | FTEORG | Total Direct Care FTE at Organisation Full-time Equivalent Staff by Setting/TargetPop Level | select State, RegId, OrgId, Setting, TargetPop, FteMed + FteNurses + FteDHP + FtePCare as Total from FTEORG |
| RegRevTotal | REG | Total Revenue at Region Level | select State, RegId, RevDVA + RevRecov + RevStateHealth + RevCwlthOther + RevPatients + RevOther + RevStateOther as Total from REG |
| RegExpNerTotal | REG | Total Residual Expenditure at Region Level | select State, RegId, ExpNerAcademic + ExpNerTraining + ExpNerInsur + ExpNerMHAct + ExpNerPromo + ExpNerResearch + ExpNerTransp + ExpNerProgAdmin + ExpNerPropLease + ExpNerServDev + ExpNerSuper + ExpNerSuppServ + ExpNerWorkComp + ExpNerOther as Total from REG |
| AdmiExpTotal | ADMI | Total Expenditure at Admitted Patient Service Unit Level | select State, RegId, OrgId, HospId, AdmiId, ExpNonSalTot + ExpSalTot as Total from ADMI |
| ResiExpTotal | RESI | Total Expenditure at Residential Service Unit Level | select State, RegId, OrgId, ClusId, ResiId, ExpNonSalTot + ExpSalTot as Total from RESI |
| AmbuExpTotal | AMBU | Total Expenditure at Ambulatory Service Unit Level | select State, RegId, OrgId, ClusId, AmbuId, ExpNonSalTot + ExpSalTot as Total from AMBU |
| StNgoSubTotal | ST | Total NGO Expenditure at State/Territory Level from STNGOE | select state, coalesce(sum(mhngoegrants),0) as Total from STNGOE group by state |
| StNgoTotal | ST | Total NGO Expenditure at State/Territory Level | select state, sum(ST.NgoOtherDepts) + coalesce(sum(StNgoSubTotal.Total),0) as Total from ST left join StNgoSubTotal using (State) group by state |
| RegNgoTotal | REG | Total NGO Expenditure at Reg Level | select reg.state, reg.regid, coalesce(sum(mhngoegrants),0) as Total from REG left join REGNGOE using (State, RegId) group by state, regid |
| OrgExpSNSTotal | ORG | Total Salary and Non-Salary Expenditure at Organisation Level | select State, RegId, OrgId, Sal.Total + NonSal.Total as Total from OrgExpSalTotal as Sal join OrgExpNonSalTotal as NonSal using (State, RegId, OrgId) |
| OrgFteorgCount | ORG | FTEORG 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 FTEORG group by State, RegId, OrgId ) as tmpinner using (State, RegId, OrgId) |
| OrgClusCount | ORG | CLUS 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 CLUS group by State, RegId, OrgId ) as tmpinner using (State, RegId, OrgId) |
| OrgHospCount | ORG | HOSP 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 HOSP group by State, RegId, OrgId ) as tmpinner using (State, RegId, OrgId) |
| ClusAmbuCount | CLUS | AMBU Count at CLUS Level | select State, RegId, OrgId, ClusId, coalesce(Count, 0) as Count from CLUS left join ( select State, RegId, OrgId, ClusId, count(*) as Count from AMBU group by State, RegId, OrgId, ClusId ) as tmpinner using (State, RegId, OrgId, ClusId) |
| ClusResiCount | CLUS | RESI Count at CLUS Level | select State, RegId, OrgId, ClusId, coalesce(Count, 0) as Count from CLUS left join ( select State, RegId, OrgId, ClusId, count(*) as Count from RESI group by State, RegId, OrgId, ClusId ) as tmpinner using (State, RegId, OrgId, ClusId) |
| OrgAdmiCount | ORG | ADMI 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 ADMI group by State, RegId, OrgId ) as tmpinner using (State, RegId, OrgId) |
| OrgResiCount | ORG | RESI 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 RESI group by State, RegId, OrgId ) as tmpinner using (State, RegId, OrgId) |
| OrgAmbuCount | ORG | AMBU 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 AMBU group by State, RegId, OrgId ) as tmpinner using (State, RegId, OrgId) |
| OrgFteSet1Count | ORG | FTEORG Count for Setting 1 | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from FTEORG where Setting = '1' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgFteSet2Count | ORG | FTEORG Count for Setting 2 | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from FTEORG where Setting = '2' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgFteSet3Count | ORG | FTEORG Count for Setting 3 | select State, RegId, OrgId, coalesce(Count, 0) as Count from ORG left join ( select State, RegId, OrgId, count(*) as Count from FTEORG where Setting = '3' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| StExpTotal | ST | State/Territory Level Total Expenditure | select state, StNgoTotal.Total + StExpNerTotal.Total as Total from StNgoTotal join StExpNerTotal using (state) |
| RegExpTotal | REG | Region Level Total Expenditure | select state, regid, RegNgoTotal.Total + RegExpNerTotal.Total as Total from RegNgoTotal join RegExpNerTotal using (state, regid) |
| OrgFteAdminSum | ORG | Total Administrative and Clerical FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteAdmin) as Total from ORG group by State, RegId, OrgId |
| StOrgFteAdminSum | ST | State Total Administrative and Clerical FTE reported at Organisation Level | select State, sum(ORG.FteAdmin) as Total from ORG group by State |
| OrgSalAdminSum | ORG | Total Administrative and Clerical Salary reported at Organisation Level | select State, RegId, OrgId, sum(ORG.ExpSalAdmin) as Total from ORG group by State, RegId, OrgId |
| OrgAdminAvgSal | ORG | Average Administrative and Clerical Salary reported at Organisation Level | select State, RegId, OrgId, sd_div_safe(Sal.Total, Fte.Total, 3) as AvgSal from OrgSalAdminSum as Sal join OrgFteAdminSum as Fte using (State, RegId, OrgId) |
| OrgFteCarerWrkrSum | ORG | Total Carer Workers FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteCarerWrkr) as Total from ORG group by State, RegId, OrgId |
| StOrgFteCarerWrkrSum | ST | State Total Carer Workers FTE reported at Organisation Level | select State, sum(ORG.FteCarerWrkr) as Total from ORG group by State |
| OrgSalCarerWrkrSum | ORG | Total Carer Workers Salary reported at Organisation Level | select State, RegId, OrgId, sum(ORG.ExpSalCarerWrkr) as Total from ORG group by State, RegId, OrgId |
| OrgCarerWrkrAvgSal | ORG | Average Carer Workers Salary reported at Organisation Level | select State, RegId, OrgId, sd_div_safe(Sal.Total, Fte.Total, 3) as AvgSal from OrgSalCarerWrkrSum as Sal join OrgFteCarerWrkrSum as Fte using (State, RegId, OrgId) |
| OrgFteCCWrkrSum | ORG | Total Carer and Consumer Workers FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteCarerWrkr + ORG.FteConsrWrkr) as Total from ORG group by State, RegId, OrgId |
| StOrgFteCCWrkrSum | ST | State Total Carer and Consumer Workers FTE reported at Organisation Level | select State, sum(ORG.FteCarerWrkr + ORG.FteConsrWrkr) as Total from ORG group by State |
| OrgSalCCWrkrSum | ORG | Total Carer and Consumer Workers Salary reported at Organisation Level | select State, RegId, OrgId, sum(ORG.ExpSalCarerWrkr + ORG.ExpSalConsrWrkr) as Total from ORG group by State, RegId, OrgId |
| OrgCCWrkrAvgSal | ORG | Average Carer and Consumer Workers Salary reported at Organisation Level | select State, RegId, OrgId, sd_div_safe(Sal.Total, Fte.Total, 3) as AvgSal from OrgSalCCWrkrSum as Sal join OrgFteCCWrkrSum as Fte using (State, RegId, OrgId) |
| OrgFteConsrWrkrSum | ORG | Total Consumer Workers FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteConsrWrkr) as Total from ORG group by State, RegId, OrgId |
| StOrgFteConsrWrkrSum | ST | State Total Consumer Workers FTE reported at Organisation Level | select State, sum(ORG.FteConsrWrkr) as Total from ORG group by State |
| OrgSalConsrWrkrSum | ORG | Total Consumer Workers Salary reported at Organisation Level | select State, RegId, OrgId, sum(ORG.ExpSalConsrWrkr) as Total from ORG group by State, RegId, OrgId |
| OrgConsrWrkrAvgSal | ORG | Average Consumer Workers Salary reported at Organisation Level | select State, RegId, OrgId, sd_div_safe(Sal.Total, Fte.Total, 3) as AvgSal from OrgSalConsrWrkrSum as Sal join OrgFteConsrWrkrSum as Fte using (State, RegId, OrgId) |
| StOrgFteOTSum | ST | State Total Occupational Therapists FTE reported at Organisation Level | select State, sum(FteOT) as Total from ORG group by State |
| StOrgFteSocialWkSum | ST | State Total Social Workers FTE reported at Organisation Level | select State, sum(FteSocialWk) as Total from ORG group by State |
| StOrgFtePsycholSum | ST | State Total Psychologists FTE reported at Organisation Level | select State, sum(FtePsychol) as Total from ORG group by State |
| StOrgFteDHPOtherSum | ST | State Total Other Diagnostic and Health Professionals FTE reported at Organisation Level | select State, sum(FteDHPOther) as Total from ORG group by State |
| OrgFteDHPSum | ORG | Total Diagnostic and Health Professionals FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteOT + ORG.FteSocialWk + ORG.FtePsychol + ORG.FteDHPOther) as Total from ORG group by State, RegId, OrgId |
| StOrgFteDHPSum | ST | State Total Diagnostic and Health Professionals FTE reported at Organisation Level | select State, sum(ORG.FteOT + ORG.FteSocialWk + ORG.FtePsychol + ORG.FteDHPOther) as Total from ORG group by State |
| OrgSalDHPSum | ORG | Total Diagnostic and Health Professionals Salary reported at Organisation Level | select State, RegId, OrgId, sum(ORG.ExpSalOT + ORG.ExpSalSocialWk + ORG.ExpSalPsychol + ORG.ExpSalDHPOther) as Total from ORG group by State, RegId, OrgId |
| OrgDHPAvgSal | ORG | Average Diagnostic and Health Professionals Salary reported at Organisation Level | select State, RegId, OrgId, sd_div_safe(Sal.Total, Fte.Total, 3) as AvgSal from OrgSalDHPSum as Sal join OrgFteDHPSum as Fte using (State, RegId, OrgId) |
| OrgFteDomestSum | ORG | Total Domestic FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteDomest) as Total from ORG group by State, RegId, OrgId |
| StOrgFteDomestSum | ST | State Total Domestic FTE reported at Organisation Level | select State, sum(ORG.FteDomest) as Total from ORG group by State |
| OrgSalDomestSum | ORG | Total Domestic Salary reported at Organisation Level | select State, RegId, OrgId, sum(ORG.ExpSalDomest) as Total from ORG group by State, RegId, OrgId |
| OrgDomestAvgSal | ORG | Average Domestic Salary reported at Organisation Level | select State, RegId, OrgId, sd_div_safe(Sal.Total, Fte.Total, 3) as AvgSal from OrgSalDomestSum as Sal join OrgFteDomestSum as Fte using (State, RegId, OrgId) |
| StOrgFteCnsltPsychSum | ST | State Total Consultant Psychiatrists and Psychiatrists FTE reported at Organisation Level | select State, sum(FteCnsltPsych) as Total from ORG group by State |
| StOrgFtePsyRegSum | ST | State Total Psychiatry Registrars and Trainees FTE reported at Organisation Level | select State, sum(FtePsyReg) as Total from ORG group by State |
| StOrgFteMedOtherSum | ST | State Total Other Medical Officers FTE reported at Organisation Level | select State, sum(FteMedOther) as Total from ORG group by State |
| OrgFteMedSum | ORG | Total Medical FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteCnsltPsych + ORG.FtePsyReg + ORG.FteMedOther) as Total from ORG group by State, RegId, OrgId |
| StOrgFteMedSum | ST | State Total Medical FTE reported at Organisation Level | select State, sum(ORG.FteCnsltPsych + ORG.FtePsyReg + ORG.FteMedOther) as Total from ORG group by State |
| OrgSalMedSum | ORG | Total Medical Salary reported at Organisation Level | select State, RegId, OrgId, sum(ORG.ExpSalCnsltPsych + ORG.ExpSalPsyReg + ORG.ExpSalMedOther) as Total from ORG group by State, RegId, OrgId |
| OrgMedAvgSal | ORG | Average Medical Salary reported at Organisation Level | select State, RegId, OrgId, sd_div_safe(Sal.Total, Fte.Total, 3) as AvgSal from OrgSalMedSum as Sal join OrgFteMedSum as Fte using (State, RegId, OrgId) |
| StOrgFteNursesRegSum | ST | State Total Registered Nurses FTE reported at Organisation Level | select State, sum(FteNursesReg) as Total from ORG group by State |
| StOrgFteNursesEnrlSum | ST | State Total Enrolled Nurses FTE reported at Organisation Level | select State, sum(FteNursesEnrl) as Total from ORG group by State |
| OrgFteNursesSum | ORG | Total Nursing FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteNursesReg + ORG.FteNursesEnrl) as Total from ORG group by State, RegId, OrgId |
| StOrgFteNursesSum | ST | State Total Nursing FTE reported at Organisation Level | select State, sum(ORG.FteNursesReg + ORG.FteNursesEnrl) as Total from ORG group by State |
| OrgSalNursesSum | ORG | Total Nursing Salary reported at Organisation Level | select State, RegId, OrgId, sum(ORG.ExpSalNursesReg + ORG.ExpSalNursesEnrl) as Total from ORG group by State, RegId, OrgId |
| OrgNursesAvgSal | ORG | Average Nursing Salary reported at Organisation Level | select State, RegId, OrgId, sd_div_safe(Sal.Total, Fte.Total, 3) as AvgSal from OrgSalNursesSum as Sal join OrgFteNursesSum as Fte using (State, RegId, OrgId) |
| OrgFtePCareSum | ORG | Total Other Personal Care FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FtePCare) as Total from ORG group by State, RegId, OrgId |
| StOrgFtePCareSum | ST | State Total Other Personal Care FTE reported at Organisation Level | select State, sum(ORG.FtePCare) as Total from ORG group by State |
| OrgSalPCareSum | ORG | Total Other Personal Care Salary reported at Organisation Level | select State, RegId, OrgId, sum(ORG.ExpSalPCare) as Total from ORG group by State, RegId, OrgId |
| OrgPCareAvgSal | ORG | Average Other Personal Care Salary reported at Organisation Level | select State, RegId, OrgId, sd_div_safe(Sal.Total, Fte.Total, 3) as AvgSal from OrgSalPCareSum as Sal join OrgFtePCareSum as Fte using (State, RegId, OrgId) |
| FteAdminSum | ORG | Total Administrative and Clerical FTE reported at Setting Level | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(FteAdmin, 0)) as Total from FTEORG group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| FteCCWrkrSum | ORG | Total Carer and Consumer Workers FTE reported at Setting Level | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(FteCCWrkr, 0)) as Total from FTEORG group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| FteDHPSum | ORG | Total Diagnostic and Health Professionals FTE reported at Setting Level | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(FteDHP, 0)) as Total from FTEORG group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| FteDomestSum | ORG | Total Domestic FTE reported at Setting Level | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(FteDomest, 0)) as Total from FTEORG group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| FteMedSum | ORG | Total Medical FTE reported at Setting Level | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(FteMed, 0)) as Total from FTEORG group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| FteNursesSum | ORG | Total Nursing FTE reported at Setting Level | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(FteNurses, 0)) as Total from FTEORG group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| FtePCareSum | ORG | Total Other Personal Care FTE reported at Setting Level | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(FtePCare, 0)) as Total from FTEORG group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgFteAllSum | ORG | Total All Staff FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteCnsltPsych + ORG.FtePsyReg + ORG.FteMedOther + ORG.FteNursesReg + ORG.FteNursesEnrl + ORG.FteOT + ORG.FteSocialWk + ORG.FtePsychol + ORG.FteDHPOther + ORG.FtePCare + ORG.FteAdmin + ORG.FteDomest + ORG.FteCarerWrkr + ORG.FteConsrWrkr) as Total from ORG group by State, RegId, OrgId |
| StOrgFteAllSum | ST | State Total All Staff FTE reported at Organisation Level | select State, sum(ORG.FteCnsltPsych + ORG.FtePsyReg + ORG.FteMedOther + ORG.FteNursesReg + ORG.FteNursesEnrl + ORG.FteOT + ORG.FteSocialWk + ORG.FtePsychol + ORG.FteDHPOther + ORG.FtePCare + ORG.FteAdmin + ORG.FteDomest + ORG.FteCarerWrkr + ORG.FteConsrWrkr) as Total from ORG group by State |
| OrgFteDCareSum | ORG | Total Direct Care FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteCnsltPsych + ORG.FtePsyReg + ORG.FteMedOther + ORG.FteNursesReg + ORG.FteNursesEnrl + ORG.FteOT + ORG.FteSocialWk + ORG.FtePsychol + ORG.FteDHPOther + ORG.FtePCare) as Total from ORG group by State, RegId, OrgId |
| StOrgFteDCareSum | ST | State Total Direct Care FTE reported at Organisation Level | select State, sum(ORG.FteCnsltPsych + ORG.FtePsyReg + ORG.FteMedOther + ORG.FteNursesReg + ORG.FteNursesEnrl + ORG.FteOT + ORG.FteSocialWk + ORG.FtePsychol + ORG.FteDHPOther + ORG.FtePCare) as Total from ORG group by State |
| OrgFteNonCareSum | ORG | Total Non-Direct Care FTE reported at Organisation Level | select State, RegId, OrgId, sum(ORG.FteAdmin + ORG.FteDomest + ORG.FteCarerWrkr + ORG.FteConsrWrkr) as Total from ORG group by State, RegId, OrgId |
| StOrgFteNonCareSum | ST | State Total Non-Direct Care FTE reported at Organisation Level | select State, sum(ORG.FteAdmin + ORG.FteDomest + ORG.FteCarerWrkr + ORG.FteConsrWrkr) as Total from ORG group by State |
| OrgAdmiExpSalTotal | ORG | Admitted Patient Service Unit Total Salary and Wages Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ExpSalTot, 0)) as Total from ADMI group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAdmiExpNonSalTotal | ORG | Admitted Patient Service Unit Total Non-salary Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ExpNonSalTot, 0)) as Total from ADMI group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAdmiDeprecTotal | ORG | Admitted Patient Service Unit Total Depreciation | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Deprec, 0)) as Total from ADMI group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAdmiExpTotal | ORG | Admitted Patient Service Unit Total Expenditure | select State, RegId, OrgId, Sal.Total + NonSal.Total as Total from OrgAdmiExpSalTotal as Sal join OrgAdmiExpNonSalTotal as NonSal using (State, RegId, OrgId) |
| StAdmiExpTotal | ST | Admitted Patient Service Unit Total Expenditure | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from OrgAdmiExpTotal group by State ) tmpinner using (State) |
| OrgResiExpSalTotal | ORG | Residential Service Unit Total Salary and Wages Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ExpSalTot, 0)) as Total from RESI group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgResiExpNonSalTotal | ORG | Residential Service Unit Total Non-salary Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ExpNonSalTot, 0)) as Total from RESI group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgResiDeprecTotal | ORG | Residential Service Unit Total Depreciation | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Deprec, 0)) as Total from RESI group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgResiExpTotal | ORG | Residential Service Unit Total Expenditure | select State, RegId, OrgId, Sal.Total + NonSal.Total as Total from OrgResiExpSalTotal as Sal join OrgResiExpNonSalTotal as NonSal using (State, RegId, OrgId) |
| StResiExpTotal | ST | Residential Service Unit Total Expenditure | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from OrgResiExpTotal group by State ) tmpinner using (State) |
| OrgAmbuExpSalTotal | ORG | Ambulatory Service Unit Total Salary and Wages Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ExpSalTot, 0)) as Total from AMBU group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAmbuExpNonSalTotal | ORG | Ambulatory Service Unit Total Non-salary Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ExpNonSalTot, 0)) as Total from AMBU group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAmbuDeprecTotal | ORG | Ambulatory Service Unit Total Depreciation | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Deprec, 0)) as Total from AMBU group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAmbuExpTotal | ORG | Ambulatory Service Unit Total Expenditure | select State, RegId, OrgId, Sal.Total + NonSal.Total as Total from OrgAmbuExpSalTotal as Sal join OrgAmbuExpNonSalTotal as NonSal using (State, RegId, OrgId) |
| StAmbuExpTotal | ST | Ambulatory Service Unit Total Expenditure | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from OrgAmbuExpTotal group by State ) tmpinner using (State) |
| OrgAmbuNContSum | ORG | Ambulatory Service Unit Total Number of Service Contacts | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(NCont, 0)) as Total from AMBU group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| RegAmbuNContSum | REG | Ambulatory Service Unit Total Number of Service Contacts | select State, RegId, coalesce(Total, 0) as Total from REG left join ( select State, RegId, sum(coalesce(NCont, 0)) as Total from AMBU group by State, RegId ) tmpinner using (State, RegId) |
| StAmbuNContSum | ST | Ambulatory Service Unit Total Number of Service Contacts | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(NCont, 0)) as Total from AMBU group by State ) tmpinner using (State) |
| OrgUnitExpSal | ORG | Total Salary and Wages Expenditure at Service Unit Level | select Admi.State as State, Admi.RegId as RegId, Admi.OrgId as OrgId, Admi.Total + Resi.Total + Ambu.Total as Total from OrgAdmiExpSalTotal as Admi join OrgResiExpSalTotal as Resi using (State, RegId, OrgId) join OrgAmbuExpSalTotal as Ambu using (State, RegId, OrgId) |
| OrgUnitExpNonSal | ORG | Total Non-salary Expenditure at Service Unit Level | select Admi.State as State, Admi.RegId as RegId, Admi.OrgId as OrgId, Admi.Total + Resi.Total + Ambu.Total as Total from OrgAdmiExpNonSalTotal as Admi join OrgResiExpNonSalTotal as Resi using (State, RegId, OrgId) join OrgAmbuExpNonSalTotal as Ambu using (State, RegId, OrgId) |
| OrgUnitExp | ORG | Total Expenditure at Service Unit Level | select Admi.State as State, Admi.RegId as RegId, Admi.OrgId as OrgId, Admi.Total + Resi.Total + Ambu.Total as Total from OrgAdmiExpTotal as Admi join OrgResiExpTotal as Resi using (State, RegId, OrgId) join OrgAmbuExpTotal as Ambu using (State, RegId, OrgId) |
| OrgUnitDeprec | ORG | Total Depreciation at Service Unit Level | select Admi.State as State, Admi.RegId as RegId, Admi.OrgId as OrgId, Admi.Total + Resi.Total + Ambu.Total as Total from OrgAdmiDeprecTotal as Admi join OrgResiDeprecTotal as Resi using (State, RegId, OrgId) join OrgAmbuDeprecTotal as Ambu using (State, RegId, OrgId) |
| RegUnitExp | REG | Total Expenditure at Service Unit Level | select State, RegId, sum(OrgUnitExp.Total) as Total from OrgUnitExp group by State, RegId |
| StUnitExp | ST | Total Expenditure at Service Unit Level | select State, sum(RegUnitExp.Total) as Total from RegUnitExp group by State |
| OrgWideExp | ORG | Organisation Wide Expenditure | select State, RegId, OrgId, Ner.Total + UnitExp.Total as Total from OrgExpNerTotal as Ner join OrgUnitExp as UnitExp using (State, RegId, OrgId) |
| StWideNgo | ST | State/Territory Wide NGO Expenditure | select State, Ngo.Total + SubNgo.Total as Total from StNgoTotal as Ngo join (select State, sum(Total) as Total from RegNgoTotal group by State) as SubNgo using (State) |
| RegWideExpNer | REG | Region Wide Residual Expenditure | select State, RegId, Ner.Total + SubNer.Total as Total from RegExpNerTotal as Ner join (select State, RegId, sum(Total) as Total from OrgExpNerTotal group by State, RegId) as SubNer using (State, RegId) |
| RegWideExp | REG | Region Wide Expenditure | select Unit.State as State, Unit.RegId as RegId, Ner.Total + Unit.Total + Ngo.Total as Total from RegWideExpNer as Ner join RegUnitExp as Unit using (State, RegId) join RegNgoTotal as Ngo using (State, RegId) |
| StWideExpNer | ST | State/Territory Wide Residual Expenditure | select State, Ner.Total + SubNer.Total as Total from StExpNerTotal as Ner join (select State, sum(Total) as Total from RegWideExpNer group by State) as SubNer using (State) |
| StWideExp | ST | State/Territory Wide Expenditure | select Unit.State as State, Ner.Total + Unit.Total + Ngo.Total as Total from StWideExpNer as Ner join StUnitExp as Unit using (State) join StWideNgo as Ngo using (State) |
| RegAppExpNer | REG | Apportionable Residual Expenditure | select State, RegId, ExpNerProgAdmin + ExpNerSuppServ + ExpNerAcademic + ExpNerSuper + ExpNerWorkComp + ExpNerInsur + ExpNerTransp + ExpNerPropLease + ExpNerOther as Total from REG |
| RegNonAppExpNer | REG | Non-apportionable Residual Expenditure | select State, RegId, ExpNerTraining + ExpNerPromo + ExpNerResearch + ExpNerMHAct + ExpNerServDev as Total from REG |
| AdmiRegAppExpNer | ADMI | Region Residual Expenditure Apportioned to Admitted Patient Service Unit | select UnitExp.State as State, UnitExp.RegId as RegId, UnitExp.OrgId as OrgId, UnitExp.HospId as HospId, UnitExp.AdmiId as AdmiId, sd_div_safe((Pie.Total * UnitExp.Total::numeric), RegUnitExp.Total, 3) as Total from AdmiExpTotal as UnitExp join RegUnitExp using (State, RegId) join RegAppExpNer as Pie using (State, RegId) |
| AdmiRegAppExpNerFmt | ADMI | Region Residual Expenditure Apportioned to Admitted Patient Service Unit (rounded) | select State, RegId, OrgId, HospId, AdmiId, round(Src.Total) as Total from AdmiRegAppExpNer as Src |
| ResiRegAppExpNer | RESI | Region Residual Expenditure Apportioned to Residential Service Unit | select UnitExp.State as State, UnitExp.RegId as RegId, UnitExp.OrgId as OrgId, UnitExp.ClusId as ClusId, UnitExp.ResiId as ResiId, sd_div_safe((Pie.Total * UnitExp.Total::numeric), RegUnitExp.Total, 3) as Total from ResiExpTotal as UnitExp join RegUnitExp using (State, RegId) join RegAppExpNer as Pie using (State, RegId) |
| ResiRegAppExpNerFmt | RESI | Region Residual Expenditure Apportioned to Residential Service Unit (rounded) | select State, RegId, OrgId, ClusId, ResiId, round(Src.Total) as Total from ResiRegAppExpNer as Src |
| AmbuRegAppExpNer | AMBU | Region Residual Expenditure Apportioned to Ambulatory Service Unit | select UnitExp.State as State, UnitExp.RegId as RegId, UnitExp.OrgId as OrgId, UnitExp.ClusId as ClusId, UnitExp.AmbuId as AmbuId, sd_div_safe((Pie.Total * UnitExp.Total::numeric), RegUnitExp.Total, 3) as Total from AmbuExpTotal as UnitExp join RegUnitExp using (State, RegId) join RegAppExpNer as Pie using (State, RegId) |
| AmbuRegAppExpNerFmt | AMBU | Region Residual Expenditure Apportioned to Ambulatory Service Unit (rounded) | select State, RegId, OrgId, ClusId, AmbuId, round(Src.Total) as Total from AmbuRegAppExpNer as Src |
| OrgAppExpNer | ORG | Apportionable Residual Expenditure | select State, RegId, OrgId, ExpNerProgAdmin + ExpNerSuppServ + ExpNerAcademic + ExpNerSuper + ExpNerWorkComp + ExpNerInsur + ExpNerTransp + ExpNerPropLease + ExpNerOther as Total from ORG |
| OrgNonAppExpNer | ORG | Non-apportionable Residual Expenditure | select State, RegId, OrgId, ExpNerTraining + ExpNerPromo + ExpNerResearch + ExpNerMHAct + ExpNerServDev as Total from ORG |
| AdmiOrgAppExpNer | ADMI | Organisation Residual Expenditure Apportioned to Admitted Patient Service Unit | select UnitExp.State as State, UnitExp.RegId as RegId, UnitExp.OrgId as OrgId, UnitExp.HospId as HospId, UnitExp.AdmiId as AdmiId, sd_div_safe((Pie.Total * UnitExp.Total::numeric), OrgUnitExp.Total, 3) as Total from AdmiExpTotal as UnitExp join OrgUnitExp using (State, RegId, OrgId) join OrgAppExpNer as Pie using (State, RegId, OrgId) |
| AdmiOrgAppExpNerFmt | ADMI | Organisation Residual Expenditure Apportioned to Admitted Patient Service Unit (rounded) | select State, RegId, OrgId, HospId, AdmiId, round(Src.Total) as Total from AdmiOrgAppExpNer as Src |
| ResiOrgAppExpNer | RESI | Organisation Residual Expenditure Apportioned to Residential Service Unit | select UnitExp.State as State, UnitExp.RegId as RegId, UnitExp.OrgId as OrgId, UnitExp.ClusId as ClusId, UnitExp.ResiId as ResiId, sd_div_safe((Pie.Total * UnitExp.Total::numeric), OrgUnitExp.Total, 3) as Total from ResiExpTotal as UnitExp join OrgUnitExp using (State, RegId, OrgId) join OrgAppExpNer as Pie using (State, RegId, OrgId) |
| ResiOrgAppExpNerFmt | RESI | Organisation Residual Expenditure Apportioned to Residential Service Unit (rounded) | select State, RegId, OrgId, ClusId, ResiId, round(Src.Total) as Total from ResiOrgAppExpNer as Src |
| AmbuOrgAppExpNer | AMBU | Organisation Residual Expenditure Apportioned to Ambulatory Service Unit | select UnitExp.State as State, UnitExp.RegId as RegId, UnitExp.OrgId as OrgId, UnitExp.ClusId as ClusId, UnitExp.AmbuId as AmbuId, sd_div_safe((Pie.Total * UnitExp.Total::numeric), OrgUnitExp.Total, 3) as Total from AmbuExpTotal as UnitExp join OrgUnitExp using (State, RegId, OrgId) join OrgAppExpNer as Pie using (State, RegId, OrgId) |
| AmbuOrgAppExpNerFmt | AMBU | Organisation Residual Expenditure Apportioned to Ambulatory Service Unit (rounded) | select State, RegId, OrgId, ClusId, AmbuId, round(Src.Total) as Total from AmbuOrgAppExpNer as Src |
| AdmiAppExpTotal | ADMI | Total Apportioned Expenditure at Admitted Patient Service Unit level | with UnitExp as ( select * from AdmiExpTotal ), RegApp as ( select * from AdmiRegAppExpNer ), OrgApp as ( select * from AdmiOrgAppExpNer ) select UnitExp.State as State, UnitExp.RegId as RegId, UnitExp.OrgId as OrgId, UnitExp.HospId as HospId, UnitExp.AdmiId as AdmiId, UnitExp.Total + RegApp.Total + OrgApp.Total as Total from UnitExp join RegApp using (State, RegId, OrgId, HospId, AdmiId) join OrgApp using (State, RegId, OrgId, HospId, AdmiId) |
| AdmiAppExpTotalFmt | ADMI | Total Apportioned Expenditure at Admitted Patient Service Unit level (rounded) | select State, RegId, OrgId, HospId, AdmiId, round(Src.Total) as Total from AdmiAppExpTotal as Src |
| StAdmiAppExpTotal | ST | Admitted Patient Service Unit Total Apportioned Expenditure | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from AdmiAppExpTotal group by State ) tmpinner using (State) |
| StAdmiAppExpTotalFmt | ST | Admitted Patient Service Unit Total Apportioned Expenditure (rounded) | select State, round(Src.Total) as Total from StAdmiAppExpTotal as Src |
| OrgAdmiAppExpTotal | ORG | Admitted Patient Service Unit Total Apportioned Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Total, 0)) as Total from AdmiAppExpTotal group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAdmiAppExpTotalFmt | ORG | Admitted Patient Service Unit Total Apportioned Expenditure (rounded) | select State, RegId, OrgId, round(Src.Total) as Total from OrgAdmiAppExpTotal as Src |
| ResiAppExpTotal | RESI | Total Apportioned Expenditure at Residential Service Unit level | with UnitExp as ( select * from ResiExpTotal ), RegApp as ( select * from ResiRegAppExpNer ), OrgApp as ( select * from ResiOrgAppExpNer ) select UnitExp.State as State, UnitExp.RegId as RegId, UnitExp.OrgId as OrgId, UnitExp.ClusId as ClusId, UnitExp.ResiId as ResiId, UnitExp.Total + RegApp.Total + OrgApp.Total as Total from UnitExp join RegApp using (State, RegId, OrgId, ClusId, ResiId) join OrgApp using (State, RegId, OrgId, ClusId, ResiId) |
| ResiAppExpTotalFmt | RESI | Total Apportioned Expenditure at Residential Service Unit level (rounded) | select State, RegId, OrgId, ClusId, ResiId, round(Src.Total) as Total from ResiAppExpTotal as Src |
| StResiAppExpTotal | ST | Residential Service Unit Total Apportioned Expenditure | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from ResiAppExpTotal group by State ) tmpinner using (State) |
| StResiAppExpTotalFmt | ST | Residential Service Unit Total Apportioned Expenditure (rounded) | select State, round(Src.Total) as Total from StResiAppExpTotal as Src |
| OrgResiAppExpTotal | ORG | Residential Service Unit Total Apportioned Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Total, 0)) as Total from ResiAppExpTotal group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgResiAppExpTotalFmt | ORG | Residential Service Unit Total Apportioned Expenditure (rounded) | select State, RegId, OrgId, round(Src.Total) as Total from OrgResiAppExpTotal as Src |
| AmbuAppExpTotal | AMBU | Total Apportioned Expenditure at Ambulatory Service Unit level | with UnitExp as ( select * from AmbuExpTotal ), RegApp as ( select * from AmbuRegAppExpNer ), OrgApp as ( select * from AmbuOrgAppExpNer ) select UnitExp.State as State, UnitExp.RegId as RegId, UnitExp.OrgId as OrgId, UnitExp.ClusId as ClusId, UnitExp.AmbuId as AmbuId, UnitExp.Total + RegApp.Total + OrgApp.Total as Total from UnitExp join RegApp using (State, RegId, OrgId, ClusId, AmbuId) join OrgApp using (State, RegId, OrgId, ClusId, AmbuId) |
| AmbuAppExpTotalFmt | AMBU | Total Apportioned Expenditure at Ambulatory Service Unit level (rounded) | select State, RegId, OrgId, ClusId, AmbuId, round(Src.Total) as Total from AmbuAppExpTotal as Src |
| StAmbuAppExpTotal | ST | Ambulatory Service Unit Total Apportioned Expenditure | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from AmbuAppExpTotal group by State ) tmpinner using (State) |
| StAmbuAppExpTotalFmt | ST | Ambulatory Service Unit Total Apportioned Expenditure (rounded) | select State, round(Src.Total) as Total from StAmbuAppExpTotal as Src |
| OrgAmbuAppExpTotal | ORG | Ambulatory Service Unit Total Apportioned Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Total, 0)) as Total from AmbuAppExpTotal group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAmbuAppExpTotalFmt | ORG | Ambulatory Service Unit Total Apportioned Expenditure (rounded) | select State, RegId, OrgId, round(Src.Total) as Total from OrgAmbuAppExpTotal as Src |
| OrgUnitAppExp | ORG | Total Apportioned Expenditure at Service Unit Level | select Admi.State as State, Admi.RegId as RegId, Admi.OrgId as OrgId, Admi.Total + Resi.Total + Ambu.Total as Total from OrgAdmiAppExpTotal as Admi join OrgResiAppExpTotal as Resi using (State, RegId, OrgId) join OrgAmbuAppExpTotal as Ambu using (State, RegId, OrgId) |
| OrgWideAppExp | ORG | Organisation Wide Apportioned Expenditure | select State, RegId, OrgId, NonAppNer.Total + UnitAppExp.Total as Total from OrgNonAppExpNer as NonAppNer join OrgUnitAppExp as UnitAppExp using (State, RegId, OrgId) |
| StCLExpTotal | ST | Co-located Hospitals Total Expenditure | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from AdmiExpTotal join HOSP using (State, RegId, OrgId, HospId) where HOSP.CoLocStatus = '1' group by State ) tmpinner using (State) |
| StCLExpTotalFmt | ST | Co-located Hospitals Total Expenditure (rounded) | select State, round(Src.Total) as Total from StCLExpTotal as Src |
| StSAExpTotal | ST | Stand Alone Hospitals Total Expenditure | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from AdmiExpTotal join HOSP using (State, RegId, OrgId, HospId) where HOSP.CoLocStatus = '2' group by State ) tmpinner using (State) |
| StSAExpTotalFmt | ST | Stand Alone Hospitals Total Expenditure (rounded) | select State, round(Src.Total) as Total from StSAExpTotal as Src |
| StCLAppExpTotal | ST | Co-located Hospitals Total Apportioned Expenditure | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from AdmiAppExpTotal join HOSP using (State, RegId, OrgId, HospId) where HOSP.CoLocStatus = '1' group by State ) tmpinner using (State) |
| StCLAppExpTotalFmt | ST | Co-located Hospitals Total Apportioned Expenditure (rounded) | select State, round(Src.Total) as Total from StCLAppExpTotal as Src |
| StSAAppExpTotal | ST | Stand Alone Hospitals Total Apportioned Expenditure | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from AdmiAppExpTotal join HOSP using (State, RegId, OrgId, HospId) where HOSP.CoLocStatus = '2' group by State ) tmpinner using (State) |
| StSAAppExpTotalFmt | ST | Stand Alone Hospitals Total Apportioned Expenditure (rounded) | select State, round(Src.Total) as Total from StSAAppExpTotal as Src |
| OrgCLExpTotal | ORG | Co-located Hospitals Total Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Total, 0)) as Total from AdmiExpTotal join HOSP using (State, RegId, OrgId, HospId) where HOSP.CoLocStatus = '1' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgCLExpTotalFmt | ORG | Co-located Hospitals Total Expenditure (rounded) | select State, RegId, OrgId, round(Src.Total) as Total from OrgCLExpTotal as Src |
| OrgSAExpTotal | ORG | Stand Alone Hospitals Total Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Total, 0)) as Total from AdmiExpTotal join HOSP using (State, RegId, OrgId, HospId) where HOSP.CoLocStatus = '2' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgSAExpTotalFmt | ORG | Stand Alone Hospitals Total Expenditure (rounded) | select State, RegId, OrgId, round(Src.Total) as Total from OrgSAExpTotal as Src |
| OrgCLAppExpTotal | ORG | Co-located Hospitals Total Apportioned Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Total, 0)) as Total from AdmiAppExpTotal join HOSP using (State, RegId, OrgId, HospId) where HOSP.CoLocStatus = '1' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgCLAppExpTotalFmt | ORG | Co-located Hospitals Total Apportioned Expenditure (rounded) | select State, RegId, OrgId, round(Src.Total) as Total from OrgCLAppExpTotal as Src |
| OrgSAAppExpTotal | ORG | Stand Alone Hospitals Total Apportioned Expenditure | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Total, 0)) as Total from AdmiAppExpTotal join HOSP using (State, RegId, OrgId, HospId) where HOSP.CoLocStatus = '2' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgSAAppExpTotalFmt | ORG | Stand Alone Hospitals Total Apportioned Expenditure (rounded) | select State, RegId, OrgId, round(Src.Total) as Total from OrgSAAppExpTotal as Src |
| RegWideNonAppExpNer | REG | Region Wide Non-Apportioned Residual Expenditure | select State, RegId, NonApp.Total + SubNonApp.Total as Total from RegNonAppExpNer as NonApp join (select State, RegId, sum(Total) as Total from OrgNonAppExpNer group by State, RegId) as SubNonApp using (State, RegId) |
| StWideNonAppExpNer | ST | State/Territory Wide Non-Apportioned Residual Expenditure | select State, NonApp.Total + SubNonApp.Total as Total from StExpNerTotal as NonApp join (select State, sum(Total) as Total from RegWideNonAppExpNer group by State) as SubNonApp using (State) |
| AmbuAvgCon | AMBU | Average Contact Cost for Ambulatory Service Unit | select State, RegId, OrgId, ClusId, AmbuId, sd_div_safe(Total, NCont, 1) as AvgCon from AMBU join AmbuExpTotal using (State, RegId, OrgId, ClusId, AmbuId) where NCont != 0 |
| AvgStay | ADMI | Average Length of Stay for Admitted Patient Service Unit | select State, RegId, OrgId, HospId, AdmiId, sd_div_safe(MHCareDays, NSeps, 1) as AvgStay from ADMI |
| AdmiOccupancy | ADMI | Average Occupancy for Admitted Patient Service Unit | select State, RegId, OrgId, HospId, AdmiId, sd_div_safe(MHCareDays, (AdmiNBeds + HitHNBeds) * 365, 3) as Occupancy from ADMI |
| AdmiPDay | ADMI | Average Patient Day Cost for Admitted Patient Service Unit | select State, RegId, OrgId, HospId, AdmiId, sd_div_safe(Total, MHCareDays, 1) as PDay from ADMI join AdmiExpTotal using (State, RegId, OrgId, HospId, AdmiId) where MHCareDays != 0 |
| OrgAdmiNBedsSum | ORG | Total Average Available Beds for Overnight-stay Patients | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| RegAdmiNBedsSum | REG | Total Average Available Beds for Overnight-stay Patients | select State, RegId, coalesce(Total, 0) as Total from REG left join ( select State, RegId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI group by State, RegId ) tmpinner using (State, RegId) |
| StAdmiNBedsSum | ST | Total Average Available Beds for Overnight-stay Patients | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI group by State ) tmpinner using (State) |
| OrgAdmiCDaysSum | ORG | Total Accrued Mental Health Care Days | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| RegAdmiCDaysSum | REG | Total Accrued Mental Health Care Days | select State, RegId, coalesce(Total, 0) as Total from REG left join ( select State, RegId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI group by State, RegId ) tmpinner using (State, RegId) |
| StAdmiCDaysSum | ST | Total Accrued Mental Health Care Days | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI group by State ) tmpinner using (State) |
| ResiOccupancy | RESI | Average Occupancy for Residential Service Unit | select State, RegId, OrgId, ClusId, ResiId, sd_div_safe(MHCareDays, ResiNBeds * 365, 3) as Occupancy from RESI |
| ResiPDay | RESI | Average Patient Day Cost for Residential Service Unit | select State, RegId, OrgId, ClusId, ResiId, sd_div_safe(Total, MHCareDays, 1) as PDay from RESI join ResiExpTotal using (State, RegId, OrgId, ClusId, ResiId) where MHCareDays != 0 |
| OrgResiNBedsSum | ORG | Total Average Available Beds for Residential Mental Health Patients | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| RegResiNBedsSum | REG | Total Average Available Beds for Residential Mental Health Patients | select State, RegId, coalesce(Total, 0) as Total from REG left join ( select State, RegId, sum(coalesce(ResiNBeds, 0)) as Total from RESI group by State, RegId ) tmpinner using (State, RegId) |
| StResiNBedsSum | ST | Total Average Available Beds for Residential Mental Health Patients | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI group by State ) tmpinner using (State) |
| OrgResiCDaysSum | ORG | Total Accrued Mental Health Care Days | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| RegResiCDaysSum | REG | Total Accrued Mental Health Care Days | select State, RegId, coalesce(Total, 0) as Total from REG left join ( select State, RegId, sum(coalesce(MHCareDays, 0)) as Total from RESI group by State, RegId ) tmpinner using (State, RegId) |
| StResiCDaysSum | ST | Total Accrued Mental Health Care Days | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI group by State ) tmpinner using (State) |
| OrgAdmiFteSum | ORG | Total FTE for Admitted Patient Service Unit at Organisation Level | select State, RegId, OrgId, coalesce(sum(Fte.Total), 0) as Total from ORG left join (select * from FteorgFteTotal where Setting = '1') as Fte using (State, RegId, OrgId) group by State, RegId, OrgId |
| OrgAdmiDCareFteSum | ORG | Total Direct Care FTE for Admitted Patient Service Units | select State, RegId, OrgId, coalesce(sum(Fte.Total), 0) as Total from ORG left join (select * from FteorgDCareTotal where Setting = '1') as Fte using (State, RegId, OrgId) group by State, RegId, OrgId |
| StAdmiDCareFteSum | ST | Total Direct Care FTE for Admitted Patient Service Units | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from FteorgDCareTotal where Setting = '1' group by State ) tmpinner using (State) |
| OrgAdmiAvgSal | ORG | Average Salary for Admitted Patient Service Unit at Organisation Level | select State, RegId, OrgId, sd_div_safe(UnitSal.Total, Fte.Total, 1) as AvgSal from OrgAdmiExpSalTotal as UnitSal join OrgAdmiFteSum as Fte using (State, RegId, OrgId) |
| OrgResiFteSum | ORG | Total FTE for Residential Service Unit at Organisation Level | select State, RegId, OrgId, coalesce(sum(Fte.Total), 0) as Total from ORG left join (select * from FteorgFteTotal where Setting = '2') as Fte using (State, RegId, OrgId) group by State, RegId, OrgId |
| OrgResiDCareFteSum | ORG | Total Direct Care FTE for Residential Service Units | select State, RegId, OrgId, coalesce(sum(Fte.Total), 0) as Total from ORG left join (select * from FteorgDCareTotal where Setting = '2') as Fte using (State, RegId, OrgId) group by State, RegId, OrgId |
| StResiDCareFteSum | ST | Total Direct Care FTE for Residential Service Units | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from FteorgDCareTotal where Setting = '2' group by State ) tmpinner using (State) |
| OrgResiAvgSal | ORG | Average Salary for Residential Service Unit at Organisation Level | select State, RegId, OrgId, sd_div_safe(UnitSal.Total, Fte.Total, 1) as AvgSal from OrgResiExpSalTotal as UnitSal join OrgResiFteSum as Fte using (State, RegId, OrgId) |
| OrgAmbuFteSum | ORG | Total FTE for Ambulatory Service Unit at Organisation Level | select State, RegId, OrgId, coalesce(sum(Fte.Total), 0) as Total from ORG left join (select * from FteorgFteTotal where Setting = '3') as Fte using (State, RegId, OrgId) group by State, RegId, OrgId |
| OrgAmbuDCareFteSum | ORG | Total Direct Care FTE for Ambulatory Service Units | select State, RegId, OrgId, coalesce(sum(Fte.Total), 0) as Total from ORG left join (select * from FteorgDCareTotal where Setting = '3') as Fte using (State, RegId, OrgId) group by State, RegId, OrgId |
| StAmbuDCareFteSum | ST | Total Direct Care FTE for Ambulatory Service Units | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from FteorgDCareTotal where Setting = '3' group by State ) tmpinner using (State) |
| OrgAmbuAvgSal | ORG | Average Salary for Ambulatory Service Unit at Organisation Level | select State, RegId, OrgId, sd_div_safe(UnitSal.Total, Fte.Total, 1) as AvgSal from OrgAmbuExpSalTotal as UnitSal join OrgAmbuFteSum as Fte using (State, RegId, OrgId) |
| OrgOohFteSum | ORG | Total FTE for Organisational overhead at Organisation Level | select State, RegId, OrgId, coalesce(sum(Fte.Total), 0) as Total from ORG left join (select * from FteorgFteTotal where Setting = '4') as Fte using (State, RegId, OrgId) group by State, RegId, OrgId |
| OrgOohDCareFteSum | ORG | Total Direct Care FTE for Organisational overheads | select State, RegId, OrgId, coalesce(sum(Fte.Total), 0) as Total from ORG left join (select * from FteorgDCareTotal where Setting = '4') as Fte using (State, RegId, OrgId) group by State, RegId, OrgId |
| StOohDCareFteSum | ST | Total Direct Care FTE for Organisational overheads | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from FteorgDCareTotal where Setting = '4' group by State ) tmpinner using (State) |
| OrgExpRealMedSum | ORG | Total Medical and VMOs Expenditure reported at Organisation Level | select State, RegId, OrgId, sum(ORG.ExpSalCnsltPsych + ORG.ExpSalPsyReg + ORG.ExpSalMedOther + ORG.ExpNonSalVMO) as Total from ORG group by State, RegId, OrgId |
| OrgRealMedAvgSal | ORG | Average Medical and VMOs Expenditure reported at Organisation Level | select State, RegId, OrgId, sd_div_safe(Sal.Total, Fte.Total, 3) as AvgSal from OrgExpRealMedSum as Sal join OrgFteMedSum as Fte using (State, RegId, OrgId) |
| OrgAmbuAvgFteCost | ORG | Average Cost per FTE for Ambulatory Service Unit at Organisation level | select State, RegId, OrgId, sd_div( Exp.Total, Fte.Total, 3 ) as AvgFteCost from OrgAmbuFteSum as Fte join OrgAmbuExpTotal as Exp using (State, RegId, OrgId) where Fte.Total != 0; |
| StStAcademicSum | ST | Total State/Territory Academic Positions Expenditure (NER) | select State, sum(coalesce(ST.ExpNerAcademic, 0)) as Total from ST group by State |
| StStTrainingSum | ST | Total State/Territory Education and Training Expenditure (NER) | select State, sum(coalesce(ST.ExpNerTraining, 0)) as Total from ST group by State |
| StStInsurSum | ST | Total State/Territory Insurance Expenditure (NER) | select State, sum(coalesce(ST.ExpNerInsur, 0)) as Total from ST group by State |
| StStMHActSum | ST | Total State/Territory Mental Health Act Regulation Including Review Tribunals Expenditure (NER) | select State, sum(coalesce(ST.ExpNerMHAct, 0)) as Total from ST group by State |
| StStPromoSum | ST | Total State/Territory Mental Health Promotion Expenditure (NER) | select State, sum(coalesce(ST.ExpNerPromo, 0)) as Total from ST group by State |
| StStResearchSum | ST | Total State/Territory Mental Health Research Expenditure (NER) | select State, sum(coalesce(ST.ExpNerResearch, 0)) as Total from ST group by State |
| StStTranspSum | ST | Total State/Territory Patient Transport Services Expenditure (NER) | select State, sum(coalesce(ST.ExpNerTransp, 0)) as Total from ST group by State |
| StStProgAdminSum | ST | Total State/Territory Program Administration Expenditure (NER) | select State, sum(coalesce(ST.ExpNerProgAdmin, 0)) as Total from ST group by State |
| StStPropLeaseSum | ST | Total State/Territory Property Leasing Costs Expenditure (NER) | select State, sum(coalesce(ST.ExpNerPropLease, 0)) as Total from ST group by State |
| StStServDevSum | ST | Total State/Territory Service Development Expenditure (NER) | select State, sum(coalesce(ST.ExpNerServDev, 0)) as Total from ST group by State |
| StStSuperSum | ST | Total State/Territory Superannuation Expenditure (NER) | select State, sum(coalesce(ST.ExpNerSuper, 0)) as Total from ST group by State |
| StStWorkCompSum | ST | Total State/Territory Workers Compensation Expenditure (NER) | select State, sum(coalesce(ST.ExpNerWorkComp, 0)) as Total from ST group by State |
| StStOtherSum | ST | Total State/Territory Other Indirect Expenditure Expenditure (NER) | select State, sum(coalesce(ST.ExpNerOther, 0)) as Total from ST group by State |
| StStNerSum | ST | Total State/Territory Expenditure (NER) | select State, sum(coalesce(Ent.Total, 0)) as Total from ST left join StExpNerTotal as Ent using (State) group by State |
| StRegAcademicSum | ST | Total Region Academic Positions Expenditure (NER) | select State, sum(coalesce(REG.ExpNerAcademic, 0)) as Total from ST left join REG using (State) group by State |
| StRegTrainingSum | ST | Total Region Education and Training Expenditure (NER) | select State, sum(coalesce(REG.ExpNerTraining, 0)) as Total from ST left join REG using (State) group by State |
| StRegInsurSum | ST | Total Region Insurance Expenditure (NER) | select State, sum(coalesce(REG.ExpNerInsur, 0)) as Total from ST left join REG using (State) group by State |
| StRegMHActSum | ST | Total Region Mental Health Act Regulation Including Review Tribunals Expenditure (NER) | select State, sum(coalesce(REG.ExpNerMHAct, 0)) as Total from ST left join REG using (State) group by State |
| StRegPromoSum | ST | Total Region Mental Health Promotion Expenditure (NER) | select State, sum(coalesce(REG.ExpNerPromo, 0)) as Total from ST left join REG using (State) group by State |
| StRegResearchSum | ST | Total Region Mental Health Research Expenditure (NER) | select State, sum(coalesce(REG.ExpNerResearch, 0)) as Total from ST left join REG using (State) group by State |
| StRegTranspSum | ST | Total Region Patient Transport Services Expenditure (NER) | select State, sum(coalesce(REG.ExpNerTransp, 0)) as Total from ST left join REG using (State) group by State |
| StRegProgAdminSum | ST | Total Region Program Administration Expenditure (NER) | select State, sum(coalesce(REG.ExpNerProgAdmin, 0)) as Total from ST left join REG using (State) group by State |
| StRegPropLeaseSum | ST | Total Region Property Leasing Costs Expenditure (NER) | select State, sum(coalesce(REG.ExpNerPropLease, 0)) as Total from ST left join REG using (State) group by State |
| StRegServDevSum | ST | Total Region Service Development Expenditure (NER) | select State, sum(coalesce(REG.ExpNerServDev, 0)) as Total from ST left join REG using (State) group by State |
| StRegSuperSum | ST | Total Region Superannuation Expenditure (NER) | select State, sum(coalesce(REG.ExpNerSuper, 0)) as Total from ST left join REG using (State) group by State |
| StRegSuppServSum | ST | Total Region Support Services Expenditure (NER) | select State, sum(coalesce(REG.ExpNerSuppServ, 0)) as Total from ST left join REG using (State) group by State |
| StRegWorkCompSum | ST | Total Region Workers Compensation Expenditure (NER) | select State, sum(coalesce(REG.ExpNerWorkComp, 0)) as Total from ST left join REG using (State) group by State |
| StRegOtherSum | ST | Total Region Other Indirect Expenditure Expenditure (NER) | select State, sum(coalesce(REG.ExpNerOther, 0)) as Total from ST left join REG using (State) group by State |
| StRegNerSum | ST | Total Region Expenditure (NER) | select State, sum(coalesce(Ent.Total, 0)) as Total from ST left join RegExpNerTotal as Ent using (State) group by State |
| StOrgAcademicSum | ST | Total Organisation Academic Positions Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerAcademic, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgTrainingSum | ST | Total Organisation Education and Training Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerTraining, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgInsurSum | ST | Total Organisation Insurance Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerInsur, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgMHActSum | ST | Total Organisation Mental Health Act Regulation Including Review Tribunals Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerMHAct, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgPromoSum | ST | Total Organisation Mental Health Promotion Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerPromo, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgResearchSum | ST | Total Organisation Mental Health Research Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerResearch, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgTranspSum | ST | Total Organisation Patient Transport Services Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerTransp, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgProgAdminSum | ST | Total Organisation Program Administration Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerProgAdmin, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgPropLeaseSum | ST | Total Organisation Property Leasing Costs Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerPropLease, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgServDevSum | ST | Total Organisation Service Development Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerServDev, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgSuperSum | ST | Total Organisation Superannuation Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerSuper, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgSuppServSum | ST | Total Organisation Support Services Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerSuppServ, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgWorkCompSum | ST | Total Organisation Workers Compensation Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerWorkComp, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgOtherSum | ST | Total Organisation Other Indirect Expenditure Expenditure (NER) | select State, sum(coalesce(ORG.ExpNerOther, 0)) as Total from ST left join ORG using (State) group by State |
| StOrgNerSum | ST | Total Organisation Expenditure (NER) | select State, sum(coalesce(Ent.Total, 0)) as Total from ST left join OrgExpNerTotal as Ent using (State) group by State |
| StWideAcademicSum | ST | Total State Wide Academic Positions Expenditure (NER) | select ST.State as State, (StStAcademicSum.Total + StRegAcademicSum.Total + StOrgAcademicSum.Total) as Total from ST left join StStAcademicSum using (State) left join StRegAcademicSum using (State) left join StOrgAcademicSum using (State) |
| StWideTrainingSum | ST | Total State Wide Education and Training Expenditure (NER) | select ST.State as State, (StStTrainingSum.Total + StRegTrainingSum.Total + StOrgTrainingSum.Total) as Total from ST left join StStTrainingSum using (State) left join StRegTrainingSum using (State) left join StOrgTrainingSum using (State) |
| StWideInsurSum | ST | Total State Wide Insurance Expenditure (NER) | select ST.State as State, (StStInsurSum.Total + StRegInsurSum.Total + StOrgInsurSum.Total) as Total from ST left join StStInsurSum using (State) left join StRegInsurSum using (State) left join StOrgInsurSum using (State) |
| StWideMHActSum | ST | Total State Wide Mental Health Act Regulation Including Review Tribunals Expenditure (NER) | select ST.State as State, (StStMHActSum.Total + StRegMHActSum.Total + StOrgMHActSum.Total) as Total from ST left join StStMHActSum using (State) left join StRegMHActSum using (State) left join StOrgMHActSum using (State) |
| StWidePromoSum | ST | Total State Wide Mental Health Promotion Expenditure (NER) | select ST.State as State, (StStPromoSum.Total + StRegPromoSum.Total + StOrgPromoSum.Total) as Total from ST left join StStPromoSum using (State) left join StRegPromoSum using (State) left join StOrgPromoSum using (State) |
| StWideResearchSum | ST | Total State Wide Mental Health Research Expenditure (NER) | select ST.State as State, (StStResearchSum.Total + StRegResearchSum.Total + StOrgResearchSum.Total) as Total from ST left join StStResearchSum using (State) left join StRegResearchSum using (State) left join StOrgResearchSum using (State) |
| StWideTranspSum | ST | Total State Wide Patient Transport Services Expenditure (NER) | select ST.State as State, (StStTranspSum.Total + StRegTranspSum.Total + StOrgTranspSum.Total) as Total from ST left join StStTranspSum using (State) left join StRegTranspSum using (State) left join StOrgTranspSum using (State) |
| StWideProgAdminSum | ST | Total State Wide Program Administration Expenditure (NER) | select ST.State as State, (StStProgAdminSum.Total + StRegProgAdminSum.Total + StOrgProgAdminSum.Total) as Total from ST left join StStProgAdminSum using (State) left join StRegProgAdminSum using (State) left join StOrgProgAdminSum using (State) |
| StWidePropLeaseSum | ST | Total State Wide Property Leasing Costs Expenditure (NER) | select ST.State as State, (StStPropLeaseSum.Total + StRegPropLeaseSum.Total + StOrgPropLeaseSum.Total) as Total from ST left join StStPropLeaseSum using (State) left join StRegPropLeaseSum using (State) left join StOrgPropLeaseSum using (State) |
| StWideServDevSum | ST | Total State Wide Service Development Expenditure (NER) | select ST.State as State, (StStServDevSum.Total + StRegServDevSum.Total + StOrgServDevSum.Total) as Total from ST left join StStServDevSum using (State) left join StRegServDevSum using (State) left join StOrgServDevSum using (State) |
| StWideSuperSum | ST | Total State Wide Superannuation Expenditure (NER) | select ST.State as State, (StStSuperSum.Total + StRegSuperSum.Total + StOrgSuperSum.Total) as Total from ST left join StStSuperSum using (State) left join StRegSuperSum using (State) left join StOrgSuperSum using (State) |
| StWideSuppServSum | ST | Total State Wide Support Services Expenditure (NER) | select ST.State as State, (StRegSuppServSum.Total + StOrgSuppServSum.Total) as Total from ST left join StRegSuppServSum using (State) left join StOrgSuppServSum using (State) |
| StWideWorkCompSum | ST | Total State Wide Workers Compensation Expenditure (NER) | select ST.State as State, (StStWorkCompSum.Total + StRegWorkCompSum.Total + StOrgWorkCompSum.Total) as Total from ST left join StStWorkCompSum using (State) left join StRegWorkCompSum using (State) left join StOrgWorkCompSum using (State) |
| StWideOtherSum | ST | Total State Wide Other Indirect Expenditure Expenditure (NER) | select ST.State as State, (StStOtherSum.Total + StRegOtherSum.Total + StOrgOtherSum.Total) as Total from ST left join StStOtherSum using (State) left join StRegOtherSum using (State) left join StOrgOtherSum using (State) |
| StAdmiNBedsCAAcSum | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '1' and ProgType = '1' group by State ) as Setting using (State) |
| StAdmiNBedsCANAcSum | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Non-acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '1' and ProgType = '2' group by State ) as Setting using (State) |
| StAdmiNBedsOldAcSum | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '2' and ProgType = '1' group by State ) as Setting using (State) |
| StAdmiNBedsOldNAcSum | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Non-acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '2' and ProgType = '2' group by State ) as Setting using (State) |
| StAdmiNBedsForAcSum | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '3' and ProgType = '1' group by State ) as Setting using (State) |
| StAdmiNBedsForNAcSum | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Non-acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '3' and ProgType = '2' group by State ) as Setting using (State) |
| StAdmiNBedsGenAcSum | ST | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '4' and ProgType = '1' group by State ) as Setting using (State) |
| StAdmiNBedsGenNAcSum | ST | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Non-acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '4' and ProgType = '2' group by State ) as Setting using (State) |
| StAdmiNBedsYthAcSum | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '5' and ProgType = '1' group by State ) as Setting using (State) |
| StAdmiNBedsYthNAcSum | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Non-acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '5' and ProgType = '2' group by State ) as Setting using (State) |
| StAdmiCDaysCAAcSum | ST | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '1' and ProgType = '1' group by State ) as Setting using (State) |
| StAdmiCDaysCANAcSum | ST | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Non-acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '1' and ProgType = '2' group by State ) as Setting using (State) |
| StAdmiCDaysOldAcSum | ST | Total Admitted Accrued Mental Health Care Days for Older person Population - Acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '2' and ProgType = '1' group by State ) as Setting using (State) |
| StAdmiCDaysOldNAcSum | ST | Total Admitted Accrued Mental Health Care Days for Older person Population - Non-acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '2' and ProgType = '2' group by State ) as Setting using (State) |
| StAdmiCDaysForAcSum | ST | Total Admitted Accrued Mental Health Care Days for Forensic Population - Acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '3' and ProgType = '1' group by State ) as Setting using (State) |
| StAdmiCDaysForNAcSum | ST | Total Admitted Accrued Mental Health Care Days for Forensic Population - Non-acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '3' and ProgType = '2' group by State ) as Setting using (State) |
| StAdmiCDaysGenAcSum | ST | Total Admitted Accrued Mental Health Care Days for General Population - Acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '4' and ProgType = '1' group by State ) as Setting using (State) |
| StAdmiCDaysGenNAcSum | ST | Total Admitted Accrued Mental Health Care Days for General Population - Non-acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '4' and ProgType = '2' group by State ) as Setting using (State) |
| StAdmiCDaysYthAcSum | ST | Total Admitted Accrued Mental Health Care Days for Youth Population - Acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '5' and ProgType = '1' group by State ) as Setting using (State) |
| StAdmiCDaysYthNAcSum | ST | Total Admitted Accrued Mental Health Care Days for Youth Population - Non-acute Care | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '5' and ProgType = '2' group by State ) as Setting using (State) |
| StResiNBedsCA24Sum | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - 24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '1' and HrsStaffed = 24 group by State ) as Setting using (State) |
| StResiNBedsCAN24Sum | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - non-24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '1' and HrsStaffed < 24 group by State ) as Setting using (State) |
| StResiNBedsOld24Sum | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - 24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '2' and HrsStaffed = 24 group by State ) as Setting using (State) |
| StResiNBedsOldN24Sum | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - non-24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '2' and HrsStaffed < 24 group by State ) as Setting using (State) |
| StResiNBedsFor24Sum | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - 24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '3' and HrsStaffed = 24 group by State ) as Setting using (State) |
| StResiNBedsForN24Sum | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - non-24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '3' and HrsStaffed < 24 group by State ) as Setting using (State) |
| StResiNBedsGen24Sum | ST | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - 24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '4' and HrsStaffed = 24 group by State ) as Setting using (State) |
| StResiNBedsGenN24Sum | ST | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - non-24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '4' and HrsStaffed < 24 group by State ) as Setting using (State) |
| StResiNBedsYth24Sum | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - 24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '5' and HrsStaffed = 24 group by State ) as Setting using (State) |
| StResiNBedsYthN24Sum | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - non-24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '5' and HrsStaffed < 24 group by State ) as Setting using (State) |
| StResiCDaysCA24Sum | ST | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - 24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '1' and HrsStaffed = 24 group by State ) as Setting using (State) |
| StResiCDaysCAN24Sum | ST | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - non-24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '1' and HrsStaffed < 24 group by State ) as Setting using (State) |
| StResiCDaysOld24Sum | ST | Total Residential Accrued Mental Health Care Days for Older person Population - 24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '2' and HrsStaffed = 24 group by State ) as Setting using (State) |
| StResiCDaysOldN24Sum | ST | Total Residential Accrued Mental Health Care Days for Older person Population - non-24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '2' and HrsStaffed < 24 group by State ) as Setting using (State) |
| StResiCDaysFor24Sum | ST | Total Residential Accrued Mental Health Care Days for Forensic Population - 24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '3' and HrsStaffed = 24 group by State ) as Setting using (State) |
| StResiCDaysForN24Sum | ST | Total Residential Accrued Mental Health Care Days for Forensic Population - non-24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '3' and HrsStaffed < 24 group by State ) as Setting using (State) |
| StResiCDaysGen24Sum | ST | Total Residential Accrued Mental Health Care Days for General Population - 24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '4' and HrsStaffed = 24 group by State ) as Setting using (State) |
| StResiCDaysGenN24Sum | ST | Total Residential Accrued Mental Health Care Days for General Population - non-24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '4' and HrsStaffed < 24 group by State ) as Setting using (State) |
| StResiCDaysYth24Sum | ST | Total Residential Accrued Mental Health Care Days for Youth Population - 24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '5' and HrsStaffed = 24 group by State ) as Setting using (State) |
| StResiCDaysYthN24Sum | ST | Total Residential Accrued Mental Health Care Days for Youth Population - non-24hr staffed | select State, coalesce(Setting.Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '5' and HrsStaffed < 24 group by State ) as Setting using (State) |
| StDCareFteSum | ST | Total State/Territory Direct Care FTE | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(Total, 0)) as Total from FteorgDCareTotal group by State ) tmpinner using (State) |
| StAdmiNBedsAcSum | ST | Total Admitted Acute Average Available Beds for Overnight-stay Patients | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where ProgType = '1' group by State ) tmpinner using (State) |
| StAdmiNBedsNAcSum | ST | Total Admitted Non-acute Average Available Beds for Overnight-stay Patients | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where ProgType = '2' group by State ) tmpinner using (State) |
| StAdmiCDaysAcSum | ST | Total Admitted Acute Accrued Mental Health Care Days | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where ProgType = '1' group by State ) tmpinner using (State) |
| StAdmiCDaysNAcSum | ST | Total Admitted Non-acute Accrued Mental Health Care Days | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where ProgType = '2' group by State ) tmpinner using (State) |
| OrgAdmiNBedsCAAcSum | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '1' and ProgType = '1' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiNBedsCANAcSum | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Non-acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '1' and ProgType = '2' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiNBedsOldAcSum | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '2' and ProgType = '1' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiNBedsOldNAcSum | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Non-acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '2' and ProgType = '2' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiNBedsForAcSum | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '3' and ProgType = '1' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiNBedsForNAcSum | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Non-acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '3' and ProgType = '2' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiNBedsGenAcSum | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '4' and ProgType = '1' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiNBedsGenNAcSum | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Non-acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '4' and ProgType = '2' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiNBedsYthAcSum | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '5' and ProgType = '1' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiNBedsYthNAcSum | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Non-acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '5' and ProgType = '2' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiCDaysCAAcSum | ORG | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '1' and ProgType = '1' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiCDaysCANAcSum | ORG | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Non-acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '1' and ProgType = '2' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiCDaysOldAcSum | ORG | Total Admitted Accrued Mental Health Care Days for Older person Population - Acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '2' and ProgType = '1' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiCDaysOldNAcSum | ORG | Total Admitted Accrued Mental Health Care Days for Older person Population - Non-acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '2' and ProgType = '2' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiCDaysForAcSum | ORG | Total Admitted Accrued Mental Health Care Days for Forensic Population - Acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '3' and ProgType = '1' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiCDaysForNAcSum | ORG | Total Admitted Accrued Mental Health Care Days for Forensic Population - Non-acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '3' and ProgType = '2' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiCDaysGenAcSum | ORG | Total Admitted Accrued Mental Health Care Days for General Population - Acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '4' and ProgType = '1' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiCDaysGenNAcSum | ORG | Total Admitted Accrued Mental Health Care Days for General Population - Non-acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '4' and ProgType = '2' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiCDaysYthAcSum | ORG | Total Admitted Accrued Mental Health Care Days for Youth Population - Acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '5' and ProgType = '1' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgAdmiCDaysYthNAcSum | ORG | Total Admitted Accrued Mental Health Care Days for Youth Population - Non-acute Care | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where TargetPop = '5' and ProgType = '2' group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiNBedsCA24Sum | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - 24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '1' and HrsStaffed = 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiNBedsCAN24Sum | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - non-24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '1' and HrsStaffed < 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiNBedsOld24Sum | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - 24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '2' and HrsStaffed = 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiNBedsOldN24Sum | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - non-24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '2' and HrsStaffed < 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiNBedsFor24Sum | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - 24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '3' and HrsStaffed = 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiNBedsForN24Sum | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - non-24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '3' and HrsStaffed < 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiNBedsGen24Sum | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - 24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '4' and HrsStaffed = 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiNBedsGenN24Sum | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - non-24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '4' and HrsStaffed < 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiNBedsYth24Sum | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - 24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '5' and HrsStaffed = 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiNBedsYthN24Sum | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - non-24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(ResiNBeds, 0)) as Total from RESI where TargetPop = '5' and HrsStaffed < 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiCDaysCA24Sum | ORG | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - 24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '1' and HrsStaffed = 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiCDaysCAN24Sum | ORG | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - non-24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '1' and HrsStaffed < 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiCDaysOld24Sum | ORG | Total Residential Accrued Mental Health Care Days for Older person Population - 24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '2' and HrsStaffed = 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiCDaysOldN24Sum | ORG | Total Residential Accrued Mental Health Care Days for Older person Population - non-24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '2' and HrsStaffed < 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiCDaysFor24Sum | ORG | Total Residential Accrued Mental Health Care Days for Forensic Population - 24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '3' and HrsStaffed = 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiCDaysForN24Sum | ORG | Total Residential Accrued Mental Health Care Days for Forensic Population - non-24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '3' and HrsStaffed < 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiCDaysGen24Sum | ORG | Total Residential Accrued Mental Health Care Days for General Population - 24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '4' and HrsStaffed = 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiCDaysGenN24Sum | ORG | Total Residential Accrued Mental Health Care Days for General Population - non-24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '4' and HrsStaffed < 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiCDaysYth24Sum | ORG | Total Residential Accrued Mental Health Care Days for Youth Population - 24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '5' and HrsStaffed = 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgResiCDaysYthN24Sum | ORG | Total Residential Accrued Mental Health Care Days for Youth Population - non-24hr staffed | select State, RegId, OrgId, coalesce(Setting.Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0)) as Total from RESI where TargetPop = '5' and HrsStaffed < 24 group by State, RegId, OrgId ) as Setting using (State, RegId, OrgId) |
| OrgDCareFteSum | ORG | Total Organisation Direct Care FTE | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(Total, 0)) as Total from FteorgDCareTotal group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAdmiNBedsAcSum | ORG | Total Admitted Acute Average Available Beds for Overnight-stay Patients | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where ProgType = '1' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAdmiNBedsNAcSum | ORG | Total Admitted Non-acute Average Available Beds for Overnight-stay Patients | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(AdmiNBeds, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where ProgType = '2' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAdmiCDaysAcSum | ORG | Total Admitted Acute Accrued Mental Health Care Days | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where ProgType = '1' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| OrgAdmiCDaysNAcSum | ORG | Total Admitted Non-acute Accrued Mental Health Care Days | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(MHCareDays, 0) + coalesce(HitHNBeds, 0)) as Total from ADMI where ProgType = '2' group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| StFteAdminSum | ST | Total Administrative and Clerical FTE reported at Setting level | select State, sum(coalesce(OrgFte.Total, 0)) as Total from ST left join FteAdminSum as OrgFte using (State) group by State |
| StFteCCWrkrSum | ST | Total Carer and Consumer Workers FTE reported at Setting level | select State, sum(coalesce(OrgFte.Total, 0)) as Total from ST left join FteCCWrkrSum as OrgFte using (State) group by State |
| StFteDHPSum | ST | Total Diagnostic and Health Professionals FTE reported at Setting level | select State, sum(coalesce(OrgFte.Total, 0)) as Total from ST left join FteDHPSum as OrgFte using (State) group by State |
| StFteDomestSum | ST | Total Domestic FTE reported at Setting level | select State, sum(coalesce(OrgFte.Total, 0)) as Total from ST left join FteDomestSum as OrgFte using (State) group by State |
| StFteMedSum | ST | Total Medical FTE reported at Setting level | select State, sum(coalesce(OrgFte.Total, 0)) as Total from ST left join FteMedSum as OrgFte using (State) group by State |
| StFteNursesSum | ST | Total Nursing FTE reported at Setting level | select State, sum(coalesce(OrgFte.Total, 0)) as Total from ST left join FteNursesSum as OrgFte using (State) group by State |
| StFtePCareSum | ST | Total Other Personal Care FTE reported at Setting level | select State, sum(coalesce(OrgFte.Total, 0)) as Total from ST left join FtePCareSum as OrgFte using (State) group by State |
| StFteSum | ST | Total FTE reported at Setting level | select State, coalesce(Total, 0) as Total from ST left join ( select State, sum(coalesce(FteAdmin, 0)) + sum(coalesce(FteCCWrkr, 0)) + sum(coalesce(FteDHP, 0)) + sum(coalesce(FteDomest, 0)) + sum(coalesce(FteMed, 0)) + sum(coalesce(FteNurses, 0)) + sum(coalesce(FtePCare, 0)) as Total from FTEORG group by State ) tmpinner using (State) |
| OrgFteSum | ORG | Total FTE reported at Setting level | select State, RegId, OrgId, coalesce(Total, 0) as Total from ORG left join ( select State, RegId, OrgId, sum(coalesce(FteAdmin, 0)) + sum(coalesce(FteCCWrkr, 0)) + sum(coalesce(FteDHP, 0)) + sum(coalesce(FteDomest, 0)) + sum(coalesce(FteMed, 0)) + sum(coalesce(FteNurses, 0)) + sum(coalesce(FtePCare, 0)) as Total from FTEORG group by State, RegId, OrgId ) tmpinner using (State, RegId, OrgId) |
| RegExpNerAcademicChange | REG | Expenditure Not Elsewhere Reported - Academic Positions Change | select State, RegId, New.ExpNerAcademic - Old.ExpNerAcademic as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerAcademicGrowth | REG | Expenditure Not Elsewhere Reported - Academic Positions Growth | select State, RegId, sd_div_safe(New.ExpNerAcademic - Old.ExpNerAcademic, Old.ExpNerAcademic, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerTrainingChange | REG | Expenditure Not Elsewhere Reported - Education and Training Change | select State, RegId, New.ExpNerTraining - Old.ExpNerTraining as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerTrainingGrowth | REG | Expenditure Not Elsewhere Reported - Education and Training Growth | select State, RegId, sd_div_safe(New.ExpNerTraining - Old.ExpNerTraining, Old.ExpNerTraining, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerInsurChange | REG | Expenditure Not Elsewhere Reported - Insurance Change | select State, RegId, New.ExpNerInsur - Old.ExpNerInsur as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerInsurGrowth | REG | Expenditure Not Elsewhere Reported - Insurance Growth | select State, RegId, sd_div_safe(New.ExpNerInsur - Old.ExpNerInsur, Old.ExpNerInsur, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerMHActChange | REG | Expenditure Not Elsewhere Reported - Mental Health Act Regulation or related legislation Change | select State, RegId, New.ExpNerMHAct - Old.ExpNerMHAct as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerMHActGrowth | REG | Expenditure Not Elsewhere Reported - Mental Health Act Regulation or related legislation Growth | select State, RegId, sd_div_safe(New.ExpNerMHAct - Old.ExpNerMHAct, Old.ExpNerMHAct, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerPromoChange | REG | Expenditure Not Elsewhere Reported - Mental Health Promotion Change | select State, RegId, New.ExpNerPromo - Old.ExpNerPromo as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerPromoGrowth | REG | Expenditure Not Elsewhere Reported - Mental Health Promotion Growth | select State, RegId, sd_div_safe(New.ExpNerPromo - Old.ExpNerPromo, Old.ExpNerPromo, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerResearchChange | REG | Expenditure Not Elsewhere Reported - Mental Health Research Change | select State, RegId, New.ExpNerResearch - Old.ExpNerResearch as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerResearchGrowth | REG | Expenditure Not Elsewhere Reported - Mental Health Research Growth | select State, RegId, sd_div_safe(New.ExpNerResearch - Old.ExpNerResearch, Old.ExpNerResearch, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerTranspChange | REG | Expenditure Not Elsewhere Reported - Patient Transport Services Change | select State, RegId, New.ExpNerTransp - Old.ExpNerTransp as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerTranspGrowth | REG | Expenditure Not Elsewhere Reported - Patient Transport Services Growth | select State, RegId, sd_div_safe(New.ExpNerTransp - Old.ExpNerTransp, Old.ExpNerTransp, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerProgAdminChange | REG | Expenditure Not Elsewhere Reported - Program Administration Change | select State, RegId, New.ExpNerProgAdmin - Old.ExpNerProgAdmin as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerProgAdminGrowth | REG | Expenditure Not Elsewhere Reported - Program Administration Growth | select State, RegId, sd_div_safe(New.ExpNerProgAdmin - Old.ExpNerProgAdmin, Old.ExpNerProgAdmin, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerPropLeaseChange | REG | Expenditure Not Elsewhere Reported - Property Leasing Costs Change | select State, RegId, New.ExpNerPropLease - Old.ExpNerPropLease as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerPropLeaseGrowth | REG | Expenditure Not Elsewhere Reported - Property Leasing Costs Growth | select State, RegId, sd_div_safe(New.ExpNerPropLease - Old.ExpNerPropLease, Old.ExpNerPropLease, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerServDevChange | REG | Expenditure Not Elsewhere Reported - Service Development Change | select State, RegId, New.ExpNerServDev - Old.ExpNerServDev as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerServDevGrowth | REG | Expenditure Not Elsewhere Reported - Service Development Growth | select State, RegId, sd_div_safe(New.ExpNerServDev - Old.ExpNerServDev, Old.ExpNerServDev, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerSuperChange | REG | Expenditure Not Elsewhere Reported - Superannuation Change | select State, RegId, New.ExpNerSuper - Old.ExpNerSuper as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerSuperGrowth | REG | Expenditure Not Elsewhere Reported - Superannuation Growth | select State, RegId, sd_div_safe(New.ExpNerSuper - Old.ExpNerSuper, Old.ExpNerSuper, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerSuppServChange | REG | Expenditure Not Elsewhere Reported - Support Services Change | select State, RegId, New.ExpNerSuppServ - Old.ExpNerSuppServ as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerSuppServGrowth | REG | Expenditure Not Elsewhere Reported - Support Services Growth | select State, RegId, sd_div_safe(New.ExpNerSuppServ - Old.ExpNerSuppServ, Old.ExpNerSuppServ, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerWorkCompChange | REG | Expenditure Not Elsewhere Reported - Workers Compensation Change | select State, RegId, New.ExpNerWorkComp - Old.ExpNerWorkComp as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerWorkCompGrowth | REG | Expenditure Not Elsewhere Reported - Workers Compensation Growth | select State, RegId, sd_div_safe(New.ExpNerWorkComp - Old.ExpNerWorkComp, Old.ExpNerWorkComp, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerOtherChange | REG | Expenditure Not Elsewhere Reported - Other Indirect Expenditure Change | select State, RegId, New.ExpNerOther - Old.ExpNerOther as Change from REG as New join hist.REG as Old using (State, RegId) |
| RegExpNerOtherGrowth | REG | Expenditure Not Elsewhere Reported - Other Indirect Expenditure Growth | select State, RegId, sd_div_safe(New.ExpNerOther - Old.ExpNerOther, Old.ExpNerOther, 3) as Growth from REG as New join hist.REG as Old using (State, RegId) |
| OrgExpNerAcademicChange | ORG | Expenditure Not Elsewhere Reported - Academic Positions Change | select State, RegId, OrgId, New.ExpNerAcademic - Old.ExpNerAcademic as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerAcademicGrowth | ORG | Expenditure Not Elsewhere Reported - Academic Positions Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerAcademic - Old.ExpNerAcademic, Old.ExpNerAcademic, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerTrainingChange | ORG | Expenditure Not Elsewhere Reported - Education and Training Change | select State, RegId, OrgId, New.ExpNerTraining - Old.ExpNerTraining as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerTrainingGrowth | ORG | Expenditure Not Elsewhere Reported - Education and Training Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerTraining - Old.ExpNerTraining, Old.ExpNerTraining, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerInsurChange | ORG | Expenditure Not Elsewhere Reported - Insurance Change | select State, RegId, OrgId, New.ExpNerInsur - Old.ExpNerInsur as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerInsurGrowth | ORG | Expenditure Not Elsewhere Reported - Insurance Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerInsur - Old.ExpNerInsur, Old.ExpNerInsur, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerMHActChange | ORG | Expenditure Not Elsewhere Reported - Mental Health Act Regulation or related legislation Change | select State, RegId, OrgId, New.ExpNerMHAct - Old.ExpNerMHAct as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerMHActGrowth | ORG | Expenditure Not Elsewhere Reported - Mental Health Act Regulation or related legislation Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerMHAct - Old.ExpNerMHAct, Old.ExpNerMHAct, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerPromoChange | ORG | Expenditure Not Elsewhere Reported - Mental Health Promotion Change | select State, RegId, OrgId, New.ExpNerPromo - Old.ExpNerPromo as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerPromoGrowth | ORG | Expenditure Not Elsewhere Reported - Mental Health Promotion Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerPromo - Old.ExpNerPromo, Old.ExpNerPromo, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerResearchChange | ORG | Expenditure Not Elsewhere Reported - Mental Health Research Change | select State, RegId, OrgId, New.ExpNerResearch - Old.ExpNerResearch as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerResearchGrowth | ORG | Expenditure Not Elsewhere Reported - Mental Health Research Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerResearch - Old.ExpNerResearch, Old.ExpNerResearch, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerTranspChange | ORG | Expenditure Not Elsewhere Reported - Patient Transport Services Change | select State, RegId, OrgId, New.ExpNerTransp - Old.ExpNerTransp as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerTranspGrowth | ORG | Expenditure Not Elsewhere Reported - Patient Transport Services Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerTransp - Old.ExpNerTransp, Old.ExpNerTransp, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerProgAdminChange | ORG | Expenditure Not Elsewhere Reported - Program Administration Change | select State, RegId, OrgId, New.ExpNerProgAdmin - Old.ExpNerProgAdmin as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerProgAdminGrowth | ORG | Expenditure Not Elsewhere Reported - Program Administration Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerProgAdmin - Old.ExpNerProgAdmin, Old.ExpNerProgAdmin, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerPropLeaseChange | ORG | Expenditure Not Elsewhere Reported - Property Leasing Costs Change | select State, RegId, OrgId, New.ExpNerPropLease - Old.ExpNerPropLease as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerPropLeaseGrowth | ORG | Expenditure Not Elsewhere Reported - Property Leasing Costs Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerPropLease - Old.ExpNerPropLease, Old.ExpNerPropLease, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerServDevChange | ORG | Expenditure Not Elsewhere Reported - Service Development Change | select State, RegId, OrgId, New.ExpNerServDev - Old.ExpNerServDev as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerServDevGrowth | ORG | Expenditure Not Elsewhere Reported - Service Development Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerServDev - Old.ExpNerServDev, Old.ExpNerServDev, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerSuperChange | ORG | Expenditure Not Elsewhere Reported - Superannuation Change | select State, RegId, OrgId, New.ExpNerSuper - Old.ExpNerSuper as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerSuperGrowth | ORG | Expenditure Not Elsewhere Reported - Superannuation Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerSuper - Old.ExpNerSuper, Old.ExpNerSuper, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerSuppServChange | ORG | Expenditure Not Elsewhere Reported - Support Services Change | select State, RegId, OrgId, New.ExpNerSuppServ - Old.ExpNerSuppServ as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerSuppServGrowth | ORG | Expenditure Not Elsewhere Reported - Support Services Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerSuppServ - Old.ExpNerSuppServ, Old.ExpNerSuppServ, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerWorkCompChange | ORG | Expenditure Not Elsewhere Reported - Workers Compensation Change | select State, RegId, OrgId, New.ExpNerWorkComp - Old.ExpNerWorkComp as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerWorkCompGrowth | ORG | Expenditure Not Elsewhere Reported - Workers Compensation Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerWorkComp - Old.ExpNerWorkComp, Old.ExpNerWorkComp, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerOtherChange | ORG | Expenditure Not Elsewhere Reported - Other Indirect Expenditure Change | select State, RegId, OrgId, New.ExpNerOther - Old.ExpNerOther as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgExpNerOtherGrowth | ORG | Expenditure Not Elsewhere Reported - Other Indirect Expenditure Growth | select State, RegId, OrgId, sd_div_safe(New.ExpNerOther - Old.ExpNerOther, Old.ExpNerOther, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteCnsltPsychChange | ORG | Full-Time Equivalent Staff - Consultant Psychiatrists and Psychiatrists Change | select State, RegId, OrgId, New.FteCnsltPsych - Old.FteCnsltPsych as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteCnsltPsychGrowth | ORG | Full-Time Equivalent Staff - Consultant Psychiatrists and Psychiatrists Growth | select State, RegId, OrgId, sd_div_safe(New.FteCnsltPsych - Old.FteCnsltPsych, Old.FteCnsltPsych, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFtePsyRegChange | ORG | Full-Time Equivalent Staff - Psychiatry Registrars and Trainees Change | select State, RegId, OrgId, New.FtePsyReg - Old.FtePsyReg as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFtePsyRegGrowth | ORG | Full-Time Equivalent Staff - Psychiatry Registrars and Trainees Growth | select State, RegId, OrgId, sd_div_safe(New.FtePsyReg - Old.FtePsyReg, Old.FtePsyReg, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteMedOtherChange | ORG | Full-Time Equivalent Staff - Other Medical Officers Change | select State, RegId, OrgId, New.FteMedOther - Old.FteMedOther as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteMedOtherGrowth | ORG | Full-Time Equivalent Staff - Other Medical Officers Growth | select State, RegId, OrgId, sd_div_safe(New.FteMedOther - Old.FteMedOther, Old.FteMedOther, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteNursesRegChange | ORG | Full-Time Equivalent Staff - Registered Nurses Change | select State, RegId, OrgId, New.FteNursesReg - Old.FteNursesReg as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteNursesRegGrowth | ORG | Full-Time Equivalent Staff - Registered Nurses Growth | select State, RegId, OrgId, sd_div_safe(New.FteNursesReg - Old.FteNursesReg, Old.FteNursesReg, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteNursesEnrlChange | ORG | Full-Time Equivalent Staff - Enrolled Nurses Change | select State, RegId, OrgId, New.FteNursesEnrl - Old.FteNursesEnrl as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteNursesEnrlGrowth | ORG | Full-Time Equivalent Staff - Enrolled Nurses Growth | select State, RegId, OrgId, sd_div_safe(New.FteNursesEnrl - Old.FteNursesEnrl, Old.FteNursesEnrl, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteOTChange | ORG | Full-Time Equivalent Staff - Occupational Therapists Change | select State, RegId, OrgId, New.FteOT - Old.FteOT as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteOTGrowth | ORG | Full-Time Equivalent Staff - Occupational Therapists Growth | select State, RegId, OrgId, sd_div_safe(New.FteOT - Old.FteOT, Old.FteOT, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteSocialWkChange | ORG | Full-Time Equivalent Staff - Social Workers Change | select State, RegId, OrgId, New.FteSocialWk - Old.FteSocialWk as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteSocialWkGrowth | ORG | Full-Time Equivalent Staff - Social Workers Growth | select State, RegId, OrgId, sd_div_safe(New.FteSocialWk - Old.FteSocialWk, Old.FteSocialWk, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFtePsycholChange | ORG | Full-Time Equivalent Staff - Psychologists Change | select State, RegId, OrgId, New.FtePsychol - Old.FtePsychol as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFtePsycholGrowth | ORG | Full-Time Equivalent Staff - Psychologists Growth | select State, RegId, OrgId, sd_div_safe(New.FtePsychol - Old.FtePsychol, Old.FtePsychol, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteDHPOtherChange | ORG | Full-Time Equivalent Staff - Other Diagnostic and Health Professionals Change | select State, RegId, OrgId, New.FteDHPOther - Old.FteDHPOther as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteDHPOtherGrowth | ORG | Full-Time Equivalent Staff - Other Diagnostic and Health Professionals Growth | select State, RegId, OrgId, sd_div_safe(New.FteDHPOther - Old.FteDHPOther, Old.FteDHPOther, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteAdminChange | ORG | Full-Time Equivalent Staff - Administrative and Clerical Staff Change | select State, RegId, OrgId, New.FteAdmin - Old.FteAdmin as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteAdminGrowth | ORG | Full-Time Equivalent Staff - Administrative and Clerical Staff Growth | select State, RegId, OrgId, sd_div_safe(New.FteAdmin - Old.FteAdmin, Old.FteAdmin, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteDomestChange | ORG | Full-Time Equivalent Staff - Domestic and Other Staff Change | select State, RegId, OrgId, New.FteDomest - Old.FteDomest as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteDomestGrowth | ORG | Full-Time Equivalent Staff - Domestic and Other Staff Growth | select State, RegId, OrgId, sd_div_safe(New.FteDomest - Old.FteDomest, Old.FteDomest, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteCarerWrkrChange | ORG | Full-Time Equivalent Staff - Mental Health Carer Workers Change | select State, RegId, OrgId, New.FteCarerWrkr - Old.FteCarerWrkr as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteCarerWrkrGrowth | ORG | Full-Time Equivalent Staff - Mental Health Carer Workers Growth | select State, RegId, OrgId, sd_div_safe(New.FteCarerWrkr - Old.FteCarerWrkr, Old.FteCarerWrkr, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteConsrWrkrChange | ORG | Full-Time Equivalent Staff - Mental Health Consumer Workers Change | select State, RegId, OrgId, New.FteConsrWrkr - Old.FteConsrWrkr as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFteConsrWrkrGrowth | ORG | Full-Time Equivalent Staff - Mental Health Consumer Workers Growth | select State, RegId, OrgId, sd_div_safe(New.FteConsrWrkr - Old.FteConsrWrkr, Old.FteConsrWrkr, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFtePCareChange | ORG | Full-Time Equivalent Staff - Other Personal Care Staff Change | select State, RegId, OrgId, New.FtePCare - Old.FtePCare as Change from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgFtePCareGrowth | ORG | Full-Time Equivalent Staff - Other Personal Care Staff Growth | select State, RegId, OrgId, sd_div_safe(New.FtePCare - Old.FtePCare, Old.FtePCare, 3) as Growth from ORG as New join hist.ORG as Old using (State, RegId, OrgId) |
| OrgNonAppExpNerChange | ORG | Non-apportionable Residual Expenditure Change | with New as (select * from OrgNonAppExpNer), Old as (select * from hist.OrgNonAppExpNer) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgNonAppExpNerGrowth | ORG | Non-apportionable Residual Expenditure Growth | with New as (select * from OrgNonAppExpNer), Old as (select * from hist.OrgNonAppExpNer) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgExpNerChange | ORG | Total Residual Expenditure at Organisation Level Change | with New as (select * from OrgExpNerTotal), Old as (select * from hist.OrgExpNerTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgExpNerGrowth | ORG | Total Residual Expenditure at Organisation Level Growth | with New as (select * from OrgExpNerTotal), Old as (select * from hist.OrgExpNerTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgWideExpChange | ORG | Organisation Wide Expenditure Change | with New as (select * from OrgWideExp), Old as (select * from hist.OrgWideExp) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgWideExpGrowth | ORG | Organisation Wide Expenditure Growth | with New as (select * from OrgWideExp), Old as (select * from hist.OrgWideExp) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgWideAppExpChange | ORG | Organisation Wide Apportioned Expenditure Change | with New as (select * from OrgWideAppExp), Old as (select * from hist.OrgWideAppExp) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgWideAppExpGrowth | ORG | Organisation Wide Apportioned Expenditure Growth | with New as (select * from OrgWideAppExp), Old as (select * from hist.OrgWideAppExp) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| RegExpChange | REG | Region Level Total Expenditure Change | with New as (select * from RegExpTotal), Old as (select * from hist.RegExpTotal) select State, RegId, New.Total - Old.Total as Change from New join Old using (State, RegId) |
| RegExpGrowth | REG | Region Level Total Expenditure Growth | with New as (select * from RegExpTotal), Old as (select * from hist.RegExpTotal) select State, RegId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId) |
| RegExpNerChange | REG | Total Residual Expenditure at Region Level Change | with New as (select * from RegExpNerTotal), Old as (select * from hist.RegExpNerTotal) select State, RegId, New.Total - Old.Total as Change from New join Old using (State, RegId) |
| RegExpNerGrowth | REG | Total Residual Expenditure at Region Level Growth | with New as (select * from RegExpNerTotal), Old as (select * from hist.RegExpNerTotal) select State, RegId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId) |
| RegWideExpNerChange | REG | Region Wide Residual Expenditure Change | with New as (select * from RegWideExpNer), Old as (select * from hist.RegWideExpNer) select State, RegId, New.Total - Old.Total as Change from New join Old using (State, RegId) |
| RegWideExpNerGrowth | REG | Region Wide Residual Expenditure Growth | with New as (select * from RegWideExpNer), Old as (select * from hist.RegWideExpNer) select State, RegId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId) |
| RegWideNonAppExpNerChange | REG | Region Wide Non-Apportioned Residual Expenditure Change | with New as (select * from RegWideNonAppExpNer), Old as (select * from hist.RegWideNonAppExpNer) select State, RegId, New.Total - Old.Total as Change from New join Old using (State, RegId) |
| RegWideNonAppExpNerGrowth | REG | Region Wide Non-Apportioned Residual Expenditure Growth | with New as (select * from RegWideNonAppExpNer), Old as (select * from hist.RegWideNonAppExpNer) select State, RegId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId) |
| RegWideExpChange | REG | Region Wide Expenditure Change | with New as (select * from RegWideExp), Old as (select * from hist.RegWideExp) select State, RegId, New.Total - Old.Total as Change from New join Old using (State, RegId) |
| RegWideExpGrowth | REG | Region Wide Expenditure Growth | with New as (select * from RegWideExp), Old as (select * from hist.RegWideExp) select State, RegId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId) |
| StWideExpNerChange | ST | State/Territory Wide Residual Expenditure Change | with New as (select * from StWideExpNer), Old as (select * from hist.StWideExpNer) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideExpNerGrowth | ST | State/Territory Wide Residual Expenditure Growth | with New as (select * from StWideExpNer), Old as (select * from hist.StWideExpNer) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideNonAppExpNerChange | ST | State/Territory Wide Non-Apportioned Residual Expenditure Change | with New as (select * from StWideNonAppExpNer), Old as (select * from hist.StWideNonAppExpNer) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideNonAppExpNerGrowth | ST | State/Territory Wide Non-Apportioned Residual Expenditure Growth | with New as (select * from StWideNonAppExpNer), Old as (select * from hist.StWideNonAppExpNer) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideExpChange | ST | State/Territory Wide Expenditure Change | with New as (select * from StWideExp), Old as (select * from hist.StWideExp) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideExpGrowth | ST | State/Territory Wide Expenditure Growth | with New as (select * from StWideExp), Old as (select * from hist.StWideExp) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| RegNgoChange | REG | Total NGO Expenditure at Region Level Change | with New as (select * from RegNgoTotal), Old as (select * from hist.RegNgoTotal) select State, RegId, New.Total - Old.Total as Change from New join Old using (State, RegId) |
| RegNgoGrowth | REG | Total NGO Expenditure at Region Level Growth | with New as (select * from RegNgoTotal), Old as (select * from hist.RegNgoTotal) select State, RegId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId) |
| StWideNgoChange | ST | State/Territory Wide NGO Expenditure Change | with New as (select * from StWideNgo), Old as (select * from hist.StWideNgo) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideNgoGrowth | ST | State/Territory Wide NGO Expenditure Growth | with New as (select * from StWideNgo), Old as (select * from hist.StWideNgo) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgAdmiNBedsChange | ORG | Total Average Available Beds for Overnight-stay Patients Change | with New as (select * from OrgAdmiNBedsSum), Old as (select * from hist.OrgAdmiNBedsSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsGrowth | ORG | Total Average Available Beds for Overnight-stay Patients Growth | with New as (select * from OrgAdmiNBedsSum), Old as (select * from hist.OrgAdmiNBedsSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsChange | ORG | Total Average Available Beds for Residential Mental Health Patients Change | with New as (select * from OrgResiNBedsSum), Old as (select * from hist.OrgResiNBedsSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsGrowth | ORG | Total Average Available Beds for Residential Mental Health Patients Growth | with New as (select * from OrgResiNBedsSum), Old as (select * from hist.OrgResiNBedsSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysChange | ORG | Total Accrued Mental Health Care Days Change | with New as (select * from OrgAdmiCDaysSum), Old as (select * from hist.OrgAdmiCDaysSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysGrowth | ORG | Total Accrued Mental Health Care Days Growth | with New as (select * from OrgAdmiCDaysSum), Old as (select * from hist.OrgAdmiCDaysSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysChange | ORG | Total Accrued Mental Health Care Days Change | with New as (select * from OrgResiCDaysSum), Old as (select * from hist.OrgResiCDaysSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysGrowth | ORG | Total Accrued Mental Health Care Days Growth | with New as (select * from OrgResiCDaysSum), Old as (select * from hist.OrgResiCDaysSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| RegAdmiNBedsChange | REG | Total Average Available Beds for Overnight-stay Patients Change | with New as (select * from RegAdmiNBedsSum), Old as (select * from hist.RegAdmiNBedsSum) select State, RegId, New.Total - Old.Total as Change from New join Old using (State, RegId) |
| RegAdmiNBedsGrowth | REG | Total Average Available Beds for Overnight-stay Patients Growth | with New as (select * from RegAdmiNBedsSum), Old as (select * from hist.RegAdmiNBedsSum) select State, RegId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId) |
| RegResiNBedsChange | REG | Total Average Available Beds for Residential Mental Health Patients Change | with New as (select * from RegResiNBedsSum), Old as (select * from hist.RegResiNBedsSum) select State, RegId, New.Total - Old.Total as Change from New join Old using (State, RegId) |
| RegResiNBedsGrowth | REG | Total Average Available Beds for Residential Mental Health Patients Growth | with New as (select * from RegResiNBedsSum), Old as (select * from hist.RegResiNBedsSum) select State, RegId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId) |
| RegAdmiCDaysChange | REG | Total Accrued Mental Health Care Days Change | with New as (select * from RegAdmiCDaysSum), Old as (select * from hist.RegAdmiCDaysSum) select State, RegId, New.Total - Old.Total as Change from New join Old using (State, RegId) |
| RegAdmiCDaysGrowth | REG | Total Accrued Mental Health Care Days Growth | with New as (select * from RegAdmiCDaysSum), Old as (select * from hist.RegAdmiCDaysSum) select State, RegId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId) |
| RegResiCDaysChange | REG | Total Accrued Mental Health Care Days Change | with New as (select * from RegResiCDaysSum), Old as (select * from hist.RegResiCDaysSum) select State, RegId, New.Total - Old.Total as Change from New join Old using (State, RegId) |
| RegResiCDaysGrowth | REG | Total Accrued Mental Health Care Days Growth | with New as (select * from RegResiCDaysSum), Old as (select * from hist.RegResiCDaysSum) select State, RegId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId) |
| StAdmiNBedsChange | ST | Total Average Available Beds for Overnight-stay Patients Change | with New as (select * from StAdmiNBedsSum), Old as (select * from hist.StAdmiNBedsSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsGrowth | ST | Total Average Available Beds for Overnight-stay Patients Growth | with New as (select * from StAdmiNBedsSum), Old as (select * from hist.StAdmiNBedsSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsChange | ST | Total Average Available Beds for Residential Mental Health Patients Change | with New as (select * from StResiNBedsSum), Old as (select * from hist.StResiNBedsSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsGrowth | ST | Total Average Available Beds for Residential Mental Health Patients Growth | with New as (select * from StResiNBedsSum), Old as (select * from hist.StResiNBedsSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysChange | ST | Total Accrued Mental Health Care Days Change | with New as (select * from StAdmiCDaysSum), Old as (select * from hist.StAdmiCDaysSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysGrowth | ST | Total Accrued Mental Health Care Days Growth | with New as (select * from StAdmiCDaysSum), Old as (select * from hist.StAdmiCDaysSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysChange | ST | Total Accrued Mental Health Care Days Change | with New as (select * from StResiCDaysSum), Old as (select * from hist.StResiCDaysSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysGrowth | ST | Total Accrued Mental Health Care Days Growth | with New as (select * from StResiCDaysSum), Old as (select * from hist.StResiCDaysSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgAdmiExpChange | ORG | Admitted Patient Service Unit Total Expenditure Change | with New as (select * from OrgAdmiExpTotal), Old as (select * from hist.OrgAdmiExpTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiExpGrowth | ORG | Admitted Patient Service Unit Total Expenditure Growth | with New as (select * from OrgAdmiExpTotal), Old as (select * from hist.OrgAdmiExpTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiExpChange | ORG | Residential Service Unit Total Expenditure Change | with New as (select * from OrgResiExpTotal), Old as (select * from hist.OrgResiExpTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiExpGrowth | ORG | Residential Service Unit Total Expenditure Growth | with New as (select * from OrgResiExpTotal), Old as (select * from hist.OrgResiExpTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAmbuExpChange | ORG | Ambulatory Service Unit Total Expenditure Change | with New as (select * from OrgAmbuExpTotal), Old as (select * from hist.OrgAmbuExpTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAmbuExpGrowth | ORG | Ambulatory Service Unit Total Expenditure Growth | with New as (select * from OrgAmbuExpTotal), Old as (select * from hist.OrgAmbuExpTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiFteChange | ORG | Total FTE for Admitted Patient Service Unit at Organisation Level Change | with New as (select * from OrgAdmiFteSum), Old as (select * from hist.OrgAdmiFteSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiFteGrowth | ORG | Total FTE for Admitted Patient Service Unit at Organisation Level Growth | with New as (select * from OrgAdmiFteSum), Old as (select * from hist.OrgAdmiFteSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiFteChange | ORG | Total FTE for Residential Service Unit at Organisation Level Change | with New as (select * from OrgResiFteSum), Old as (select * from hist.OrgResiFteSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiFteGrowth | ORG | Total FTE for Residential Service Unit at Organisation Level Growth | with New as (select * from OrgResiFteSum), Old as (select * from hist.OrgResiFteSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAmbuFteChange | ORG | Total FTE for Ambulatory Service Unit at Organisation Level Change | with New as (select * from OrgAmbuFteSum), Old as (select * from hist.OrgAmbuFteSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAmbuFteGrowth | ORG | Total FTE for Ambulatory Service Unit at Organisation Level Growth | with New as (select * from OrgAmbuFteSum), Old as (select * from hist.OrgAmbuFteSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiAppExpChange | ORG | Admitted Patient Service Unit Total Apportioned Expenditure Change | with New as (select * from OrgAdmiAppExpTotal), Old as (select * from hist.OrgAdmiAppExpTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiAppExpGrowth | ORG | Admitted Patient Service Unit Total Apportioned Expenditure Growth | with New as (select * from OrgAdmiAppExpTotal), Old as (select * from hist.OrgAdmiAppExpTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiAppExpChange | ORG | Residential Service Unit Total Apportioned Expenditure Change | with New as (select * from OrgResiAppExpTotal), Old as (select * from hist.OrgResiAppExpTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiAppExpGrowth | ORG | Residential Service Unit Total Apportioned Expenditure Growth | with New as (select * from OrgResiAppExpTotal), Old as (select * from hist.OrgResiAppExpTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAmbuAppExpChange | ORG | Ambulatory Service Unit Total Apportioned Expenditure Change | with New as (select * from OrgAmbuAppExpTotal), Old as (select * from hist.OrgAmbuAppExpTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAmbuAppExpGrowth | ORG | Ambulatory Service Unit Total Apportioned Expenditure Growth | with New as (select * from OrgAmbuAppExpTotal), Old as (select * from hist.OrgAmbuAppExpTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiAppExpTotalFmtChange | ORG | Admitted Patient Service Unit Total Apportioned Expenditure (rounded) Change | with New as (select * from OrgAdmiAppExpTotalFmt), Old as (select * from hist.OrgAdmiAppExpTotalFmt) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiAppExpTotalFmtGrowth | ORG | Admitted Patient Service Unit Total Apportioned Expenditure (rounded) Growth | with New as (select * from OrgAdmiAppExpTotalFmt), Old as (select * from hist.OrgAdmiAppExpTotalFmt) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiAppExpTotalFmtChange | ORG | Residential Service Unit Total Apportioned Expenditure (rounded) Change | with New as (select * from OrgResiAppExpTotalFmt), Old as (select * from hist.OrgResiAppExpTotalFmt) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiAppExpTotalFmtGrowth | ORG | Residential Service Unit Total Apportioned Expenditure (rounded) Growth | with New as (select * from OrgResiAppExpTotalFmt), Old as (select * from hist.OrgResiAppExpTotalFmt) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAmbuAppExpTotalFmtChange | ORG | Ambulatory Service Unit Total Apportioned Expenditure (rounded) Change | with New as (select * from OrgAmbuAppExpTotalFmt), Old as (select * from hist.OrgAmbuAppExpTotalFmt) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAmbuAppExpTotalFmtGrowth | ORG | Ambulatory Service Unit Total Apportioned Expenditure (rounded) Growth | with New as (select * from OrgAmbuAppExpTotalFmt), Old as (select * from hist.OrgAmbuAppExpTotalFmt) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiDCareFteChange | ORG | Total Direct Care FTE for Admitted Patient Service Units Change | with New as (select * from OrgAdmiDCareFteSum), Old as (select * from hist.OrgAdmiDCareFteSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiDCareFteGrowth | ORG | Total Direct Care FTE for Admitted Patient Service Units Growth | with New as (select * from OrgAdmiDCareFteSum), Old as (select * from hist.OrgAdmiDCareFteSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiDCareFteChange | ORG | Total Direct Care FTE for Residential Service Units Change | with New as (select * from OrgResiDCareFteSum), Old as (select * from hist.OrgResiDCareFteSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiDCareFteGrowth | ORG | Total Direct Care FTE for Residential Service Units Growth | with New as (select * from OrgResiDCareFteSum), Old as (select * from hist.OrgResiDCareFteSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAmbuDCareFteChange | ORG | Total Direct Care FTE for Ambulatory Service Units Change | with New as (select * from OrgAmbuDCareFteSum), Old as (select * from hist.OrgAmbuDCareFteSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAmbuDCareFteGrowth | ORG | Total Direct Care FTE for Ambulatory Service Units Growth | with New as (select * from OrgAmbuDCareFteSum), Old as (select * from hist.OrgAmbuDCareFteSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgDCareFteChange | ORG | Total Organisation Direct Care FTE Change | with New as (select * from OrgDCareFteSum), Old as (select * from hist.OrgDCareFteSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgDCareFteGrowth | ORG | Total Organisation Direct Care FTE Growth | with New as (select * from OrgDCareFteSum), Old as (select * from hist.OrgDCareFteSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgFteMedChange | ORG | Total Medical FTE reported at Organisation Level Change | with New as (select * from OrgFteMedSum), Old as (select * from hist.OrgFteMedSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgFteMedGrowth | ORG | Total Medical FTE reported at Organisation Level Growth | with New as (select * from OrgFteMedSum), Old as (select * from hist.OrgFteMedSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgFteNursesChange | ORG | Total Nursing FTE reported at Organisation Level Change | with New as (select * from OrgFteNursesSum), Old as (select * from hist.OrgFteNursesSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgFteNursesGrowth | ORG | Total Nursing FTE reported at Organisation Level Growth | with New as (select * from OrgFteNursesSum), Old as (select * from hist.OrgFteNursesSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgFteDHPChange | ORG | Total Diagnostic and Health Professionals FTE reported at Organisation Level Change | with New as (select * from OrgFteDHPSum), Old as (select * from hist.OrgFteDHPSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgFteDHPGrowth | ORG | Total Diagnostic and Health Professionals FTE reported at Organisation Level Growth | with New as (select * from OrgFteDHPSum), Old as (select * from hist.OrgFteDHPSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StAdmiExpChange | ST | Admitted Patient Service Unit Total Expenditure Change | with New as (select * from StAdmiExpTotal), Old as (select * from hist.StAdmiExpTotal) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiExpGrowth | ST | Admitted Patient Service Unit Total Expenditure Growth | with New as (select * from StAdmiExpTotal), Old as (select * from hist.StAdmiExpTotal) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiExpChange | ST | Residential Service Unit Total Expenditure Change | with New as (select * from StResiExpTotal), Old as (select * from hist.StResiExpTotal) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiExpGrowth | ST | Residential Service Unit Total Expenditure Growth | with New as (select * from StResiExpTotal), Old as (select * from hist.StResiExpTotal) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAmbuExpChange | ST | Ambulatory Service Unit Total Expenditure Change | with New as (select * from StAmbuExpTotal), Old as (select * from hist.StAmbuExpTotal) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAmbuExpGrowth | ST | Ambulatory Service Unit Total Expenditure Growth | with New as (select * from StAmbuExpTotal), Old as (select * from hist.StAmbuExpTotal) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiAppExpChange | ST | Admitted Patient Service Unit Total Apportioned Expenditure Change | with New as (select * from StAdmiAppExpTotal), Old as (select * from hist.StAdmiAppExpTotal) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiAppExpGrowth | ST | Admitted Patient Service Unit Total Apportioned Expenditure Growth | with New as (select * from StAdmiAppExpTotal), Old as (select * from hist.StAdmiAppExpTotal) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiAppExpChange | ST | Residential Service Unit Total Apportioned Expenditure Change | with New as (select * from StResiAppExpTotal), Old as (select * from hist.StResiAppExpTotal) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiAppExpGrowth | ST | Residential Service Unit Total Apportioned Expenditure Growth | with New as (select * from StResiAppExpTotal), Old as (select * from hist.StResiAppExpTotal) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAmbuAppExpChange | ST | Ambulatory Service Unit Total Apportioned Expenditure Change | with New as (select * from StAmbuAppExpTotal), Old as (select * from hist.StAmbuAppExpTotal) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAmbuAppExpGrowth | ST | Ambulatory Service Unit Total Apportioned Expenditure Growth | with New as (select * from StAmbuAppExpTotal), Old as (select * from hist.StAmbuAppExpTotal) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiAppExpTotalFmtChange | ST | Admitted Patient Service Unit Total Apportioned Expenditure (rounded) Change | with New as (select * from StAdmiAppExpTotalFmt), Old as (select * from hist.StAdmiAppExpTotalFmt) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiAppExpTotalFmtGrowth | ST | Admitted Patient Service Unit Total Apportioned Expenditure (rounded) Growth | with New as (select * from StAdmiAppExpTotalFmt), Old as (select * from hist.StAdmiAppExpTotalFmt) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiAppExpTotalFmtChange | ST | Residential Service Unit Total Apportioned Expenditure (rounded) Change | with New as (select * from StResiAppExpTotalFmt), Old as (select * from hist.StResiAppExpTotalFmt) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiAppExpTotalFmtGrowth | ST | Residential Service Unit Total Apportioned Expenditure (rounded) Growth | with New as (select * from StResiAppExpTotalFmt), Old as (select * from hist.StResiAppExpTotalFmt) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAmbuAppExpTotalFmtChange | ST | Ambulatory Service Unit Total Apportioned Expenditure (rounded) Change | with New as (select * from StAmbuAppExpTotalFmt), Old as (select * from hist.StAmbuAppExpTotalFmt) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAmbuAppExpTotalFmtGrowth | ST | Ambulatory Service Unit Total Apportioned Expenditure (rounded) Growth | with New as (select * from StAmbuAppExpTotalFmt), Old as (select * from hist.StAmbuAppExpTotalFmt) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StCLExpChange | ST | Co-located Hospitals Total Expenditure Change | with New as (select * from StCLExpTotal), Old as (select * from hist.StCLExpTotal) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StCLExpGrowth | ST | Co-located Hospitals Total Expenditure Growth | with New as (select * from StCLExpTotal), Old as (select * from hist.StCLExpTotal) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StSAExpChange | ST | Stand Alone Hospitals Total Expenditure Change | with New as (select * from StSAExpTotal), Old as (select * from hist.StSAExpTotal) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StSAExpGrowth | ST | Stand Alone Hospitals Total Expenditure Growth | with New as (select * from StSAExpTotal), Old as (select * from hist.StSAExpTotal) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgCLExpChange | ORG | Co-located Hospitals Total Expenditure Change | with New as (select * from OrgCLExpTotal), Old as (select * from hist.OrgCLExpTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgCLExpGrowth | ORG | Co-located Hospitals Total Expenditure Growth | with New as (select * from OrgCLExpTotal), Old as (select * from hist.OrgCLExpTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgSAExpChange | ORG | Stand Alone Hospitals Total Expenditure Change | with New as (select * from OrgSAExpTotal), Old as (select * from hist.OrgSAExpTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgSAExpGrowth | ORG | Stand Alone Hospitals Total Expenditure Growth | with New as (select * from OrgSAExpTotal), Old as (select * from hist.OrgSAExpTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StCLAppExpChange | ST | Co-located Hospitals Total Apportioned Expenditure Change | with New as (select * from StCLAppExpTotal), Old as (select * from hist.StCLAppExpTotal) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StCLAppExpGrowth | ST | Co-located Hospitals Total Apportioned Expenditure Growth | with New as (select * from StCLAppExpTotal), Old as (select * from hist.StCLAppExpTotal) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StSAAppExpChange | ST | Stand Alone Hospitals Total Apportioned Expenditure Change | with New as (select * from StSAAppExpTotal), Old as (select * from hist.StSAAppExpTotal) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StSAAppExpGrowth | ST | Stand Alone Hospitals Total Apportioned Expenditure Growth | with New as (select * from StSAAppExpTotal), Old as (select * from hist.StSAAppExpTotal) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgCLAppExpChange | ORG | Co-located Hospitals Total Apportioned Expenditure Change | with New as (select * from OrgCLAppExpTotal), Old as (select * from hist.OrgCLAppExpTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgCLAppExpGrowth | ORG | Co-located Hospitals Total Apportioned Expenditure Growth | with New as (select * from OrgCLAppExpTotal), Old as (select * from hist.OrgCLAppExpTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgSAAppExpChange | ORG | Stand Alone Hospitals Total Apportioned Expenditure Change | with New as (select * from OrgSAAppExpTotal), Old as (select * from hist.OrgSAAppExpTotal) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgSAAppExpGrowth | ORG | Stand Alone Hospitals Total Apportioned Expenditure Growth | with New as (select * from OrgSAAppExpTotal), Old as (select * from hist.OrgSAAppExpTotal) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StCLAppExpTotalFmtChange | ST | Co-located Hospitals Total Apportioned Expenditure (rounded) Change | with New as (select * from StCLAppExpTotalFmt), Old as (select * from hist.StCLAppExpTotalFmt) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StCLAppExpTotalFmtGrowth | ST | Co-located Hospitals Total Apportioned Expenditure (rounded) Growth | with New as (select * from StCLAppExpTotalFmt), Old as (select * from hist.StCLAppExpTotalFmt) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StSAAppExpTotalFmtChange | ST | Stand Alone Hospitals Total Apportioned Expenditure (rounded) Change | with New as (select * from StSAAppExpTotalFmt), Old as (select * from hist.StSAAppExpTotalFmt) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StSAAppExpTotalFmtGrowth | ST | Stand Alone Hospitals Total Apportioned Expenditure (rounded) Growth | with New as (select * from StSAAppExpTotalFmt), Old as (select * from hist.StSAAppExpTotalFmt) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgCLAppExpTotalFmtChange | ORG | Co-located Hospitals Total Apportioned Expenditure (rounded) Change | with New as (select * from OrgCLAppExpTotalFmt), Old as (select * from hist.OrgCLAppExpTotalFmt) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgCLAppExpTotalFmtGrowth | ORG | Co-located Hospitals Total Apportioned Expenditure (rounded) Growth | with New as (select * from OrgCLAppExpTotalFmt), Old as (select * from hist.OrgCLAppExpTotalFmt) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgSAAppExpTotalFmtChange | ORG | Stand Alone Hospitals Total Apportioned Expenditure (rounded) Change | with New as (select * from OrgSAAppExpTotalFmt), Old as (select * from hist.OrgSAAppExpTotalFmt) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgSAAppExpTotalFmtGrowth | ORG | Stand Alone Hospitals Total Apportioned Expenditure (rounded) Growth | with New as (select * from OrgSAAppExpTotalFmt), Old as (select * from hist.OrgSAAppExpTotalFmt) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StAmbuNContChange | ST | Ambulatory Service Unit Total Number of Service Contacts Change | with New as (select * from StAmbuNContSum), Old as (select * from hist.StAmbuNContSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAmbuNContGrowth | ST | Ambulatory Service Unit Total Number of Service Contacts Growth | with New as (select * from StAmbuNContSum), Old as (select * from hist.StAmbuNContSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgAmbuNContChange | ORG | Ambulatory Service Unit Total Number of Service Contacts Change | with New as (select * from OrgAmbuNContSum), Old as (select * from hist.OrgAmbuNContSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAmbuNContGrowth | ORG | Ambulatory Service Unit Total Number of Service Contacts Growth | with New as (select * from OrgAmbuNContSum), Old as (select * from hist.OrgAmbuNContSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StAdmiDCareFteChange | ST | Total Direct Care FTE for Admitted Patient Service Units Change | with New as (select * from StAdmiDCareFteSum), Old as (select * from hist.StAdmiDCareFteSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiDCareFteGrowth | ST | Total Direct Care FTE for Admitted Patient Service Units Growth | with New as (select * from StAdmiDCareFteSum), Old as (select * from hist.StAdmiDCareFteSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiDCareFteChange | ST | Total Direct Care FTE for Residential Service Units Change | with New as (select * from StResiDCareFteSum), Old as (select * from hist.StResiDCareFteSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiDCareFteGrowth | ST | Total Direct Care FTE for Residential Service Units Growth | with New as (select * from StResiDCareFteSum), Old as (select * from hist.StResiDCareFteSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAmbuDCareFteChange | ST | Total Direct Care FTE for Ambulatory Service Units Change | with New as (select * from StAmbuDCareFteSum), Old as (select * from hist.StAmbuDCareFteSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAmbuDCareFteGrowth | ST | Total Direct Care FTE for Ambulatory Service Units Growth | with New as (select * from StAmbuDCareFteSum), Old as (select * from hist.StAmbuDCareFteSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StDCareFteChange | ST | Total State/Territory Direct Care FTE Change | with New as (select * from StDCareFteSum), Old as (select * from hist.StDCareFteSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StDCareFteGrowth | ST | Total State/Territory Direct Care FTE Growth | with New as (select * from StDCareFteSum), Old as (select * from hist.StDCareFteSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStAcademicChange | ST | Total State/Territory Academic Positions Expenditure (NER) Change | with New as (select * from StStAcademicSum), Old as (select * from hist.StStAcademicSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStAcademicGrowth | ST | Total State/Territory Academic Positions Expenditure (NER) Growth | with New as (select * from StStAcademicSum), Old as (select * from hist.StStAcademicSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStTrainingChange | ST | Total State/Territory Education and Training Expenditure (NER) Change | with New as (select * from StStTrainingSum), Old as (select * from hist.StStTrainingSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStTrainingGrowth | ST | Total State/Territory Education and Training Expenditure (NER) Growth | with New as (select * from StStTrainingSum), Old as (select * from hist.StStTrainingSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStInsurChange | ST | Total State/Territory Insurance Expenditure (NER) Change | with New as (select * from StStInsurSum), Old as (select * from hist.StStInsurSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStInsurGrowth | ST | Total State/Territory Insurance Expenditure (NER) Growth | with New as (select * from StStInsurSum), Old as (select * from hist.StStInsurSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStMHActChange | ST | Total State/Territory Mental Health Act Regulation Including Review Tribunals Expenditure (NER) Change | with New as (select * from StStMHActSum), Old as (select * from hist.StStMHActSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStMHActGrowth | ST | Total State/Territory Mental Health Act Regulation Including Review Tribunals Expenditure (NER) Growth | with New as (select * from StStMHActSum), Old as (select * from hist.StStMHActSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStPromoChange | ST | Total State/Territory Mental Health Promotion Expenditure (NER) Change | with New as (select * from StStPromoSum), Old as (select * from hist.StStPromoSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStPromoGrowth | ST | Total State/Territory Mental Health Promotion Expenditure (NER) Growth | with New as (select * from StStPromoSum), Old as (select * from hist.StStPromoSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStResearchChange | ST | Total State/Territory Mental Health Research Expenditure (NER) Change | with New as (select * from StStResearchSum), Old as (select * from hist.StStResearchSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStResearchGrowth | ST | Total State/Territory Mental Health Research Expenditure (NER) Growth | with New as (select * from StStResearchSum), Old as (select * from hist.StStResearchSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStTranspChange | ST | Total State/Territory Patient Transport Services Expenditure (NER) Change | with New as (select * from StStTranspSum), Old as (select * from hist.StStTranspSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStTranspGrowth | ST | Total State/Territory Patient Transport Services Expenditure (NER) Growth | with New as (select * from StStTranspSum), Old as (select * from hist.StStTranspSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStProgAdminChange | ST | Total State/Territory Program Administration Expenditure (NER) Change | with New as (select * from StStProgAdminSum), Old as (select * from hist.StStProgAdminSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStProgAdminGrowth | ST | Total State/Territory Program Administration Expenditure (NER) Growth | with New as (select * from StStProgAdminSum), Old as (select * from hist.StStProgAdminSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStPropLeaseChange | ST | Total State/Territory Property Leasing Costs Expenditure (NER) Change | with New as (select * from StStPropLeaseSum), Old as (select * from hist.StStPropLeaseSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStPropLeaseGrowth | ST | Total State/Territory Property Leasing Costs Expenditure (NER) Growth | with New as (select * from StStPropLeaseSum), Old as (select * from hist.StStPropLeaseSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStServDevChange | ST | Total State/Territory Service Development Expenditure (NER) Change | with New as (select * from StStServDevSum), Old as (select * from hist.StStServDevSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStServDevGrowth | ST | Total State/Territory Service Development Expenditure (NER) Growth | with New as (select * from StStServDevSum), Old as (select * from hist.StStServDevSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStSuperChange | ST | Total State/Territory Superannuation Expenditure (NER) Change | with New as (select * from StStSuperSum), Old as (select * from hist.StStSuperSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStSuperGrowth | ST | Total State/Territory Superannuation Expenditure (NER) Growth | with New as (select * from StStSuperSum), Old as (select * from hist.StStSuperSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStWorkCompChange | ST | Total State/Territory Workers Compensation Expenditure (NER) Change | with New as (select * from StStWorkCompSum), Old as (select * from hist.StStWorkCompSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStWorkCompGrowth | ST | Total State/Territory Workers Compensation Expenditure (NER) Growth | with New as (select * from StStWorkCompSum), Old as (select * from hist.StStWorkCompSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStOtherChange | ST | Total State/Territory Other Indirect Expenditure Expenditure (NER) Change | with New as (select * from StStOtherSum), Old as (select * from hist.StStOtherSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStOtherGrowth | ST | Total State/Territory Other Indirect Expenditure Expenditure (NER) Growth | with New as (select * from StStOtherSum), Old as (select * from hist.StStOtherSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StStNerChange | ST | Total State/Territory Expenditure (NER) Change | with New as (select * from StStNerSum), Old as (select * from hist.StStNerSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StStNerGrowth | ST | Total State/Territory Expenditure (NER) Growth | with New as (select * from StStNerSum), Old as (select * from hist.StStNerSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegAcademicChange | ST | Total Region Academic Positions Expenditure (NER) Change | with New as (select * from StRegAcademicSum), Old as (select * from hist.StRegAcademicSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegAcademicGrowth | ST | Total Region Academic Positions Expenditure (NER) Growth | with New as (select * from StRegAcademicSum), Old as (select * from hist.StRegAcademicSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegTrainingChange | ST | Total Region Education and Training Expenditure (NER) Change | with New as (select * from StRegTrainingSum), Old as (select * from hist.StRegTrainingSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegTrainingGrowth | ST | Total Region Education and Training Expenditure (NER) Growth | with New as (select * from StRegTrainingSum), Old as (select * from hist.StRegTrainingSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegInsurChange | ST | Total Region Insurance Expenditure (NER) Change | with New as (select * from StRegInsurSum), Old as (select * from hist.StRegInsurSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegInsurGrowth | ST | Total Region Insurance Expenditure (NER) Growth | with New as (select * from StRegInsurSum), Old as (select * from hist.StRegInsurSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegMHActChange | ST | Total Region Mental Health Act Regulation Including Review Tribunals Expenditure (NER) Change | with New as (select * from StRegMHActSum), Old as (select * from hist.StRegMHActSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegMHActGrowth | ST | Total Region Mental Health Act Regulation Including Review Tribunals Expenditure (NER) Growth | with New as (select * from StRegMHActSum), Old as (select * from hist.StRegMHActSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegPromoChange | ST | Total Region Mental Health Promotion Expenditure (NER) Change | with New as (select * from StRegPromoSum), Old as (select * from hist.StRegPromoSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegPromoGrowth | ST | Total Region Mental Health Promotion Expenditure (NER) Growth | with New as (select * from StRegPromoSum), Old as (select * from hist.StRegPromoSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegResearchChange | ST | Total Region Mental Health Research Expenditure (NER) Change | with New as (select * from StRegResearchSum), Old as (select * from hist.StRegResearchSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegResearchGrowth | ST | Total Region Mental Health Research Expenditure (NER) Growth | with New as (select * from StRegResearchSum), Old as (select * from hist.StRegResearchSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegTranspChange | ST | Total Region Patient Transport Services Expenditure (NER) Change | with New as (select * from StRegTranspSum), Old as (select * from hist.StRegTranspSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegTranspGrowth | ST | Total Region Patient Transport Services Expenditure (NER) Growth | with New as (select * from StRegTranspSum), Old as (select * from hist.StRegTranspSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegProgAdminChange | ST | Total Region Program Administration Expenditure (NER) Change | with New as (select * from StRegProgAdminSum), Old as (select * from hist.StRegProgAdminSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegProgAdminGrowth | ST | Total Region Program Administration Expenditure (NER) Growth | with New as (select * from StRegProgAdminSum), Old as (select * from hist.StRegProgAdminSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegPropLeaseChange | ST | Total Region Property Leasing Costs Expenditure (NER) Change | with New as (select * from StRegPropLeaseSum), Old as (select * from hist.StRegPropLeaseSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegPropLeaseGrowth | ST | Total Region Property Leasing Costs Expenditure (NER) Growth | with New as (select * from StRegPropLeaseSum), Old as (select * from hist.StRegPropLeaseSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegServDevChange | ST | Total Region Service Development Expenditure (NER) Change | with New as (select * from StRegServDevSum), Old as (select * from hist.StRegServDevSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegServDevGrowth | ST | Total Region Service Development Expenditure (NER) Growth | with New as (select * from StRegServDevSum), Old as (select * from hist.StRegServDevSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegSuperChange | ST | Total Region Superannuation Expenditure (NER) Change | with New as (select * from StRegSuperSum), Old as (select * from hist.StRegSuperSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegSuperGrowth | ST | Total Region Superannuation Expenditure (NER) Growth | with New as (select * from StRegSuperSum), Old as (select * from hist.StRegSuperSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegSuppServChange | ST | Total Region Support Services Expenditure (NER) Change | with New as (select * from StRegSuppServSum), Old as (select * from hist.StRegSuppServSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegSuppServGrowth | ST | Total Region Support Services Expenditure (NER) Growth | with New as (select * from StRegSuppServSum), Old as (select * from hist.StRegSuppServSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegWorkCompChange | ST | Total Region Workers Compensation Expenditure (NER) Change | with New as (select * from StRegWorkCompSum), Old as (select * from hist.StRegWorkCompSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegWorkCompGrowth | ST | Total Region Workers Compensation Expenditure (NER) Growth | with New as (select * from StRegWorkCompSum), Old as (select * from hist.StRegWorkCompSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegOtherChange | ST | Total Region Other Indirect Expenditure Expenditure (NER) Change | with New as (select * from StRegOtherSum), Old as (select * from hist.StRegOtherSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegOtherGrowth | ST | Total Region Other Indirect Expenditure Expenditure (NER) Growth | with New as (select * from StRegOtherSum), Old as (select * from hist.StRegOtherSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StRegNerChange | ST | Total Region Expenditure (NER) Change | with New as (select * from StRegNerSum), Old as (select * from hist.StRegNerSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StRegNerGrowth | ST | Total Region Expenditure (NER) Growth | with New as (select * from StRegNerSum), Old as (select * from hist.StRegNerSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgAcademicChange | ST | Total Organisation Academic Positions Expenditure (NER) Change | with New as (select * from StOrgAcademicSum), Old as (select * from hist.StOrgAcademicSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgAcademicGrowth | ST | Total Organisation Academic Positions Expenditure (NER) Growth | with New as (select * from StOrgAcademicSum), Old as (select * from hist.StOrgAcademicSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgTrainingChange | ST | Total Organisation Education and Training Expenditure (NER) Change | with New as (select * from StOrgTrainingSum), Old as (select * from hist.StOrgTrainingSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgTrainingGrowth | ST | Total Organisation Education and Training Expenditure (NER) Growth | with New as (select * from StOrgTrainingSum), Old as (select * from hist.StOrgTrainingSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgInsurChange | ST | Total Organisation Insurance Expenditure (NER) Change | with New as (select * from StOrgInsurSum), Old as (select * from hist.StOrgInsurSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgInsurGrowth | ST | Total Organisation Insurance Expenditure (NER) Growth | with New as (select * from StOrgInsurSum), Old as (select * from hist.StOrgInsurSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgMHActChange | ST | Total Organisation Mental Health Act Regulation Including Review Tribunals Expenditure (NER) Change | with New as (select * from StOrgMHActSum), Old as (select * from hist.StOrgMHActSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgMHActGrowth | ST | Total Organisation Mental Health Act Regulation Including Review Tribunals Expenditure (NER) Growth | with New as (select * from StOrgMHActSum), Old as (select * from hist.StOrgMHActSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgPromoChange | ST | Total Organisation Mental Health Promotion Expenditure (NER) Change | with New as (select * from StOrgPromoSum), Old as (select * from hist.StOrgPromoSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgPromoGrowth | ST | Total Organisation Mental Health Promotion Expenditure (NER) Growth | with New as (select * from StOrgPromoSum), Old as (select * from hist.StOrgPromoSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgResearchChange | ST | Total Organisation Mental Health Research Expenditure (NER) Change | with New as (select * from StOrgResearchSum), Old as (select * from hist.StOrgResearchSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgResearchGrowth | ST | Total Organisation Mental Health Research Expenditure (NER) Growth | with New as (select * from StOrgResearchSum), Old as (select * from hist.StOrgResearchSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgTranspChange | ST | Total Organisation Patient Transport Services Expenditure (NER) Change | with New as (select * from StOrgTranspSum), Old as (select * from hist.StOrgTranspSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgTranspGrowth | ST | Total Organisation Patient Transport Services Expenditure (NER) Growth | with New as (select * from StOrgTranspSum), Old as (select * from hist.StOrgTranspSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgProgAdminChange | ST | Total Organisation Program Administration Expenditure (NER) Change | with New as (select * from StOrgProgAdminSum), Old as (select * from hist.StOrgProgAdminSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgProgAdminGrowth | ST | Total Organisation Program Administration Expenditure (NER) Growth | with New as (select * from StOrgProgAdminSum), Old as (select * from hist.StOrgProgAdminSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgPropLeaseChange | ST | Total Organisation Property Leasing Costs Expenditure (NER) Change | with New as (select * from StOrgPropLeaseSum), Old as (select * from hist.StOrgPropLeaseSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgPropLeaseGrowth | ST | Total Organisation Property Leasing Costs Expenditure (NER) Growth | with New as (select * from StOrgPropLeaseSum), Old as (select * from hist.StOrgPropLeaseSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgServDevChange | ST | Total Organisation Service Development Expenditure (NER) Change | with New as (select * from StOrgServDevSum), Old as (select * from hist.StOrgServDevSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgServDevGrowth | ST | Total Organisation Service Development Expenditure (NER) Growth | with New as (select * from StOrgServDevSum), Old as (select * from hist.StOrgServDevSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgSuperChange | ST | Total Organisation Superannuation Expenditure (NER) Change | with New as (select * from StOrgSuperSum), Old as (select * from hist.StOrgSuperSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgSuperGrowth | ST | Total Organisation Superannuation Expenditure (NER) Growth | with New as (select * from StOrgSuperSum), Old as (select * from hist.StOrgSuperSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgSuppServChange | ST | Total Organisation Support Services Expenditure (NER) Change | with New as (select * from StOrgSuppServSum), Old as (select * from hist.StOrgSuppServSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgSuppServGrowth | ST | Total Organisation Support Services Expenditure (NER) Growth | with New as (select * from StOrgSuppServSum), Old as (select * from hist.StOrgSuppServSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgWorkCompChange | ST | Total Organisation Workers Compensation Expenditure (NER) Change | with New as (select * from StOrgWorkCompSum), Old as (select * from hist.StOrgWorkCompSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgWorkCompGrowth | ST | Total Organisation Workers Compensation Expenditure (NER) Growth | with New as (select * from StOrgWorkCompSum), Old as (select * from hist.StOrgWorkCompSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgOtherChange | ST | Total Organisation Other Indirect Expenditure Expenditure (NER) Change | with New as (select * from StOrgOtherSum), Old as (select * from hist.StOrgOtherSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgOtherGrowth | ST | Total Organisation Other Indirect Expenditure Expenditure (NER) Growth | with New as (select * from StOrgOtherSum), Old as (select * from hist.StOrgOtherSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgNerChange | ST | Total Organisation Expenditure (NER) Change | with New as (select * from StOrgNerSum), Old as (select * from hist.StOrgNerSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgNerGrowth | ST | Total Organisation Expenditure (NER) Growth | with New as (select * from StOrgNerSum), Old as (select * from hist.StOrgNerSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideAcademicChange | ST | Total State Wide Academic Positions Expenditure (NER) Change | with New as (select * from StWideAcademicSum), Old as (select * from hist.StWideAcademicSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideAcademicGrowth | ST | Total State Wide Academic Positions Expenditure (NER) Growth | with New as (select * from StWideAcademicSum), Old as (select * from hist.StWideAcademicSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideTrainingChange | ST | Total State Wide Education and Training Expenditure (NER) Change | with New as (select * from StWideTrainingSum), Old as (select * from hist.StWideTrainingSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideTrainingGrowth | ST | Total State Wide Education and Training Expenditure (NER) Growth | with New as (select * from StWideTrainingSum), Old as (select * from hist.StWideTrainingSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideInsurChange | ST | Total State Wide Insurance Expenditure (NER) Change | with New as (select * from StWideInsurSum), Old as (select * from hist.StWideInsurSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideInsurGrowth | ST | Total State Wide Insurance Expenditure (NER) Growth | with New as (select * from StWideInsurSum), Old as (select * from hist.StWideInsurSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideMHActChange | ST | Total State Wide Mental Health Act Regulation Including Review Tribunals Expenditure (NER) Change | with New as (select * from StWideMHActSum), Old as (select * from hist.StWideMHActSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideMHActGrowth | ST | Total State Wide Mental Health Act Regulation Including Review Tribunals Expenditure (NER) Growth | with New as (select * from StWideMHActSum), Old as (select * from hist.StWideMHActSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWidePromoChange | ST | Total State Wide Mental Health Promotion Expenditure (NER) Change | with New as (select * from StWidePromoSum), Old as (select * from hist.StWidePromoSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWidePromoGrowth | ST | Total State Wide Mental Health Promotion Expenditure (NER) Growth | with New as (select * from StWidePromoSum), Old as (select * from hist.StWidePromoSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideResearchChange | ST | Total State Wide Mental Health Research Expenditure (NER) Change | with New as (select * from StWideResearchSum), Old as (select * from hist.StWideResearchSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideResearchGrowth | ST | Total State Wide Mental Health Research Expenditure (NER) Growth | with New as (select * from StWideResearchSum), Old as (select * from hist.StWideResearchSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideTranspChange | ST | Total State Wide Patient Transport Services Expenditure (NER) Change | with New as (select * from StWideTranspSum), Old as (select * from hist.StWideTranspSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideTranspGrowth | ST | Total State Wide Patient Transport Services Expenditure (NER) Growth | with New as (select * from StWideTranspSum), Old as (select * from hist.StWideTranspSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideProgAdminChange | ST | Total State Wide Program Administration Expenditure (NER) Change | with New as (select * from StWideProgAdminSum), Old as (select * from hist.StWideProgAdminSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideProgAdminGrowth | ST | Total State Wide Program Administration Expenditure (NER) Growth | with New as (select * from StWideProgAdminSum), Old as (select * from hist.StWideProgAdminSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWidePropLeaseChange | ST | Total State Wide Property Leasing Costs Expenditure (NER) Change | with New as (select * from StWidePropLeaseSum), Old as (select * from hist.StWidePropLeaseSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWidePropLeaseGrowth | ST | Total State Wide Property Leasing Costs Expenditure (NER) Growth | with New as (select * from StWidePropLeaseSum), Old as (select * from hist.StWidePropLeaseSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideServDevChange | ST | Total State Wide Service Development Expenditure (NER) Change | with New as (select * from StWideServDevSum), Old as (select * from hist.StWideServDevSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideServDevGrowth | ST | Total State Wide Service Development Expenditure (NER) Growth | with New as (select * from StWideServDevSum), Old as (select * from hist.StWideServDevSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideSuperChange | ST | Total State Wide Superannuation Expenditure (NER) Change | with New as (select * from StWideSuperSum), Old as (select * from hist.StWideSuperSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideSuperGrowth | ST | Total State Wide Superannuation Expenditure (NER) Growth | with New as (select * from StWideSuperSum), Old as (select * from hist.StWideSuperSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideSuppServChange | ST | Total State Wide Support Services Expenditure (NER) Change | with New as (select * from StWideSuppServSum), Old as (select * from hist.StWideSuppServSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideSuppServGrowth | ST | Total State Wide Support Services Expenditure (NER) Growth | with New as (select * from StWideSuppServSum), Old as (select * from hist.StWideSuppServSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideWorkCompChange | ST | Total State Wide Workers Compensation Expenditure (NER) Change | with New as (select * from StWideWorkCompSum), Old as (select * from hist.StWideWorkCompSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideWorkCompGrowth | ST | Total State Wide Workers Compensation Expenditure (NER) Growth | with New as (select * from StWideWorkCompSum), Old as (select * from hist.StWideWorkCompSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StWideOtherChange | ST | Total State Wide Other Indirect Expenditure Expenditure (NER) Change | with New as (select * from StWideOtherSum), Old as (select * from hist.StWideOtherSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StWideOtherGrowth | ST | Total State Wide Other Indirect Expenditure Expenditure (NER) Growth | with New as (select * from StWideOtherSum), Old as (select * from hist.StWideOtherSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsCA24Change | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - 24hr staffed Change | with New as (select * from StResiNBedsCA24Sum), Old as (select * from hist.StResiNBedsCA24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsCA24Growth | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - 24hr staffed Growth | with New as (select * from StResiNBedsCA24Sum), Old as (select * from hist.StResiNBedsCA24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsCAN24Change | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - non-24hr staffed Change | with New as (select * from StResiNBedsCAN24Sum), Old as (select * from hist.StResiNBedsCAN24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsCAN24Growth | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - non-24hr staffed Growth | with New as (select * from StResiNBedsCAN24Sum), Old as (select * from hist.StResiNBedsCAN24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiNBedsCAAcChange | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Acute Care Change | with New as (select * from StAdmiNBedsCAAcSum), Old as (select * from hist.StAdmiNBedsCAAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsCAAcGrowth | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Acute Care Growth | with New as (select * from StAdmiNBedsCAAcSum), Old as (select * from hist.StAdmiNBedsCAAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiNBedsCANAcChange | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Non-acute Care Change | with New as (select * from StAdmiNBedsCANAcSum), Old as (select * from hist.StAdmiNBedsCANAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsCANAcGrowth | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Non-acute Care Growth | with New as (select * from StAdmiNBedsCANAcSum), Old as (select * from hist.StAdmiNBedsCANAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsOld24Change | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - 24hr staffed Change | with New as (select * from StResiNBedsOld24Sum), Old as (select * from hist.StResiNBedsOld24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsOld24Growth | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - 24hr staffed Growth | with New as (select * from StResiNBedsOld24Sum), Old as (select * from hist.StResiNBedsOld24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsOldN24Change | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - non-24hr staffed Change | with New as (select * from StResiNBedsOldN24Sum), Old as (select * from hist.StResiNBedsOldN24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsOldN24Growth | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - non-24hr staffed Growth | with New as (select * from StResiNBedsOldN24Sum), Old as (select * from hist.StResiNBedsOldN24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiNBedsOldAcChange | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Acute Care Change | with New as (select * from StAdmiNBedsOldAcSum), Old as (select * from hist.StAdmiNBedsOldAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsOldAcGrowth | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Acute Care Growth | with New as (select * from StAdmiNBedsOldAcSum), Old as (select * from hist.StAdmiNBedsOldAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiNBedsOldNAcChange | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Non-acute Care Change | with New as (select * from StAdmiNBedsOldNAcSum), Old as (select * from hist.StAdmiNBedsOldNAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsOldNAcGrowth | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Non-acute Care Growth | with New as (select * from StAdmiNBedsOldNAcSum), Old as (select * from hist.StAdmiNBedsOldNAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsFor24Change | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - 24hr staffed Change | with New as (select * from StResiNBedsFor24Sum), Old as (select * from hist.StResiNBedsFor24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsFor24Growth | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - 24hr staffed Growth | with New as (select * from StResiNBedsFor24Sum), Old as (select * from hist.StResiNBedsFor24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsForN24Change | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - non-24hr staffed Change | with New as (select * from StResiNBedsForN24Sum), Old as (select * from hist.StResiNBedsForN24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsForN24Growth | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - non-24hr staffed Growth | with New as (select * from StResiNBedsForN24Sum), Old as (select * from hist.StResiNBedsForN24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiNBedsForAcChange | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Acute Care Change | with New as (select * from StAdmiNBedsForAcSum), Old as (select * from hist.StAdmiNBedsForAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsForAcGrowth | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Acute Care Growth | with New as (select * from StAdmiNBedsForAcSum), Old as (select * from hist.StAdmiNBedsForAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiNBedsForNAcChange | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Non-acute Care Change | with New as (select * from StAdmiNBedsForNAcSum), Old as (select * from hist.StAdmiNBedsForNAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsForNAcGrowth | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Non-acute Care Growth | with New as (select * from StAdmiNBedsForNAcSum), Old as (select * from hist.StAdmiNBedsForNAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsGen24Change | ST | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - 24hr staffed Change | with New as (select * from StResiNBedsGen24Sum), Old as (select * from hist.StResiNBedsGen24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsGen24Growth | ST | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - 24hr staffed Growth | with New as (select * from StResiNBedsGen24Sum), Old as (select * from hist.StResiNBedsGen24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsGenN24Change | ST | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - non-24hr staffed Change | with New as (select * from StResiNBedsGenN24Sum), Old as (select * from hist.StResiNBedsGenN24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsGenN24Growth | ST | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - non-24hr staffed Growth | with New as (select * from StResiNBedsGenN24Sum), Old as (select * from hist.StResiNBedsGenN24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiNBedsGenAcChange | ST | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Acute Care Change | with New as (select * from StAdmiNBedsGenAcSum), Old as (select * from hist.StAdmiNBedsGenAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsGenAcGrowth | ST | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Acute Care Growth | with New as (select * from StAdmiNBedsGenAcSum), Old as (select * from hist.StAdmiNBedsGenAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiNBedsGenNAcChange | ST | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Non-acute Care Change | with New as (select * from StAdmiNBedsGenNAcSum), Old as (select * from hist.StAdmiNBedsGenNAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsGenNAcGrowth | ST | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Non-acute Care Growth | with New as (select * from StAdmiNBedsGenNAcSum), Old as (select * from hist.StAdmiNBedsGenNAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsYth24Change | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - 24hr staffed Change | with New as (select * from StResiNBedsYth24Sum), Old as (select * from hist.StResiNBedsYth24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsYth24Growth | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - 24hr staffed Growth | with New as (select * from StResiNBedsYth24Sum), Old as (select * from hist.StResiNBedsYth24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiNBedsYthN24Change | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - non-24hr staffed Change | with New as (select * from StResiNBedsYthN24Sum), Old as (select * from hist.StResiNBedsYthN24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiNBedsYthN24Growth | ST | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - non-24hr staffed Growth | with New as (select * from StResiNBedsYthN24Sum), Old as (select * from hist.StResiNBedsYthN24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiNBedsYthAcChange | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Acute Care Change | with New as (select * from StAdmiNBedsYthAcSum), Old as (select * from hist.StAdmiNBedsYthAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsYthAcGrowth | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Acute Care Growth | with New as (select * from StAdmiNBedsYthAcSum), Old as (select * from hist.StAdmiNBedsYthAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiNBedsYthNAcChange | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Non-acute Care Change | with New as (select * from StAdmiNBedsYthNAcSum), Old as (select * from hist.StAdmiNBedsYthNAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiNBedsYthNAcGrowth | ST | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Non-acute Care Growth | with New as (select * from StAdmiNBedsYthNAcSum), Old as (select * from hist.StAdmiNBedsYthNAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysCA24Change | ST | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - 24hr staffed Change | with New as (select * from StResiCDaysCA24Sum), Old as (select * from hist.StResiCDaysCA24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysCA24Growth | ST | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - 24hr staffed Growth | with New as (select * from StResiCDaysCA24Sum), Old as (select * from hist.StResiCDaysCA24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysCAN24Change | ST | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - non-24hr staffed Change | with New as (select * from StResiCDaysCAN24Sum), Old as (select * from hist.StResiCDaysCAN24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysCAN24Growth | ST | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - non-24hr staffed Growth | with New as (select * from StResiCDaysCAN24Sum), Old as (select * from hist.StResiCDaysCAN24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysCAAcChange | ST | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Acute Care Change | with New as (select * from StAdmiCDaysCAAcSum), Old as (select * from hist.StAdmiCDaysCAAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysCAAcGrowth | ST | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Acute Care Growth | with New as (select * from StAdmiCDaysCAAcSum), Old as (select * from hist.StAdmiCDaysCAAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysCANAcChange | ST | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Non-acute Care Change | with New as (select * from StAdmiCDaysCANAcSum), Old as (select * from hist.StAdmiCDaysCANAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysCANAcGrowth | ST | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Non-acute Care Growth | with New as (select * from StAdmiCDaysCANAcSum), Old as (select * from hist.StAdmiCDaysCANAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysOld24Change | ST | Total Residential Accrued Mental Health Care Days for Older person Population - 24hr staffed Change | with New as (select * from StResiCDaysOld24Sum), Old as (select * from hist.StResiCDaysOld24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysOld24Growth | ST | Total Residential Accrued Mental Health Care Days for Older person Population - 24hr staffed Growth | with New as (select * from StResiCDaysOld24Sum), Old as (select * from hist.StResiCDaysOld24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysOldN24Change | ST | Total Residential Accrued Mental Health Care Days for Older person Population - non-24hr staffed Change | with New as (select * from StResiCDaysOldN24Sum), Old as (select * from hist.StResiCDaysOldN24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysOldN24Growth | ST | Total Residential Accrued Mental Health Care Days for Older person Population - non-24hr staffed Growth | with New as (select * from StResiCDaysOldN24Sum), Old as (select * from hist.StResiCDaysOldN24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysOldAcChange | ST | Total Admitted Accrued Mental Health Care Days for Older person Population - Acute Care Change | with New as (select * from StAdmiCDaysOldAcSum), Old as (select * from hist.StAdmiCDaysOldAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysOldAcGrowth | ST | Total Admitted Accrued Mental Health Care Days for Older person Population - Acute Care Growth | with New as (select * from StAdmiCDaysOldAcSum), Old as (select * from hist.StAdmiCDaysOldAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysOldNAcChange | ST | Total Admitted Accrued Mental Health Care Days for Older person Population - Non-acute Care Change | with New as (select * from StAdmiCDaysOldNAcSum), Old as (select * from hist.StAdmiCDaysOldNAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysOldNAcGrowth | ST | Total Admitted Accrued Mental Health Care Days for Older person Population - Non-acute Care Growth | with New as (select * from StAdmiCDaysOldNAcSum), Old as (select * from hist.StAdmiCDaysOldNAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysFor24Change | ST | Total Residential Accrued Mental Health Care Days for Forensic Population - 24hr staffed Change | with New as (select * from StResiCDaysFor24Sum), Old as (select * from hist.StResiCDaysFor24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysFor24Growth | ST | Total Residential Accrued Mental Health Care Days for Forensic Population - 24hr staffed Growth | with New as (select * from StResiCDaysFor24Sum), Old as (select * from hist.StResiCDaysFor24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysForN24Change | ST | Total Residential Accrued Mental Health Care Days for Forensic Population - non-24hr staffed Change | with New as (select * from StResiCDaysForN24Sum), Old as (select * from hist.StResiCDaysForN24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysForN24Growth | ST | Total Residential Accrued Mental Health Care Days for Forensic Population - non-24hr staffed Growth | with New as (select * from StResiCDaysForN24Sum), Old as (select * from hist.StResiCDaysForN24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysForAcChange | ST | Total Admitted Accrued Mental Health Care Days for Forensic Population - Acute Care Change | with New as (select * from StAdmiCDaysForAcSum), Old as (select * from hist.StAdmiCDaysForAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysForAcGrowth | ST | Total Admitted Accrued Mental Health Care Days for Forensic Population - Acute Care Growth | with New as (select * from StAdmiCDaysForAcSum), Old as (select * from hist.StAdmiCDaysForAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysForNAcChange | ST | Total Admitted Accrued Mental Health Care Days for Forensic Population - Non-acute Care Change | with New as (select * from StAdmiCDaysForNAcSum), Old as (select * from hist.StAdmiCDaysForNAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysForNAcGrowth | ST | Total Admitted Accrued Mental Health Care Days for Forensic Population - Non-acute Care Growth | with New as (select * from StAdmiCDaysForNAcSum), Old as (select * from hist.StAdmiCDaysForNAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysGen24Change | ST | Total Residential Accrued Mental Health Care Days for General Population - 24hr staffed Change | with New as (select * from StResiCDaysGen24Sum), Old as (select * from hist.StResiCDaysGen24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysGen24Growth | ST | Total Residential Accrued Mental Health Care Days for General Population - 24hr staffed Growth | with New as (select * from StResiCDaysGen24Sum), Old as (select * from hist.StResiCDaysGen24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysGenN24Change | ST | Total Residential Accrued Mental Health Care Days for General Population - non-24hr staffed Change | with New as (select * from StResiCDaysGenN24Sum), Old as (select * from hist.StResiCDaysGenN24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysGenN24Growth | ST | Total Residential Accrued Mental Health Care Days for General Population - non-24hr staffed Growth | with New as (select * from StResiCDaysGenN24Sum), Old as (select * from hist.StResiCDaysGenN24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysGenAcChange | ST | Total Admitted Accrued Mental Health Care Days for General Population - Acute Care Change | with New as (select * from StAdmiCDaysGenAcSum), Old as (select * from hist.StAdmiCDaysGenAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysGenAcGrowth | ST | Total Admitted Accrued Mental Health Care Days for General Population - Acute Care Growth | with New as (select * from StAdmiCDaysGenAcSum), Old as (select * from hist.StAdmiCDaysGenAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysGenNAcChange | ST | Total Admitted Accrued Mental Health Care Days for General Population - Non-acute Care Change | with New as (select * from StAdmiCDaysGenNAcSum), Old as (select * from hist.StAdmiCDaysGenNAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysGenNAcGrowth | ST | Total Admitted Accrued Mental Health Care Days for General Population - Non-acute Care Growth | with New as (select * from StAdmiCDaysGenNAcSum), Old as (select * from hist.StAdmiCDaysGenNAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysYth24Change | ST | Total Residential Accrued Mental Health Care Days for Youth Population - 24hr staffed Change | with New as (select * from StResiCDaysYth24Sum), Old as (select * from hist.StResiCDaysYth24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysYth24Growth | ST | Total Residential Accrued Mental Health Care Days for Youth Population - 24hr staffed Growth | with New as (select * from StResiCDaysYth24Sum), Old as (select * from hist.StResiCDaysYth24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StResiCDaysYthN24Change | ST | Total Residential Accrued Mental Health Care Days for Youth Population - non-24hr staffed Change | with New as (select * from StResiCDaysYthN24Sum), Old as (select * from hist.StResiCDaysYthN24Sum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StResiCDaysYthN24Growth | ST | Total Residential Accrued Mental Health Care Days for Youth Population - non-24hr staffed Growth | with New as (select * from StResiCDaysYthN24Sum), Old as (select * from hist.StResiCDaysYthN24Sum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysYthAcChange | ST | Total Admitted Accrued Mental Health Care Days for Youth Population - Acute Care Change | with New as (select * from StAdmiCDaysYthAcSum), Old as (select * from hist.StAdmiCDaysYthAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysYthAcGrowth | ST | Total Admitted Accrued Mental Health Care Days for Youth Population - Acute Care Growth | with New as (select * from StAdmiCDaysYthAcSum), Old as (select * from hist.StAdmiCDaysYthAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StAdmiCDaysYthNAcChange | ST | Total Admitted Accrued Mental Health Care Days for Youth Population - Non-acute Care Change | with New as (select * from StAdmiCDaysYthNAcSum), Old as (select * from hist.StAdmiCDaysYthNAcSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StAdmiCDaysYthNAcGrowth | ST | Total Admitted Accrued Mental Health Care Days for Youth Population - Non-acute Care Growth | with New as (select * from StAdmiCDaysYthNAcSum), Old as (select * from hist.StAdmiCDaysYthNAcSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgResiNBedsCA24Change | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - 24hr staffed Change | with New as (select * from OrgResiNBedsCA24Sum), Old as (select * from hist.OrgResiNBedsCA24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsCA24Growth | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - 24hr staffed Growth | with New as (select * from OrgResiNBedsCA24Sum), Old as (select * from hist.OrgResiNBedsCA24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsCAN24Change | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - non-24hr staffed Change | with New as (select * from OrgResiNBedsCAN24Sum), Old as (select * from hist.OrgResiNBedsCAN24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsCAN24Growth | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Child and adolescent Population - non-24hr staffed Growth | with New as (select * from OrgResiNBedsCAN24Sum), Old as (select * from hist.OrgResiNBedsCAN24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsCAAcChange | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Acute Care Change | with New as (select * from OrgAdmiNBedsCAAcSum), Old as (select * from hist.OrgAdmiNBedsCAAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsCAAcGrowth | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Acute Care Growth | with New as (select * from OrgAdmiNBedsCAAcSum), Old as (select * from hist.OrgAdmiNBedsCAAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsCANAcChange | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Non-acute Care Change | with New as (select * from OrgAdmiNBedsCANAcSum), Old as (select * from hist.OrgAdmiNBedsCANAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsCANAcGrowth | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Child and adolescent Population - Non-acute Care Growth | with New as (select * from OrgAdmiNBedsCANAcSum), Old as (select * from hist.OrgAdmiNBedsCANAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsOld24Change | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - 24hr staffed Change | with New as (select * from OrgResiNBedsOld24Sum), Old as (select * from hist.OrgResiNBedsOld24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsOld24Growth | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - 24hr staffed Growth | with New as (select * from OrgResiNBedsOld24Sum), Old as (select * from hist.OrgResiNBedsOld24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsOldN24Change | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - non-24hr staffed Change | with New as (select * from OrgResiNBedsOldN24Sum), Old as (select * from hist.OrgResiNBedsOldN24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsOldN24Growth | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Older person Population - non-24hr staffed Growth | with New as (select * from OrgResiNBedsOldN24Sum), Old as (select * from hist.OrgResiNBedsOldN24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsOldAcChange | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Acute Care Change | with New as (select * from OrgAdmiNBedsOldAcSum), Old as (select * from hist.OrgAdmiNBedsOldAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsOldAcGrowth | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Acute Care Growth | with New as (select * from OrgAdmiNBedsOldAcSum), Old as (select * from hist.OrgAdmiNBedsOldAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsOldNAcChange | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Non-acute Care Change | with New as (select * from OrgAdmiNBedsOldNAcSum), Old as (select * from hist.OrgAdmiNBedsOldNAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsOldNAcGrowth | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Older person Population - Non-acute Care Growth | with New as (select * from OrgAdmiNBedsOldNAcSum), Old as (select * from hist.OrgAdmiNBedsOldNAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsFor24Change | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - 24hr staffed Change | with New as (select * from OrgResiNBedsFor24Sum), Old as (select * from hist.OrgResiNBedsFor24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsFor24Growth | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - 24hr staffed Growth | with New as (select * from OrgResiNBedsFor24Sum), Old as (select * from hist.OrgResiNBedsFor24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsForN24Change | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - non-24hr staffed Change | with New as (select * from OrgResiNBedsForN24Sum), Old as (select * from hist.OrgResiNBedsForN24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsForN24Growth | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Forensic Population - non-24hr staffed Growth | with New as (select * from OrgResiNBedsForN24Sum), Old as (select * from hist.OrgResiNBedsForN24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsForAcChange | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Acute Care Change | with New as (select * from OrgAdmiNBedsForAcSum), Old as (select * from hist.OrgAdmiNBedsForAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsForAcGrowth | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Acute Care Growth | with New as (select * from OrgAdmiNBedsForAcSum), Old as (select * from hist.OrgAdmiNBedsForAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsForNAcChange | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Non-acute Care Change | with New as (select * from OrgAdmiNBedsForNAcSum), Old as (select * from hist.OrgAdmiNBedsForNAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsForNAcGrowth | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Forensic Population - Non-acute Care Growth | with New as (select * from OrgAdmiNBedsForNAcSum), Old as (select * from hist.OrgAdmiNBedsForNAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsGen24Change | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - 24hr staffed Change | with New as (select * from OrgResiNBedsGen24Sum), Old as (select * from hist.OrgResiNBedsGen24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsGen24Growth | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - 24hr staffed Growth | with New as (select * from OrgResiNBedsGen24Sum), Old as (select * from hist.OrgResiNBedsGen24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsGenN24Change | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - non-24hr staffed Change | with New as (select * from OrgResiNBedsGenN24Sum), Old as (select * from hist.OrgResiNBedsGenN24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsGenN24Growth | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for General Population - non-24hr staffed Growth | with New as (select * from OrgResiNBedsGenN24Sum), Old as (select * from hist.OrgResiNBedsGenN24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsGenAcChange | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Acute Care Change | with New as (select * from OrgAdmiNBedsGenAcSum), Old as (select * from hist.OrgAdmiNBedsGenAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsGenAcGrowth | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Acute Care Growth | with New as (select * from OrgAdmiNBedsGenAcSum), Old as (select * from hist.OrgAdmiNBedsGenAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsGenNAcChange | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Non-acute Care Change | with New as (select * from OrgAdmiNBedsGenNAcSum), Old as (select * from hist.OrgAdmiNBedsGenNAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsGenNAcGrowth | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for General Population - Non-acute Care Growth | with New as (select * from OrgAdmiNBedsGenNAcSum), Old as (select * from hist.OrgAdmiNBedsGenNAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsYth24Change | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - 24hr staffed Change | with New as (select * from OrgResiNBedsYth24Sum), Old as (select * from hist.OrgResiNBedsYth24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsYth24Growth | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - 24hr staffed Growth | with New as (select * from OrgResiNBedsYth24Sum), Old as (select * from hist.OrgResiNBedsYth24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsYthN24Change | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - non-24hr staffed Change | with New as (select * from OrgResiNBedsYthN24Sum), Old as (select * from hist.OrgResiNBedsYthN24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiNBedsYthN24Growth | ORG | Total Residential Average Available Beds for Residential Mental Health Patients for Youth Population - non-24hr staffed Growth | with New as (select * from OrgResiNBedsYthN24Sum), Old as (select * from hist.OrgResiNBedsYthN24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsYthAcChange | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Acute Care Change | with New as (select * from OrgAdmiNBedsYthAcSum), Old as (select * from hist.OrgAdmiNBedsYthAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsYthAcGrowth | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Acute Care Growth | with New as (select * from OrgAdmiNBedsYthAcSum), Old as (select * from hist.OrgAdmiNBedsYthAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsYthNAcChange | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Non-acute Care Change | with New as (select * from OrgAdmiNBedsYthNAcSum), Old as (select * from hist.OrgAdmiNBedsYthNAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiNBedsYthNAcGrowth | ORG | Total Admitted Average Available Beds for Overnight-stay Patients for Youth Population - Non-acute Care Growth | with New as (select * from OrgAdmiNBedsYthNAcSum), Old as (select * from hist.OrgAdmiNBedsYthNAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysCA24Change | ORG | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - 24hr staffed Change | with New as (select * from OrgResiCDaysCA24Sum), Old as (select * from hist.OrgResiCDaysCA24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysCA24Growth | ORG | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - 24hr staffed Growth | with New as (select * from OrgResiCDaysCA24Sum), Old as (select * from hist.OrgResiCDaysCA24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysCAN24Change | ORG | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - non-24hr staffed Change | with New as (select * from OrgResiCDaysCAN24Sum), Old as (select * from hist.OrgResiCDaysCAN24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysCAN24Growth | ORG | Total Residential Accrued Mental Health Care Days for Child and adolescent Population - non-24hr staffed Growth | with New as (select * from OrgResiCDaysCAN24Sum), Old as (select * from hist.OrgResiCDaysCAN24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysCAAcChange | ORG | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Acute Care Change | with New as (select * from OrgAdmiCDaysCAAcSum), Old as (select * from hist.OrgAdmiCDaysCAAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysCAAcGrowth | ORG | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Acute Care Growth | with New as (select * from OrgAdmiCDaysCAAcSum), Old as (select * from hist.OrgAdmiCDaysCAAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysCANAcChange | ORG | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Non-acute Care Change | with New as (select * from OrgAdmiCDaysCANAcSum), Old as (select * from hist.OrgAdmiCDaysCANAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysCANAcGrowth | ORG | Total Admitted Accrued Mental Health Care Days for Child and adolescent Population - Non-acute Care Growth | with New as (select * from OrgAdmiCDaysCANAcSum), Old as (select * from hist.OrgAdmiCDaysCANAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysOld24Change | ORG | Total Residential Accrued Mental Health Care Days for Older person Population - 24hr staffed Change | with New as (select * from OrgResiCDaysOld24Sum), Old as (select * from hist.OrgResiCDaysOld24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysOld24Growth | ORG | Total Residential Accrued Mental Health Care Days for Older person Population - 24hr staffed Growth | with New as (select * from OrgResiCDaysOld24Sum), Old as (select * from hist.OrgResiCDaysOld24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysOldN24Change | ORG | Total Residential Accrued Mental Health Care Days for Older person Population - non-24hr staffed Change | with New as (select * from OrgResiCDaysOldN24Sum), Old as (select * from hist.OrgResiCDaysOldN24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysOldN24Growth | ORG | Total Residential Accrued Mental Health Care Days for Older person Population - non-24hr staffed Growth | with New as (select * from OrgResiCDaysOldN24Sum), Old as (select * from hist.OrgResiCDaysOldN24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysOldAcChange | ORG | Total Admitted Accrued Mental Health Care Days for Older person Population - Acute Care Change | with New as (select * from OrgAdmiCDaysOldAcSum), Old as (select * from hist.OrgAdmiCDaysOldAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysOldAcGrowth | ORG | Total Admitted Accrued Mental Health Care Days for Older person Population - Acute Care Growth | with New as (select * from OrgAdmiCDaysOldAcSum), Old as (select * from hist.OrgAdmiCDaysOldAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysOldNAcChange | ORG | Total Admitted Accrued Mental Health Care Days for Older person Population - Non-acute Care Change | with New as (select * from OrgAdmiCDaysOldNAcSum), Old as (select * from hist.OrgAdmiCDaysOldNAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysOldNAcGrowth | ORG | Total Admitted Accrued Mental Health Care Days for Older person Population - Non-acute Care Growth | with New as (select * from OrgAdmiCDaysOldNAcSum), Old as (select * from hist.OrgAdmiCDaysOldNAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysFor24Change | ORG | Total Residential Accrued Mental Health Care Days for Forensic Population - 24hr staffed Change | with New as (select * from OrgResiCDaysFor24Sum), Old as (select * from hist.OrgResiCDaysFor24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysFor24Growth | ORG | Total Residential Accrued Mental Health Care Days for Forensic Population - 24hr staffed Growth | with New as (select * from OrgResiCDaysFor24Sum), Old as (select * from hist.OrgResiCDaysFor24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysForN24Change | ORG | Total Residential Accrued Mental Health Care Days for Forensic Population - non-24hr staffed Change | with New as (select * from OrgResiCDaysForN24Sum), Old as (select * from hist.OrgResiCDaysForN24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysForN24Growth | ORG | Total Residential Accrued Mental Health Care Days for Forensic Population - non-24hr staffed Growth | with New as (select * from OrgResiCDaysForN24Sum), Old as (select * from hist.OrgResiCDaysForN24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysForAcChange | ORG | Total Admitted Accrued Mental Health Care Days for Forensic Population - Acute Care Change | with New as (select * from OrgAdmiCDaysForAcSum), Old as (select * from hist.OrgAdmiCDaysForAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysForAcGrowth | ORG | Total Admitted Accrued Mental Health Care Days for Forensic Population - Acute Care Growth | with New as (select * from OrgAdmiCDaysForAcSum), Old as (select * from hist.OrgAdmiCDaysForAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysForNAcChange | ORG | Total Admitted Accrued Mental Health Care Days for Forensic Population - Non-acute Care Change | with New as (select * from OrgAdmiCDaysForNAcSum), Old as (select * from hist.OrgAdmiCDaysForNAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysForNAcGrowth | ORG | Total Admitted Accrued Mental Health Care Days for Forensic Population - Non-acute Care Growth | with New as (select * from OrgAdmiCDaysForNAcSum), Old as (select * from hist.OrgAdmiCDaysForNAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysGen24Change | ORG | Total Residential Accrued Mental Health Care Days for General Population - 24hr staffed Change | with New as (select * from OrgResiCDaysGen24Sum), Old as (select * from hist.OrgResiCDaysGen24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysGen24Growth | ORG | Total Residential Accrued Mental Health Care Days for General Population - 24hr staffed Growth | with New as (select * from OrgResiCDaysGen24Sum), Old as (select * from hist.OrgResiCDaysGen24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysGenN24Change | ORG | Total Residential Accrued Mental Health Care Days for General Population - non-24hr staffed Change | with New as (select * from OrgResiCDaysGenN24Sum), Old as (select * from hist.OrgResiCDaysGenN24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysGenN24Growth | ORG | Total Residential Accrued Mental Health Care Days for General Population - non-24hr staffed Growth | with New as (select * from OrgResiCDaysGenN24Sum), Old as (select * from hist.OrgResiCDaysGenN24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysGenAcChange | ORG | Total Admitted Accrued Mental Health Care Days for General Population - Acute Care Change | with New as (select * from OrgAdmiCDaysGenAcSum), Old as (select * from hist.OrgAdmiCDaysGenAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysGenAcGrowth | ORG | Total Admitted Accrued Mental Health Care Days for General Population - Acute Care Growth | with New as (select * from OrgAdmiCDaysGenAcSum), Old as (select * from hist.OrgAdmiCDaysGenAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysGenNAcChange | ORG | Total Admitted Accrued Mental Health Care Days for General Population - Non-acute Care Change | with New as (select * from OrgAdmiCDaysGenNAcSum), Old as (select * from hist.OrgAdmiCDaysGenNAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysGenNAcGrowth | ORG | Total Admitted Accrued Mental Health Care Days for General Population - Non-acute Care Growth | with New as (select * from OrgAdmiCDaysGenNAcSum), Old as (select * from hist.OrgAdmiCDaysGenNAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysYth24Change | ORG | Total Residential Accrued Mental Health Care Days for Youth Population - 24hr staffed Change | with New as (select * from OrgResiCDaysYth24Sum), Old as (select * from hist.OrgResiCDaysYth24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysYth24Growth | ORG | Total Residential Accrued Mental Health Care Days for Youth Population - 24hr staffed Growth | with New as (select * from OrgResiCDaysYth24Sum), Old as (select * from hist.OrgResiCDaysYth24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysYthN24Change | ORG | Total Residential Accrued Mental Health Care Days for Youth Population - non-24hr staffed Change | with New as (select * from OrgResiCDaysYthN24Sum), Old as (select * from hist.OrgResiCDaysYthN24Sum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgResiCDaysYthN24Growth | ORG | Total Residential Accrued Mental Health Care Days for Youth Population - non-24hr staffed Growth | with New as (select * from OrgResiCDaysYthN24Sum), Old as (select * from hist.OrgResiCDaysYthN24Sum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysYthAcChange | ORG | Total Admitted Accrued Mental Health Care Days for Youth Population - Acute Care Change | with New as (select * from OrgAdmiCDaysYthAcSum), Old as (select * from hist.OrgAdmiCDaysYthAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysYthAcGrowth | ORG | Total Admitted Accrued Mental Health Care Days for Youth Population - Acute Care Growth | with New as (select * from OrgAdmiCDaysYthAcSum), Old as (select * from hist.OrgAdmiCDaysYthAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysYthNAcChange | ORG | Total Admitted Accrued Mental Health Care Days for Youth Population - Non-acute Care Change | with New as (select * from OrgAdmiCDaysYthNAcSum), Old as (select * from hist.OrgAdmiCDaysYthNAcSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgAdmiCDaysYthNAcGrowth | ORG | Total Admitted Accrued Mental Health Care Days for Youth Population - Non-acute Care Growth | with New as (select * from OrgAdmiCDaysYthNAcSum), Old as (select * from hist.OrgAdmiCDaysYthNAcSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StFteAdminChange | ST | Total Administrative and Clerical FTE reported at Setting level Change | with New as (select * from StFteAdminSum), Old as (select * from hist.StFteAdminSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StFteAdminGrowth | ST | Total Administrative and Clerical FTE reported at Setting level Growth | with New as (select * from StFteAdminSum), Old as (select * from hist.StFteAdminSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| FteAdminChange | ORG | Total Administrative and Clerical FTE reported at Setting Level Change | with New as (select * from FteAdminSum), Old as (select * from hist.FteAdminSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| FteAdminGrowth | ORG | Total Administrative and Clerical FTE reported at Setting Level Growth | with New as (select * from FteAdminSum), Old as (select * from hist.FteAdminSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StFteCCWrkrChange | ST | Total Carer and Consumer Workers FTE reported at Setting level Change | with New as (select * from StFteCCWrkrSum), Old as (select * from hist.StFteCCWrkrSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StFteCCWrkrGrowth | ST | Total Carer and Consumer Workers FTE reported at Setting level Growth | with New as (select * from StFteCCWrkrSum), Old as (select * from hist.StFteCCWrkrSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| FteCCWrkrChange | ORG | Total Carer and Consumer Workers FTE reported at Setting Level Change | with New as (select * from FteCCWrkrSum), Old as (select * from hist.FteCCWrkrSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| FteCCWrkrGrowth | ORG | Total Carer and Consumer Workers FTE reported at Setting Level Growth | with New as (select * from FteCCWrkrSum), Old as (select * from hist.FteCCWrkrSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StFteDHPChange | ST | Total Diagnostic and Health Professionals FTE reported at Setting level Change | with New as (select * from StFteDHPSum), Old as (select * from hist.StFteDHPSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StFteDHPGrowth | ST | Total Diagnostic and Health Professionals FTE reported at Setting level Growth | with New as (select * from StFteDHPSum), Old as (select * from hist.StFteDHPSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| FteDHPChange | ORG | Total Diagnostic and Health Professionals FTE reported at Setting Level Change | with New as (select * from FteDHPSum), Old as (select * from hist.FteDHPSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| FteDHPGrowth | ORG | Total Diagnostic and Health Professionals FTE reported at Setting Level Growth | with New as (select * from FteDHPSum), Old as (select * from hist.FteDHPSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StFteDomestChange | ST | Total Domestic FTE reported at Setting level Change | with New as (select * from StFteDomestSum), Old as (select * from hist.StFteDomestSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StFteDomestGrowth | ST | Total Domestic FTE reported at Setting level Growth | with New as (select * from StFteDomestSum), Old as (select * from hist.StFteDomestSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| FteDomestChange | ORG | Total Domestic FTE reported at Setting Level Change | with New as (select * from FteDomestSum), Old as (select * from hist.FteDomestSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| FteDomestGrowth | ORG | Total Domestic FTE reported at Setting Level Growth | with New as (select * from FteDomestSum), Old as (select * from hist.FteDomestSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StFteMedChange | ST | Total Medical FTE reported at Setting level Change | with New as (select * from StFteMedSum), Old as (select * from hist.StFteMedSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StFteMedGrowth | ST | Total Medical FTE reported at Setting level Growth | with New as (select * from StFteMedSum), Old as (select * from hist.StFteMedSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| FteMedChange | ORG | Total Medical FTE reported at Setting Level Change | with New as (select * from FteMedSum), Old as (select * from hist.FteMedSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| FteMedGrowth | ORG | Total Medical FTE reported at Setting Level Growth | with New as (select * from FteMedSum), Old as (select * from hist.FteMedSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StFteNursesChange | ST | Total Nursing FTE reported at Setting level Change | with New as (select * from StFteNursesSum), Old as (select * from hist.StFteNursesSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StFteNursesGrowth | ST | Total Nursing FTE reported at Setting level Growth | with New as (select * from StFteNursesSum), Old as (select * from hist.StFteNursesSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| FteNursesChange | ORG | Total Nursing FTE reported at Setting Level Change | with New as (select * from FteNursesSum), Old as (select * from hist.FteNursesSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| FteNursesGrowth | ORG | Total Nursing FTE reported at Setting Level Growth | with New as (select * from FteNursesSum), Old as (select * from hist.FteNursesSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StFtePCareChange | ST | Total Other Personal Care FTE reported at Setting level Change | with New as (select * from StFtePCareSum), Old as (select * from hist.StFtePCareSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StFtePCareGrowth | ST | Total Other Personal Care FTE reported at Setting level Growth | with New as (select * from StFtePCareSum), Old as (select * from hist.StFtePCareSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| FtePCareChange | ORG | Total Other Personal Care FTE reported at Setting Level Change | with New as (select * from FtePCareSum), Old as (select * from hist.FtePCareSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| FtePCareGrowth | ORG | Total Other Personal Care FTE reported at Setting Level Growth | with New as (select * from FtePCareSum), Old as (select * from hist.FtePCareSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StFteChange | ST | Total FTE reported at Setting level Change | with New as (select * from StFteSum), Old as (select * from hist.StFteSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StFteGrowth | ST | Total FTE reported at Setting level Growth | with New as (select * from StFteSum), Old as (select * from hist.StFteSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgFteChange | ORG | Total FTE reported at Setting level Change | with New as (select * from OrgFteSum), Old as (select * from hist.OrgFteSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgFteGrowth | ORG | Total FTE reported at Setting level Growth | with New as (select * from OrgFteSum), Old as (select * from hist.OrgFteSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StOrgFteAdminChange | ST | State Total Administrative and Clerical FTE reported at Organisation Level Change | with New as (select * from StOrgFteAdminSum), Old as (select * from hist.StOrgFteAdminSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteAdminGrowth | ST | State Total Administrative and Clerical FTE reported at Organisation Level Growth | with New as (select * from StOrgFteAdminSum), Old as (select * from hist.StOrgFteAdminSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteCarerWrkrChange | ST | State Total Carer Workers FTE reported at Organisation Level Change | with New as (select * from StOrgFteCarerWrkrSum), Old as (select * from hist.StOrgFteCarerWrkrSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteCarerWrkrGrowth | ST | State Total Carer Workers FTE reported at Organisation Level Growth | with New as (select * from StOrgFteCarerWrkrSum), Old as (select * from hist.StOrgFteCarerWrkrSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteCCWrkrChange | ST | State Total Carer and Consumer Workers FTE reported at Organisation Level Change | with New as (select * from StOrgFteCCWrkrSum), Old as (select * from hist.StOrgFteCCWrkrSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteCCWrkrGrowth | ST | State Total Carer and Consumer Workers FTE reported at Organisation Level Growth | with New as (select * from StOrgFteCCWrkrSum), Old as (select * from hist.StOrgFteCCWrkrSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteCnsltPsychChange | ST | State Total Consultant Psychiatrists and Psychiatrists FTE reported at Organisation Level Change | with New as (select * from StOrgFteCnsltPsychSum), Old as (select * from hist.StOrgFteCnsltPsychSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteCnsltPsychGrowth | ST | State Total Consultant Psychiatrists and Psychiatrists FTE reported at Organisation Level Growth | with New as (select * from StOrgFteCnsltPsychSum), Old as (select * from hist.StOrgFteCnsltPsychSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteConsrWrkrChange | ST | State Total Consumer Workers FTE reported at Organisation Level Change | with New as (select * from StOrgFteConsrWrkrSum), Old as (select * from hist.StOrgFteConsrWrkrSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteConsrWrkrGrowth | ST | State Total Consumer Workers FTE reported at Organisation Level Growth | with New as (select * from StOrgFteConsrWrkrSum), Old as (select * from hist.StOrgFteConsrWrkrSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteDHPChange | ST | State Total Diagnostic and Health Professionals FTE reported at Organisation Level Change | with New as (select * from StOrgFteDHPSum), Old as (select * from hist.StOrgFteDHPSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteDHPGrowth | ST | State Total Diagnostic and Health Professionals FTE reported at Organisation Level Growth | with New as (select * from StOrgFteDHPSum), Old as (select * from hist.StOrgFteDHPSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteDHPOtherChange | ST | State Total Other Diagnostic and Health Professionals FTE reported at Organisation Level Change | with New as (select * from StOrgFteDHPOtherSum), Old as (select * from hist.StOrgFteDHPOtherSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteDHPOtherGrowth | ST | State Total Other Diagnostic and Health Professionals FTE reported at Organisation Level Growth | with New as (select * from StOrgFteDHPOtherSum), Old as (select * from hist.StOrgFteDHPOtherSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteDomestChange | ST | State Total Domestic FTE reported at Organisation Level Change | with New as (select * from StOrgFteDomestSum), Old as (select * from hist.StOrgFteDomestSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteDomestGrowth | ST | State Total Domestic FTE reported at Organisation Level Growth | with New as (select * from StOrgFteDomestSum), Old as (select * from hist.StOrgFteDomestSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteMedChange | ST | State Total Medical FTE reported at Organisation Level Change | with New as (select * from StOrgFteMedSum), Old as (select * from hist.StOrgFteMedSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteMedGrowth | ST | State Total Medical FTE reported at Organisation Level Growth | with New as (select * from StOrgFteMedSum), Old as (select * from hist.StOrgFteMedSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteMedOtherChange | ST | State Total Other Medical Officers FTE reported at Organisation Level Change | with New as (select * from StOrgFteMedOtherSum), Old as (select * from hist.StOrgFteMedOtherSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteMedOtherGrowth | ST | State Total Other Medical Officers FTE reported at Organisation Level Growth | with New as (select * from StOrgFteMedOtherSum), Old as (select * from hist.StOrgFteMedOtherSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteNursesChange | ST | State Total Nursing FTE reported at Organisation Level Change | with New as (select * from StOrgFteNursesSum), Old as (select * from hist.StOrgFteNursesSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteNursesGrowth | ST | State Total Nursing FTE reported at Organisation Level Growth | with New as (select * from StOrgFteNursesSum), Old as (select * from hist.StOrgFteNursesSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteNursesEnrlChange | ST | State Total Enrolled Nurses FTE reported at Organisation Level Change | with New as (select * from StOrgFteNursesEnrlSum), Old as (select * from hist.StOrgFteNursesEnrlSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteNursesEnrlGrowth | ST | State Total Enrolled Nurses FTE reported at Organisation Level Growth | with New as (select * from StOrgFteNursesEnrlSum), Old as (select * from hist.StOrgFteNursesEnrlSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteNursesRegChange | ST | State Total Registered Nurses FTE reported at Organisation Level Change | with New as (select * from StOrgFteNursesRegSum), Old as (select * from hist.StOrgFteNursesRegSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteNursesRegGrowth | ST | State Total Registered Nurses FTE reported at Organisation Level Growth | with New as (select * from StOrgFteNursesRegSum), Old as (select * from hist.StOrgFteNursesRegSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteOTChange | ST | State Total Occupational Therapists FTE reported at Organisation Level Change | with New as (select * from StOrgFteOTSum), Old as (select * from hist.StOrgFteOTSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteOTGrowth | ST | State Total Occupational Therapists FTE reported at Organisation Level Growth | with New as (select * from StOrgFteOTSum), Old as (select * from hist.StOrgFteOTSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFtePCareChange | ST | State Total Other Personal Care FTE reported at Organisation Level Change | with New as (select * from StOrgFtePCareSum), Old as (select * from hist.StOrgFtePCareSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFtePCareGrowth | ST | State Total Other Personal Care FTE reported at Organisation Level Growth | with New as (select * from StOrgFtePCareSum), Old as (select * from hist.StOrgFtePCareSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFtePsycholChange | ST | State Total Psychologists FTE reported at Organisation Level Change | with New as (select * from StOrgFtePsycholSum), Old as (select * from hist.StOrgFtePsycholSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFtePsycholGrowth | ST | State Total Psychologists FTE reported at Organisation Level Growth | with New as (select * from StOrgFtePsycholSum), Old as (select * from hist.StOrgFtePsycholSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFtePsyRegChange | ST | State Total Psychiatry Registrars and Trainees FTE reported at Organisation Level Change | with New as (select * from StOrgFtePsyRegSum), Old as (select * from hist.StOrgFtePsyRegSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFtePsyRegGrowth | ST | State Total Psychiatry Registrars and Trainees FTE reported at Organisation Level Growth | with New as (select * from StOrgFtePsyRegSum), Old as (select * from hist.StOrgFtePsyRegSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteSocialWkChange | ST | State Total Social Workers FTE reported at Organisation Level Change | with New as (select * from StOrgFteSocialWkSum), Old as (select * from hist.StOrgFteSocialWkSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteSocialWkGrowth | ST | State Total Social Workers FTE reported at Organisation Level Growth | with New as (select * from StOrgFteSocialWkSum), Old as (select * from hist.StOrgFteSocialWkSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| StOrgFteAllChange | ST | State Total All Staff FTE reported at Organisation Level Change | with New as (select * from StOrgFteAllSum), Old as (select * from hist.StOrgFteAllSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteAllGrowth | ST | State Total All Staff FTE reported at Organisation Level Growth | with New as (select * from StOrgFteAllSum), Old as (select * from hist.StOrgFteAllSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgFteAllChange | ORG | Total All Staff FTE reported at Organisation Level Change | with New as (select * from OrgFteAllSum), Old as (select * from hist.OrgFteAllSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgFteAllGrowth | ORG | Total All Staff FTE reported at Organisation Level Growth | with New as (select * from OrgFteAllSum), Old as (select * from hist.OrgFteAllSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StOrgFteDCareChange | ST | State Total Direct Care FTE reported at Organisation Level Change | with New as (select * from StOrgFteDCareSum), Old as (select * from hist.StOrgFteDCareSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteDCareGrowth | ST | State Total Direct Care FTE reported at Organisation Level Growth | with New as (select * from StOrgFteDCareSum), Old as (select * from hist.StOrgFteDCareSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgFteDCareChange | ORG | Total Direct Care FTE reported at Organisation Level Change | with New as (select * from OrgFteDCareSum), Old as (select * from hist.OrgFteDCareSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgFteDCareGrowth | ORG | Total Direct Care FTE reported at Organisation Level Growth | with New as (select * from OrgFteDCareSum), Old as (select * from hist.OrgFteDCareSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StOrgFteNonCareChange | ST | State Total Non-Direct Care FTE reported at Organisation Level Change | with New as (select * from StOrgFteNonCareSum), Old as (select * from hist.StOrgFteNonCareSum) select State, New.Total - Old.Total as Change from New join Old using (State) |
| StOrgFteNonCareGrowth | ST | State Total Non-Direct Care FTE reported at Organisation Level Growth | with New as (select * from StOrgFteNonCareSum), Old as (select * from hist.StOrgFteNonCareSum) select State, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State) |
| OrgFteNonCareChange | ORG | Total Non-Direct Care FTE reported at Organisation Level Change | with New as (select * from OrgFteNonCareSum), Old as (select * from hist.OrgFteNonCareSum) select State, RegId, OrgId, New.Total - Old.Total as Change from New join Old using (State, RegId, OrgId) |
| OrgFteNonCareGrowth | ORG | Total Non-Direct Care FTE reported at Organisation Level Growth | with New as (select * from OrgFteNonCareSum), Old as (select * from hist.OrgFteNonCareSum) select State, RegId, OrgId, sd_div_safe(New.Total - Old.Total, Old.Total, 3) as Growth from New join Old using (State, RegId, OrgId) |
| StStdsReviewProp | ST | Proportion of National Standards for Mental Health Services Review Status codes that equal 1 | with admi_tot as ( select State, count(*) as val from ADMI where StdsReviewSt = '1' group by State ), ambu_tot as ( select State, count(*) as val from AMBU where StdsReviewSt = '1' group by State ), resi_tot as ( select State, count(*) as val from RESI where StdsReviewSt = '1' group by State ), unit_count as ( SELECT State, count(*) AS val FROM UnitUnion GROUP BY state ) select State, sd_div_safe( coalesce(ambu_tot.val,0) + coalesce(resi_tot.val,0) + coalesce(admi_tot.val,0), unit_count.val, 3) AS value from ST LEFT JOIN admi_tot using (State) LEFT JOIN ambu_tot using (State) LEFT JOIN resi_tot using (State) LEFT JOIN unit_count USING (State) WHERE unit_count.val > 0 |
| StNHousePlacesGrowthAmt | ST | Absolute percentage that Public Supported Housing Places changed since previous year. | select State, prev.NHousePlaces as previous_amount, ST.NHousePlaces as current_amount, sd_div(100 * abs(prev.NHousePlaces - ST.NHousePlaces), greatest(prev.nhouseplaces, ST.nhouseplaces), 1) as percent_change from main.ST join hist.ST prev using (State) where prev.nhouseplaces > 0 or ST.nhouseplaces > 0 |