In Step 6.14.1, the sample SQL use ActiveHHType, from tlsa_HHID. However, the specifications state to use EntryHHType from tlsa_HHID. I'm assuming the specifications should be considered correct, but can you confirm?
update hh
set hh.Other3917Days = (select datediff (dd,
(select top 1 hn.DateToStreetESSH
from tlsa_HHID hhid
inner join hmis_Enrollment hn on hn.EnrollmentID = hhid.EnrollmentID
where hhid.HoHID = hh.HoHID and hhid.ActiveHHType = hh.HHType
and hhid.EntryDate > hh.LastInactive
and hn.DateToStreetESSH <= hh.LastInactive
and (hhid.LSAProjectType in (0,1,8)
or hn.LivingSituation between 100 and 199
or (hn.LengthOfStay in (10,11) and hn.PreviousStreetESSH = 1)
or (hn.LivingSituation between 200 and 299
and hn.LengthOfStay in (2,3) and hn.PreviousStreetESSH = 1))
order by hn.DateToStreetESSH asc)
, hh.LastInactive)) + 1
, hh.Step = '6.14.1'
from tlsa_Household hh
For any active enrollment or any EnrollmentID from tlsa_HHID where HoHID/EntryHHType = tlsa_Household.HoHID/HHType and:
• EntryDate > LastInactive; and
o LivingSituation in (101,118,116) or LSAProjectType in (0,1,8); or
o LSAProjectType not in (0,1,8) and LengthOfStay in (10, 11) and PreviousStreetESSH = 1; or
o LSAProjectType not in (0,1,8) and LivingSituation in (204,205,206,207,215,225) and LengthOfStay in (2,3) and PreviousStreetESSH = 1
The value of Other3917Days is equal to the count of all dates:
• Between the later of DateToStreetESSH or LastInactive and the day prior to the associated EntryDate where the date does not already have a status based on system use.
• Between any DateToStreetESSH and the day prior to LastInactive where the associated EntryDate is > LastInactive.
In Step 6.14.1, the sample SQL use ActiveHHType, from tlsa_HHID. However, the specifications state to use EntryHHType from tlsa_HHID. I'm assuming the specifications should be considered correct, but can you confirm?