HMIS / LSASampleCode

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

Error in Step 7.4.1 of sample SQL #1206

Closed evaripatis closed 9 months ago

evaripatis commented 9 months ago

For Step 7.4, the specifications state the age must be between 21 and 65, while the sample SQL has the age being between 18 and 65. image

    insert into tlsa_ExitHoHAdult (
        PersonalID, QualifyingExitHHID,
        Cohort, CHStart, LastActive, 
        Step)
    select distinct n.PersonalID, ex.QualifyingExitHHID,
        ex.Cohort, 
        dateadd(dd, 1, (dateadd(yy, -3, max(n.ExitDate)))),
        max(n.ExitDate), '7.4.1'
    from tlsa_Exit ex
    inner join tlsa_HHID hhid on hhid.HouseholdID = ex.QualifyingExitHHID
    inner join tlsa_CohortDates cd on cd.Cohort = ex.Cohort
    inner join tlsa_Enrollment n on n.HouseholdID = ex.QualifyingExitHHID 
        and n.ExitDate between cd.CohortStart and cd.CohortEnd
    inner join hmis_Enrollment hn on hn.EnrollmentID = n.EnrollmentID
    where (n.RelationshipToHoH = 1 
            or (cd.Cohort = 0 and n.ActiveAge between 18 and 65)
            or (cd.Cohort = -1 and n.Exit1Age between 18 and 65)
            or (cd.Cohort = -2 and n.Exit2Age between 18 and 65))
        and (ex.ExitFrom <> 3 or hhid.EntryDate > dateadd(yy, -1, hhid.ExitDate))
        and (ex.ExitFrom not in (5,6) or hhid.MoveInDate > dateadd(yy, -1, hhid.ExitDate))
    group by n.PersonalID, ex.QualifyingExitHHID, ex.Cohort
MollyMcEvilley commented 9 months ago

Given the possible values for age, this will result in the same output...

image