VW_HCHB_VolHoursBasis

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

reporting_month

date

√

null

service_date

date

√

null

branch_code

char

√

null

branch_groupname

varchar

200

null

Ref_Signature_BranchGroup.branch_groupname

Implied ConstraintR

branch_serviceline

varchar

200

null

volunteer_hours

numeric

23,7

√

null

visit_hours

numeric

40,6

null

nonvisit_hours

numeric

40,6

null

vacation_hours

numeric

40,6

null

sick_hours

numeric

40,6

null

holiday_hours

numeric

40,6

null

training_hours

numeric

40,6

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW dbo.VW_HCHB_VolHoursBasis AS

/*********************************************************************
Database:		DataCStore
View:			dbo.VW_HCHB_VolHoursBasis
Created:		6/15/2020
Description:	Consolidates worker hours and volunteer hours by
                branch, by day, which enables calculation of
                volunteer hour percentage. See VW_HCHB_HoursProd for
                CASE logic to identify different types of employee hours.
                NOTE: Volunteer hours as hospice only.
*********************************************************************/

    WITH vol AS (
        SELECT vt.vt_servicedate,
               vt.vt_branchcode,
               SUM(CASE WHEN vt.vt_starttime <= vt.vt_endtime THEN DATEDIFF(MI, vt.vt_starttime, vt.vt_endtime)
                    ELSE DATEDIFF(MI, vt.vt_starttime, DATEADD(DD, 1, vt.vt_endtime)) END) AS vol_minutes
        FROM dbo.HCHB_VOLUNTEER_TIME AS vt
        WHERE vt.vt_active = 'Y'
        GROUP BY vt.vt_servicedate,
                 vt.vt_branchcode),

         emp AS (
             SELECT hp.CEV_DATE_or_NVT_service_date                                                                AS servicedate,
                    hp.epi_branchcode,
                    SUM(CASE
                            WHEN hp.ptg_description NOT IN
                                 ('VACATION', 'SICK TIME', 'HOLIDAY', 'PV HOLIDAY', 'EDUCATION & TRAINING') AND
                                 hp.pc_desc = 'VISIT' THEN hp.hours_worked
                            ELSE 0 END)                                                                            AS visit_hours,
                    SUM(CASE
                            WHEN hp.ptg_description NOT IN
                                 ('VACATION', 'SICK TIME', 'HOLIDAY', 'PV HOLIDAY', 'EDUCATION & TRAINING') AND
                                 hp.pc_desc = 'TIME & NON VISIT ACTIVITY' THEN hp.hours_worked
                            ELSE 0 END)                                                                            AS nonvisit_hours,
                    SUM(CASE
                            WHEN hp.ptg_description IN ('VACATION') THEN hp.hours_worked
                            ELSE 0 END)                                                                            AS vacation_hours,
                    SUM(CASE
                            WHEN hp.ptg_description IN ('SICK TIME') THEN hp.hours_worked
                            ELSE 0 END)                                                                            AS sick_hours,
                    SUM(CASE
                            WHEN hp.ptg_description IN ('HOLIDAY', 'PV HOLIDAY') THEN hp.hours_worked
                            ELSE 0 END)                                                                            AS holiday_hours,
                    SUM(CASE
                            WHEN hp.ptg_description = 'EDUCATION & TRAINING' THEN hp.hours_worked
                            ELSE 0 END)                                                                            AS training_hours
             FROM dbo.HCHB_HoursProd AS hp
             GROUP BY hp.CEV_DATE_or_NVT_service_date,
                      hp.epi_branchcode
         ),

         synth AS (
            /* FULL OUTER JOIN means NULL values are possible on either side */
            SELECT COALESCE(emp.servicedate, vol.vt_servicedate)   AS service_date,
                   COALESCE(emp.epi_branchcode, vol.vt_branchcode) AS branch_code,
                   ISNULL(vol.vol_minutes, 0)                      AS volunteer_minutes,
                   ISNULL(vol.vol_minutes, 0) / 60.0000            AS volunteer_hours,
                   ISNULL(emp.visit_hours, 0)                      AS visit_hours,
                   ISNULL(emp.nonvisit_hours, 0)                   AS nonvisit_hours,
                   ISNULL(emp.vacation_hours, 0)                   AS vacation_hours,
                   ISNULL(emp.sick_hours, 0)                       AS sick_hours,
                   ISNULL(emp.holiday_hours, 0)                    AS holiday_hours,
                   ISNULL(emp.training_hours, 0)                   AS training_hours
            FROM vol
            FULL OUTER JOIN emp
            ON vol.vt_servicedate = emp.servicedate
                AND vol.vt_branchcode = emp.epi_branchcode)

    SELECT DATEFROMPARTS(YEAR(service_date), MONTH(service_date), 1) AS reporting_month,
           CAST(synth.service_date AS DATE) AS service_date,
           synth.branch_code,
           sb.branch_groupname,
           sb.branch_serviceline,
--            synth.volunteer_minutes,
           synth.volunteer_hours,
           synth.visit_hours,
           synth.nonvisit_hours,
           synth.vacation_hours,
           synth.sick_hours,
           synth.holiday_hours,
           synth.training_hours
    FROM synth
    JOIN dbo.Ref_Signature_Branch AS sb
        ON synth.branch_code = sb.branch_code
    WHERE 1=1
        AND sb.branch_serviceline = 'Hospice'
        AND sb.branch_fromdate <= synth.service_date
        AND ISNULL(sb.branch_thrudate, '2199-12-31') >= synth.service_date

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views