VW_HCHB_Rollup_Monthly

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

reporting_month

date

null

soc_asof_date

date

√

null

branch_groupname

varchar

200

null

Ref_Signature_BranchGroup.branch_groupname

Implied ConstraintR

service_line

varchar

200

null

branch_code

varchar

null

most_recent_census

int

√

null

avg_daily_census

int

√

null

soc_total

int

√

null

soc_medicare

int

√

null

soc_episodic

int

√

null

soc_ava_ref

int

√

null

soc_recert

int

√

null

soc_budget

int

√

null

nonadmit_ava_ref

int

√

null

nonadmit_total

int

√

null

bg_sortorder

int

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW dbo.VW_HCHB_Rollup_Monthly AS
    WITH soc AS (SELECT reporting_month,
                        max(census_asof_date) AS max_census_asof_date,
                        max(soc_asof_date)    AS soc_asof_date,
                        branch_groupname,
                        service_line,
                        branch_code,
                        sum(soc_total)        AS soc_total,
                        sum(soc_medicare)     AS soc_medicare,
                        sum(soc_episodic)     AS soc_episodic,
                        sum(soc_ava_ref)      AS soc_ava_ref,
                        sum(soc_recert)       AS soc_recert,
                        sum(soc_budget)       AS soc_budget,
                        avg(census_total)     AS avg_daily_census,
                        sum(nonadmit_ava_ref) AS nonadmit_ava_ref,
                        sum(nonadmit_total)   AS nonadmit_total,
                        bg_sortorder
                 FROM dbo.HCHB_Rollup_Daily
                 GROUP BY reporting_month, branch_groupname, service_line, branch_code, bg_sortorder)

    SELECT soc.reporting_month,
           soc.soc_asof_date,
           soc.branch_groupname,
           soc.service_line,
           soc.branch_code,
           census.census_total AS most_recent_census,
           soc.avg_daily_census,
           soc.soc_total,
           soc.soc_medicare,
           soc.soc_episodic,
           soc.soc_ava_ref,
           soc.soc_recert,
           soc.soc_budget,
           soc.nonadmit_ava_ref,
           soc.nonadmit_total,
           soc.bg_sortorder
    FROM soc
         /* The outer join allows examination of soc (starts) data prior to September 2019, when we first
            started collecting census snapshots. */
         LEFT JOIN dbo.HCHB_Rollup_Daily AS census
                   ON soc.reporting_month = census.reporting_month
                       AND soc.max_census_asof_date = census.census_asof_date
                       AND soc.branch_code = census.branch_code
    WHERE soc.branch_code IN (SELECT branch_code FROM dbo.Ref_Signature_Branch)

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views