search

HMIS / LSASampleCode

Longitudinal System Analysis (LSA) Sample Code and Documentation
22 stars 10 forks source link

Question about 6.14.2 #1189

Closed evaripatis closed 10 months ago

evaripatis commented 11 months ago

In testing with the "DataLab - 2024 Zips 2023-11-09" data set, I'm finding a number of scenarios where multiple enrollments for an individual have the same DateToStreetESSH value. The logic calls to take the top 1 EnrollmentID, from hmis_Enrollment (code below), when ordered by DateToStreetESSH, but what is the expected result when there are multiple EnrollmentIDs that could be chosen? Should the code also be ordering by EnrollmentID? Should it, instead, be taking all EnrollmentIDs where it has that top DateToStreetESSH? It is made further unclear by the fact that the actual specs don't seem to say anything about only being interested in one particular Enrollment, but seemingly just the greatest DateToStreetESSH.

(select top 1 hn.EnrollmentID 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)

image image

MollyMcEvilley commented 10 months ago

With many apologies, I have finally stared at this long enough to grasp the issue. That subquery has been using 'select top 1...' for three years -- what I have failed to grasp about your issue is that that is totally out of sync with the specs., which don't say anywhere to use only the earliest DateToStreetESSH. That might seem obvious, given that you even included a screenshot of the specs, but... not so much, apparently. I was thinking it was a question of which enrollment to use when there were multiple with the same DateToStreetESSH and not that the code is just altogether wrong.

So. I have updated the code for 6.14.2 to capture dates based on DateToStreetESSH from all relevant enrollments, consistent with the specs. Commit forthcoming.

evaripatis commented 10 months ago

Acknowledged and appreciated. Thank you.