Follow-up for #1192: Sample Code (Step 7.9.2) - HHDisability - question about cohort usage

[nataliesus]

Hi @MollyMcEvilley ,

Step 7.9.2 was updated recently. I would like to confirm with you the current logic for [HHDisability]:

1. Want to double-check that hhid.HoHID = disability.PersonalID is OK, and we do not need to use disability.RelationshipToHoH = 1 instead;
2. Just want to confirm we should use ex.Cohort which was added recently, and not cd.Cohort as it was done for [HHFleeingDV];
3. [HHFleeingDV] has coalesce( ..., 0 ) condition. I wonder if we do not need the same condition for [HHDisability] as well to cover cases when client is not HoH and not adult (now [HHDisability] is NULL in this case, but according to the spec it should be 0 )

Thank you! Natalie

[MollyMcEvilley]

1. Since the HouseholdID is part of the where clause, the two are equivalent -- the HoHID is set based on RelationshipToHoH -- so the result is the same.
2. There will be no difference in the result, but as you point out, the join to tlsa_CohortDates isn't necessary since we can get the cohort from tlsa_Exit. I'm guessing there's no real impact on performance, but I'm going to remove the join and use tlsa_Exit.Cohort.
3. There is only one non-zero possibility for HHDisability, so we can take the maximum value, whatever it is. For HHFleeingDV, there are multiple non-zero possibilities and we want to use the minimum value that is not zero, if one exists. The COALESCE for HHFleeingDV compensates for the 'then null' in that case statement that doesn't exist in the one for HHFleeingDV.

[jbloescw]

@MollyMcEvilley This change results in an error for SQL Servers Msg 8124, Level 16, State 1, Line 7837 Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

[eanders]

@jbloescw did you have a fix for this? (Or has it been fixed?)

[jbloescw]

@eanders There was another push Molly did that rearranged the query in a way where SQL does not error out. It compiled and gave me the all clear.

[eanders]

@jbloescw thank you!