LSA Temporary and Reference Tables
These tables are created by 01 Temp Reporting and Reference Tables.sql.
tlsa_CohortDates
Based on ReportStart and ReportEnd, includes all cohorts used in the LSA with their associated start, end, and lookback dates.
Logic is defined in 3.2 LSA Reporting Cohorts and Dates.
| Column Name | Data Type | Notes |
|---|---|---|
| Cohort | int | Cohort identifier |
| CohortStart | date | Cohort start date |
| CohortEnd | date | Cohort end date |
| LookbackDate | date | ReportStart - 7 years |
| ReportID | int | LSAReport.ReportID |
tlsa_HHID
A master table of potentially reportable HMIS HouseholdIDs active in continuum ES/SH/TH/RRH/PSH projects between LookbackDate (ReportStart - 7 years) and ReportEnd. Used to store effective entry, move-in, and exit dates, household types, and other frequently-referenced data.
Business logic associated with populating tlsa_HHID is in section 3.3 HMIS Household Enrollments
| Column Name | Data Type | Notes |
|---|---|---|
| HouseholdID | nvarchar(32) | From HMIS |
| HoHID | nvarchar(32) | The PersonalID for the head of household |
| EnrollmentID | nvarchar(32) | From HMIS |
| ProjectID | nvarchar(32) | From HMIS |
| LSAProjectType | int | If ProjectType = 13 and RRHSubType = 2, LSAProjectType = 13 If ProjectType = 13 and RRHSubType = 1, LSAProjectType = 15 Otherwise, LSAProjectType = hmis_Project.ProjectType. |
| EntryDate | date | Section 3.3 - EntryDate |
| MoveInDate | date | Section 3.3 - MoveInDate |
| ExitDate | date | Section 3.3 - ExitDate |
| LastBednight | date | Section 3.3 - LastBedNight |
| EntryHHType | int | Section 3.6 - Household Types |
| ActiveHHType | int | Section 3.6 - Household Types |
| Exit1HHType | int | Section 3.6 - Household Types |
| Exit2HHType | int | Section 3.6 - Household Types |
| ExitDest | int | Section 3.3 - ExitDest |
| Active | bit | Section 5.1 - Active and AIR HouseholdIDs |
| AIR | bit | Section 5.1 - Active and AIR HouseholdIDs |
| ExitCohort | int | Section 7.1 - Qualifying Exits in Exit Cohort Periods |
| HHChronic | int | Section 5.12 - HHChronic |
| HHVet | int | Section 5.12 - HHVet |
| HHDisability | int | Section 5.12 - HHDisability |
| HHFleeingDV | int | Section 5.12 - HHFleeingDV |
| HHAdultAge | int | Section 5.12 - HHAdultAge |
| HHParent | int | Section 5.12 - HHParent |
| AC3Plus | int | Section 5.12 - AC3Plus |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
tlsa_Enrollment
A master table of potentially reportable enrollments associated with the HouseholdIDs in tlsa_HHID; used to store entry, exit, and move-in dates, enrollment ages, and other frequently-referenced data.
Business logic associated with populating tlsa_Enrollment is in section 3.4 HMIS Client Enrollments (tlsa_Enrollment).
| Column Name | Data Type | Notes |
|---|---|---|
| EnrollmentID | nvarchar(32) | From HMIS |
| PersonalID | nvarchar(32) | From HMIS |
| HouseholdID | nvarchar(32) | From HMIS |
| RelationshipToHoH | int | From HMIS |
| ProjectID | nvarchar(32) | From HMIS |
| LSAProjectType | int | If ProjectType = 13 and RRHSubType = 2, LSAProjectType = 13 If ProjectType = 13 and RRHSubType = 1, LSAProjectType = 15 Otherwise, LSAProjectType = hmis_Project.ProjectType. |
| EntryDate | date | Section 3.4 - EntryDate |
| MoveInDate | date | Section 3.4 - MoveInDate |
| ExitDate | date | Section 3.4 - ExitDate |
| LastBednight | date | Section 3.4 - LastBedNight |
| EntryAge | int | Section 3.5 - Enrollment Ages |
| ActiveAge | int | Section 3.5 - Enrollment Ages |
| Exit1Age | int | Section 3.5 - Enrollment Ages |
| Exit2Age | int | Section 3.5 - Enrollment Ages |
| DisabilityStatus | int | Section 3.4 - DisabilityStatus |
| DVStatus | int | Section 3.4 - DVStatus |
| Active | bit | Section 5.2 - Active Enrollments |
| AIR | bit | Section 5.2 - AIR Enrollments |
| PITOctober | bit | Section 9.2 - Active and Point in Time Cohorts |
| PITJanuary | bit | Section 9.2 - Active and Point in Time Cohorts |
| PITApril | bit | Section 9.2 - Active and Point in Time Cohorts |
| PITJuly | bit | Section 9.2 - Active and Point in Time Cohorts |
| CH | bit | Section 5.6 - Enrollments Relevant to Counting ESSHStreet Dates |
| HIV | bit | Section 5.4 - HIV |
| SMI | bit | Section 5.4 - SMI |
| SUD | bit | Section 5.4 - SUD |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
tlsa_Person
A client-level pre-cursor to LSAPerson with records for each PersonalID active in report period.
Business logic associated with tlsa_Person is in section 5 - HMIS Business Logic - LSAPerson
ch_Exclude
Dates enrolled in TH or housed in RRH/PSH; used for LSAPerson chronic homelessness determination.
| Column Name | Data Type | Notes |
|---|---|---|
| PersonalID | nvarchar(32) | Section 5.7 - Get Dates to Exclude from Counts of ES/SH/Street Days |
| excludeDate | date | Section 5.7 - Get Dates to Exclude from Counts of ES/SH/Street Days |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
ch_Include
Dates in ES/SH or on the street; used for LSAPerson chronic homelessness determination.
| Column Name | Data Type | Notes |
|---|---|---|
| PersonalID | nvarchar(32) | Section 5.8 - Get Dates to Include in Counts of ES/SH/Street Days |
| ESSHStreetDate | date | Section 5.8 - Get Dates to Include in Counts of ES/SH/Street Days |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
ch_Episodes
Holds episodes of ES/SH/Street time constructed from ch_Include for LSAPerson chronic homelessness determination.
| Column Name | Data Type | Notes |
|---|---|---|
| PersonalID | nvarchar(32) | Section 5.9 - Get ES/SH/Street Episodes |
| episodeStart | date | Section 5.9 - Get ES/SH/Street Episodes |
| episodeEnd | date | Section 5.9 - Get ES/SH/Street Episodes |
| episodeDays | int | Section 5.9 - Get ES/SH/Street Episodes |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
tlsa_Household
A household-level precursor to LSAHousehold with a record for each unique combination of the head of household’s PersonalID (HoHID) and household type (HHType) active in the report period.
Business logic associated with tlsa_Household begins in section 6.1 Get Distinct Households for LSAHousehold
sys_Time
Used as the basis for counts of dates in ES/SH, TH, RRH/PSH (unhoused and housed), and ES/SH/StreetDates for LSAHousehold.
Business logic associated with sys_Time is in sections:
| Column Name | Data Type | Notes |
|---|---|---|
| HoHID | nvarchar(32) | |
| HHType | int | |
| sysDate | date | |
| sysStatus | int | |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
sys_TimePadded
Used to identify households’ last inactive date as a precursor to reporting on SystemPath in LSAHousehold.
See section 6.12 Get Last Inactive Date for business logic.
| Column Name | Data Type | Notes |
|---|---|---|
| HoHID | nvarchar(32) | |
| HHType | int | |
| Cohort | int | |
| StartDate | date | |
| EndDate | date | |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
tlsa_Exit
A household-level precursor to LSAExit with a record for each unique combination of the head of household’s PersonalID (HoHID), household type (HHType), and cohort with qualifying exits in the given cohort period.
Business logic begins in section 7.1 Identify Qualifying Exits in Exit Cohort Periods and continues through the end of section 7.
ch_Exclude_exit
An analog to ch_Exclude; used to hold dates in TH or housed in RRH/PSH for LSAExit chronic homelessness determination.
Business logic is in section 7.5 Get Dates to Exclude from Counts of ES/SH/Street Days (ch_Exclude_exit)
| Column Name | Data Type | Notes |
|---|---|---|
| PersonalID | nvarchar(32) | |
| excludeDate | date | |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
ch_Include_exit
An analog for ch_Include; holds dates in ES/SH or on the street for LSAExit chronic homelessness determination.
Business logic is in section 7.6 Get Dates to Include in Counts of ES/SH/Street Days (ch_Include_exit).
| Column Name | Data Type | Notes |
|---|---|---|
| PersonalID | nvarchar(32) | |
| ESSHStreetDate | date | |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
ch_Episodes_exit
An analog for ch_Episodes; records of episodes of ES/SH/Street time constructed from ch_Include for LSAExit chronic homelessness determination.
Business logic is in section 7.7 Get ES/SH/Street Episodes (ch_Episodes_exit).
| Column Name | Data Type | Notes |
|---|---|---|
| PersonalID | nvarchar(32) | |
| episodeStart | date | |
| episodeEnd | date | |
| episodeDays | int | |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
sys_TimePadded_exit
An analog for sys_TimePadded; used to identify households’ last inactive date as a precursor to reporting on SystemPath in LSAExit.
Business logic is in section 7.11 Last Inactive Date for Exit Cohorts.
| Column Name | Data Type | Notes |
|---|---|---|
| HoHID | nvarchar(32) | |
| HHType | int | |
| Cohort | int | |
| StartDate | date | |
| EndDate | date | |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
tlsa_ExitHoHAdult
Used as the basis for reporting on chronic homelessness for LSAExit (which is limited to adults and head of household).
Business logic is in section 7.4 Identify HoH and Adult Members of Exit Cohorts.
| Column Name | Data Type | Notes |
|---|---|---|
| PersonalID | nvarchar(32) | Section 7.4 - Record Selection |
| QualifyingExitHHID | nvarchar(32) | Section 7.4 - Record Selection |
| Cohort | int | Section 7.4 - Record Selection |
| DisabilityStatus | int | Section 7.4 - DisabilityStatus |
| CHStart | date | Section 7.4 - CHStart |
| LastActive | date | Section 7.4 - LastActive |
| CHTime | int | Section 7.4 - CHTime and CHTimeStatus |
| CHTimeStatus | int | Section 7.4 - CHTime and CHTimeStatus |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
tlsa_AveragePops
Used to identify households in various populations and subpopulations for reporting on average # of days in section 8 based on tlsa_Household and tlsa_Exit.
Required populations, subpopulations, and associated criteria are in section 8.3 Populations for Average Days from LSAHousehold and LSAExit.
| Column Name | Data Type | Notes |
|---|---|---|
| PopID | int | |
| Cohort | int | |
| HoHID | nvarchar(32) | |
| HHType | int | |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
tlsa_CountPops
Used to identify people/households in various populations and subpopulations for active-in-residence counts in section 9.
| Column Name | Data Type | Notes |
|---|---|---|
| PopID | int | |
| PersonalID | nvarchar(32) | |
| HouseholdID | nvarchar(32) | |
| Step | nvarchar(10) | Not referenced in programming specifications; used by sample code to identify the query that created or most recently updated the record. |
ref_Calendar
A table of dates between 10/1/2012 and 9/30/2030. This is populated by 01 Temp Reporting and Reference Tables.sql and used in the sample code as a tool for counting days.
| Column Name | Data Type | Notes |
|---|---|---|
| theDate | date | |
| yyyy | smallint | |
| mm | tinyint | |
| dd | tinyint | |
| month_name | nvarchar(10) | |
| day_name | nvarchar(10) | |
| fy | smallint |
ref_RowValues
A reference table of valid/required combinations of Cohort, Universe, and SystemPath values for each ReportRow in LSACalculated; this is populated by insert statements in 01 Temp Reporting and Reference Tables.sql based on requirements defined in:
- Section 8 - LSACalculated Averages
- Section 9 - LSACalculated Counts
- Section 10 - LSACalculated Data Quality Counts
| Column Name | Data Type | Notes |
|---|---|---|
| RowID | int | |
| Cohort | int | |
| Universe | int | |
| SystemPath | int |
ref_RowPopulations
A reference table of required populations/subpopulations for each ReportRow in LSACalculated; this is populated by insert statements in 01 Temp Reporting and Reference Tables.sql based on requirements defined in:
- Section 8 - LSACalculated Averages
- Section 9 - LSACalculated Counts
- Section 10 - LSACalculated Data Quality Counts.
| Column Name | Data Type | Notes |
|---|---|---|
| RowMin | int | |
| RowMax | int | |
| ByPath | int | |
| ByProject | int | |
| PopID | int | |
| Pop1 | int | |
| Pop2 | int |
ref_PopHHTypes
A reference table of household types associated with each population; this is populated by insert statements in 01 Temp Reporting and Reference Tables.sql based on criteria defined in sections 8 and 9.
| Column Name | Data Type | Notes |
|---|---|---|
| PopID | int | |
| HHType | int |