Possible discrepancy in 10.7 between SQL and Results

[evaripatis]

We have a question regarding the sample SQL and sample results, with respect to Step 10.7 (10 LSACalculated Data Quality.sql). Line 161 of the sample SQL states that RelationshipToHoH is either Null or not between 1 and 5. When the original Enrollment.csv file is filtered for this, 5 rows are returned (EnrollmentIDs NXS10221157, NXS11021257, NXS11427925, RRH15826969, and RRH15826987). Line 158 of the sample SQL has Enrollment.csv data joined on HouseholdID and EnrollmentCoC. In the original Enrollment.csv file, there are no values for EnrollmentCoC for these 5 individuals, likely because they are NOT heads of household. It seems as if EnrollmentCoC, from the HoH, should first be getting applied to ALL members of the same HouseholdID. Is this perhaps something SQL is inherently doing, or is this a discrepancy between the sample SQL and the sample results?

[vlopez0603]

Hi @evaripatis - I have a quick clarifying question. By "discrepancy between the sample SQL and the sample results," are you referring to the fact that the sample LSACalculated file lists a total of 4 cases for ReportRow 908 as opposed to the 5 individuals in the Enrollment.csv file where RelationshipToHoH == 99?

[evaripatis]

@vlopez0603, no, I'm curious why any of the 5 are getting counted when none of them have an EnrollmentCoC value. Thus the question if SQL is somehow inherently associating the EnrollmentCoC values to the HouseholdID values. Our results aren't returning a count for any of those three ProjectID for ReportRow 908.

[vlopez0603]

Got it. Our dev is taking a look at this, and I'll follow up once I know more.

[vlopez0603]

Hi @evaripatis - Changes were made to steps 10.6, 10.7, and 11.2, and the sample SQL code has been updated. When you get a chance, please let us know if this resolves the discrepancy that you identified in step 10.7. Thanks!

[evaripatis]

A quick note/observation about the change made for Step 11.2. While it is nice that it is now explicit, it shouldn't be necessary, since the join is on a sub query where you are already inner joining on EnrollmentCoC=ReportCoC. Because that inner join on CoC happens within the sub query, the sub query should only be returning heads of household. The only reason RelationshipToHoH=1 should be necessary, in this scenario, is if non-HoH also happen to have the EnrollmentCoC field filled out, which is unlikely.

Still reviewing/testing Step 10 updates, on my end.

[evaripatis]

@vlopez0603, all discrepancies now resolved.