Published using Google Docs
CAEH Monthly Inflow/Outflow Report Guide
Updated automatically every 5 minutes

Report Guide

CAEH Monthly
Inflow/Outflow Report

Introduction

This report is intended for communities who are using HIFIS version 4.0.59.1 to be able to easily extract and report on monthly data points directly from HIFIS.

The report relies heavily on a new feature termed “Client History Changes” which is not a feature that users will often come across. On the Client Vitals screen of HIFIS there is a button called “Display Vitals History” which contains a record of every time a field value on the Vitals screen changed along with a timestamp corresponding to the change.

As of version 4.0.59.1, it now includes “Client State” changes, which contains a record of when the client becomes Active, Inactive, Archived, or Deceased.

Also new is the “Client Housing Status” on this screen, which contains a record of when the client becomes Homeless, Chronically Homeless, Housed, Transitional, or Unknown.


Each client always has a current Client Status and a current Housing Status. Both of these are displayed in the Client “Tombstone” on the left of the client file. Therefore, a single client could be in any of the following boxes at a given time:

Active

Deceased

Inactive

Archived

Homeless

Active + homeless

Deceased + homeless

Inactive + homeless

Archived + homeless

Chronically Homeless

Active + chronically homeless

Deceased + chronically homeless

Inactive + chronically homeless

Archived + chronically homeless

Housed

Active + housed

Deceased + housed

Inactive + housed

Archived + housed

Transitional

Active + transitional

Deceased + transitional

Inactive + transitional

Archived + transitional

Unknown

Active + unknown

Deceased + unknown

Inactive + unknown

Archived + unknown

The report identifies transitions from one box to another. For every transition, it assesses to see if the transition is relevant for the purposes of your monthly inflow/outflow reporting.

For example, a client who is actively chronically homeless but doesn’t have any interactions within HIFIS for a period of time will have their Client Status change from Active to Inactive, moving them from the Active + Chronically Homeless box to the Inactive + Chronically Homeless box. This transition is a relevant one and will be captured in the report.

However, if a client who is living in supportive housing passes away, they would move from the Active + Housed box to the Deceased + Housed box. This transition is irrelevant, and will not be captured in this report.

HIFIS Version

This report requires HIFIS version 4.0.59.1 or later to run.


Source Data

Data in this report is pulled from the Client History Changes table (see Introduction). Changes in the client’s Housing Status are informed by the client’s Housing History and Admissions. Changes in the client’s State are informed by the services a client is receiving.

Modules and Fields

Data is retrieved from the following modules and fields in HIFIS:

Module
Fields

Clients

  • ClientID
  • ClusterID
  • Client State
  • Vitals History
  • Date of Birth
  • Gender
  • Veteran Status
  • Indigenous Status

Tables Referenced

The following database tables are used in this report:

Tables
  • HIFIS_Clients
  • HIFIS_People
  • HIFIS_ClientHistoryChanges
  • HIFIS_GenderTypes
  • HIFIS_AboriginalIndicatorTypes
  • HIFIS_VeteranStateTypes

Inclusion Criteria

The report includes data for the current cluster and for the month surrounding a user-selected date.

Parameters

The report prompts for the following parameters:

Prompt Text
Parameter Name
Notes

N/A (this is a hidden parameter)

{?CurrentOrganizationID}

  • Automatically selects the current service provider that the user is logged in at

Enter Report Month (choose any date in the reporting month):

{?ReportDate}

  • The report will run for the reporting month

Filtering

Clients are included if they meet the following criteria:

Tables

{?ReportDate} BETWEEN t.DateOfChange AND t.StatusEndDate -- on the user-selected date, i.e. the last day of the month, if they were:

AND t.HousingStateID = 3 -- Chronically homeless

AND t.ClientStateID = 1 -- Active

OR

t.StatusEndDate BETWEEN @DateStart AND {?ReportDate} -- they transitioned out of the chronic and active box sometime in the reporting month

AND t.HousingStateID = 3 -- Chronically homeless

AND t.ClientStateID = 1 -- Active

Calculations

Formulas

The following formulas are used in this report:

Formula Name
Calculation
Description

Aged In

a.HousingStateID = 3

        AND a.DateOfChange BETWEEN @DateStart AND @DateEnd

        AND t.CreatedDate NOT BETWEEN @DateStart AND @DateEnd

        AND a.RN = 1

        AND a.ClientStateID = 1

        AND a.ChangeTypeID = 1

The client was active and chronically homeless at the end of the month, AND they became active and chronically homeless in the reporting month, AND the client file wasn’t created in the reporting month, AND it is the client’s first time being chronically homeless.

Newly Identified

a.HousingStateID = 3

        AND t.CreatedDate BETWEEN @DateStart AND @DateEnd

        AND a.ClientStateID = 1

The client was active and chronically homeless at the end of the month, AND the client file was created in the reporting month.

Returned from Housing

DateOfChange BETWEEN @DateStart AND @DateEnd

        AND RN > 1

        AND ChangeTypeID = 1

        AND HousingStateID = 3

        AND PriorStateID = 1

        AND ClientStateID = 1

The client was active and chronically homeless at the end of the month, AND they experienced a transition from housed to chronically homeless and active during the reporting month, AND it was not their first time being chronically homeless.

Returned from Inactive

DateOfChange BETWEEN @DateStart AND @DateEnd

        AND RN > 1

        AND ChangeTypeID = 2

        AND HousingStateID = 3

        AND PriorStateID = 3

        AND ClientStateID = 1

The client was active and chronically homeless at the end of the month, AND they experienced a transition from inactive to chronically homeless and active during the reporting month, AND it was not their first time being chronically homeless.

Moved to Housing

DateOfChange BETWEEN @DateStart AND @DateEnd

        AND ChangeTypeID = 1

        AND HousingStateID = 1

        AND PriorStateID = 3

        AND ClientStateID = 1

The client was chronically homeless and active at some point during the reporting month, but had a transition out of that box to housing sometime that month.

Moved to Inactive

DateOfChange BETWEEN @DateStart AND @DateEnd

        AND ChangeTypeID = 2

        AND HousingStateID = 3

        AND PriorStateID = 1

        AND ClientStateID = 3

The client was chronically homeless and active at some point during the reporting month, but had a transition out of that box to inactive sometime that month.


Full Query

The following is the full SQL query used by this report:

Query

DECLARE @DateStart AS date = DATEFROMPARTS(year({?ReportDate}),month({?ReportDate}),1),

        @DateEnd AS date = DATEADD(d,-1,DATEFROMPARTS(year({?ReportDate}),month(DATEADD(m, 1, {?ReportDate})),1));

DECLARE @ClusterID AS smallint

SELECT @ClusterID = o.ClusterID

        FROM HIFIS_Organizations o

        WHERE o.OrganizationID = {?CurrentOrganizationID};

DECLARE @ClusterName AS nvarchar(50)

SELECT @ClusterName = HIFIS_Cluster.Name

        FROM HIFIS_Cluster

        WHERE HIFIS_Cluster.ClusterID = @ClusterID;

DECLARE @chc TABLE (

        ClientID int,

        DateOfChange date,

        FieldTypeID int,

        [Value] int,

        PrevValue int

)

INSERT INTO @chc -- We are creating a version of HIFIS_ClientHistoryChanges that only includes relevant and cleaned data

SELECT *

FROM

        (SELECT ClientID,

                DateOfChange,

                FieldTypeID,

                [Value],

                LAG([Value],1) OVER (PARTITION BY ClientID, FieldTypeID ORDER BY DateOfChange ASC) AS PrevValue -- What's the change from?        

        FROM

                (SELECT chc.ClientID,

                        CONVERT(date,chc.DateOfChange) "DateOfChange",

                        chc.FieldTypeID,

                        p.ClusterID,

                        CASE WHEN FieldTypeID = 9 AND chc.[Value] IN (2,3,4) THEN 3 ELSE chc.[Value] END AS [Value] -- Treat Deceased and Archived as Inactive

                FROM HIFIS_ClientHistoryChanges chc

                        INNER JOIN HIFIS_Clients c ON chc.ClientID = c.ClientID

                        INNER JOIN HIFIS_People p ON c.PersonID = p.PersonID AND p.ClusterID = @ClusterID -- Filter by Cluster

                WHERE chc.FieldTypeID IN (9,17)

                                -- 9 = Activity

                                -- 17 = Housing

                        AND NOT (FieldTypeID = 17 AND Value IN (4,5)) -- Ignore unknown and transitional housing statuses

                                -- 4 = unknown

                                -- 5 = transitional

                ) A

        ) B

WHERE [Value] <> ISNULL(PrevValue,0) -- Ignore transitions that aren't transitions (data errors) like when a client becomes chronic twice in a row

;

DECLARE @AllChanges TABLE (

        ClientID bigint,

        StatusStartDate date,

        StatusEndDate date,

        ChangeTypeID smallint,

        ClientStateID smallint,

        HousingStateID smallint,

        PriorStateID smallint

);

INSERT INTO @AllChanges -- We're creating a new table that combines all transitions

-- Activity Changes

SELECT ClientID,

        DateOfChange,

        StatusEndDate,

        2 AS "ChangeTypeID",

        ClientStateID,

        (SELECT TOP 1 [Value] FROM @chc h

                WHERE h.DateOfChange <= a.DateOfChange

                        AND a.ClientID = h.ClientID

                        AND h.FieldTypeID = 17 -- 17 = Housing

                ORDER BY h.DateOfChange DESC) AS "HousingStateID",

        PrevValue

FROM

        (SELECT ClientID,

                DateOfChange,

                ISNULL(LEAD (DateOfChange,1) OVER (PARTITION BY ClientID ORDER BY DateOfChange ASC), @DateEnd) AS "StatusEndDate",

                [Value] AS ClientStateID,

                PrevValue

        FROM @chc chc

        WHERE FieldTypeID = 9 -- 9 = Activity

        ) a

WHERE a.StatusEndDate >= @DateStart

        AND a.DateOfChange <= @DateEnd

UNION ALL

-- Housing Changes

SELECT ClientID,

        DateOfChange,

        StatusEndDate,

        1 AS "ChangeTypeID",

        (SELECT TOP 1 [Value] FROM @chc a

                WHERE a.DateOfChange <= h.DateOfChange

                        AND a.ClientID = h.ClientID

                        AND a.FieldTypeID = 9

                ORDER BY a.DateOfChange DESC) AS "ClientStateID",

        HousingStateID,

        PrevValue

FROM

        (

                SELECT ClientID,

                        DateOfChange,

                        [Value] AS HousingStateID,

                        ISNULL(LEAD (DateOfChange,1) OVER (PARTITION BY ClientID ORDER BY DateOfChange ASC), @DateEnd) AS "StatusEndDate",

                        PrevValue

                FROM @chc chc

                WHERE FieldTypeID = 17 /* 9 = State and 17 = Housing */

        ) h

WHERE StatusEndDate >= @DateStart

        AND DateOfChange <= @DateEnd

;

-- SELECT * FROM @AllChanges;

--WITH DateFirstHomeless AS (

--        SELECT

--                ClientID,

--                MIN(chc.DateOfChange) AS "DateStart"

--        FROM @chc chc

--        WHERE FieldTypeID = 17

--                AND [Value] = 2

--        GROUP BY ClientID

--),

WITH DateFirstChronic AS (

        SELECT

                ClientID,

                MIN(chc.DateOfChange) AS "DateStart"

        FROM @chc chc

        WHERE FieldTypeID = 17

                AND [Value] = 3

        GROUP BY ClientID

),

DateFirstIdentified AS (

        SELECT

                ClientID,

                MIN(StatusStartDate) "DateStart"

        FROM @AllChanges

        WHERE HousingStateID = 3

                AND ClientStateID = 1

        GROUP BY ClientID, HousingStateID, ClientStateID

),

ChronicPool AS (SELECT

        t.ClientID

FROM @AllChanges t

WHERE @DateEnd BETWEEN t.StatusStartDate AND t.StatusEndDate

        AND t.HousingStateID = 3

        AND t.ClientStateID = 1

),

Removed AS (SELECT

        t.ClientID

FROM @AllChanges t

WHERE t.StatusEndDate BETWEEN @DateStart AND @DateEnd

        AND t.HousingStateID = 3

        AND t.ClientStateID = 1

),

Main AS (

-- All Chronically Homeless

SELECT

        t.ClientID,

        'Currently Homeless' AS "Category",

        @DateEnd AS "TransitionDate"

FROM ChronicPool t

UNION ALL

-- Aged In

SELECT

        t.ClientID,

        'Aged In' AS "Category",

        ch.DateStart AS "TransitionDate"

FROM ChronicPool t

        INNER JOIN DateFirstChronic ch ON t.ClientID = ch.ClientID

        INNER JOIN DateFirstIdentified n ON n.ClientID = ch.ClientID and n.DateStart = ch.DateStart

WHERE ch.DateStart BETWEEN @DateStart AND @DateEnd -- First time client became chronic is in the report range

        AND n.DateStart = ch.DateStart -- First time client was identified as chronic is also first time they became chronically homeless

UNION ALL

-- Newly Identified

SELECT

        t.ClientID,

        'Newly Identified' AS "Category",

        n.DateStart AS "TransitionDate"

FROM DateFirstIdentified n

        INNER JOIN ChronicPool t ON n.ClientID = t.ClientID

        INNER JOIN DateFirstChronic ch ON n.ClientID = ch.ClientID

WHERE n.DateStart BETWEEN @DateStart AND @DateEnd -- First time client was identified is in the report range

        AND n.DateStart > ch.DateStart -- First time client was identified as chronic was after the first time they became chronically homeless

UNION ALL

-- Returned from Housing

SELECT

        t.ClientID,

        'Returned from Housing' AS "Category",

        a.StatusStartDate AS "TransitionDate"

FROM @AllChanges a

        INNER JOIN ChronicPool t ON a.ClientID = t.ClientID

        INNER JOIN DateFirstChronic ch ON a.ClientID = ch.ClientID

WHERE StatusStartDate BETWEEN @DateStart AND @DateEnd

        AND a.StatusStartDate > ch.DateStart

        AND ChangeTypeID = 1

        AND HousingStateID = 3

        AND PriorStateID = 1

        AND ClientStateID = 1

UNION ALL

-- Returned from Inactive

SELECT

        t.ClientID,

        'Returned from Inactive' AS "Category",

        a.StatusStartDate AS "TransitionDate"

FROM @AllChanges a

        INNER JOIN ChronicPool t ON a.ClientID = t.ClientID

        INNER JOIN DateFirstChronic ch ON a.ClientID = ch.ClientID

WHERE StatusStartDate BETWEEN @DateStart AND @DateEnd  -- The transition occurred sometime in the report range

        AND a.StatusStartDate > ch.DateStart -- The transition was sometime after the first time they became chronic, so it's a return

        AND ChangeTypeID = 2 -- Activity change

        AND HousingStateID = 3 -- Chronic

        AND PriorStateID = 3 -- Inactive

        AND ClientStateID = 1 -- Active

        

UNION ALL

-- Moved to Housing

SELECT

        r.ClientID,

        'Moved to Housing' AS "Category",

        a.StatusStartDate AS "TransitionDate"

FROM @AllChanges a

        INNER JOIN Removed r ON a.ClientID = r.ClientID

WHERE StatusStartDate BETWEEN @DateStart AND @DateEnd

        AND ChangeTypeID = 1

        AND HousingStateID = 1

        AND PriorStateID = 3

        AND ClientStateID = 1

UNION ALL

-- Moved to Inactive

SELECT

        r.ClientID,

        'Moved to Inactive' AS "Category",

        a.StatusStartDate AS "TransitionDate"

FROM @AllChanges a

        INNER JOIN Removed r ON a.ClientID = r.ClientID

WHERE StatusStartDate BETWEEN @DateStart AND @DateEnd

        AND ChangeTypeID = 2 -- Activity change

        AND HousingStateID = 3

        AND PriorStateID = 1

        AND ClientStateID = 3

)

SELECT Main.*,

        @ClusterName AS "Cluster",

        vw_ClientBasics.Gender_En,

        vw_ClientBasics.VeteranStatus_En,

        vw_ClientBasics.AboriginalIndicator_En "IndigenousStatus",

        DATEDIFF(yyyy,vw_ClientBasics.DOB,@DateEnd) "AgeAtDateEnd",

        @DateStart "DateStart",

        @DateEnd "DateEnd"

FROM Main

        INNER JOIN vw_ClientBasics ON Main.ClientID = vw_ClientBasics.ClientID


Changelog

Page  /