Only keep matched records in HNY devdb table

[SashaWeinstein]

Current Implementation

Right now the HNY_devdb table is created with a left join in _hny.sql of MID_devDB to HNY_lookup. However there are far more records in MID_devdb than HNY_lookup, resulting in a table with many records with just data from MID_devdb and NULL for the rest of the fields.

Enhancement

I think it would be cleaner to have an inner join here instead, as it would make the HNY_devdb more easily readable.

Downstream effects

The two rows from the HNY_devdb table are added to FINAL_devdb with another left join in final.sql. The left join should populate these rows with nulls in situations where there is no matching HNY record, which is the same as the existing functionality

[SashaWeinstein]

@td928 might helpful for your confirmation here that this is a good enhancement

[td928]

yeah good observation there. I think the intention was for the analyst be able to pull HNY information in the context of ALL other activities tracked by DevDB. Say a community district what percentage of the total new constructions are also HNY. That's why the table is a little more hunky. Also this part of the work is completely reworked in the HNY join ticket and I would recommend look in there for base which to do more enhancement on.

[SashaWeinstein]

Ok word I don't think the HNY_devdb would be able to answer that question on total new constructions? The final_devdb would answer that question

[td928]

yeah I am with you there. It does feel like they are somewhat redundant. I asked Housing whether we can modify hny_devdb and their answers weren't inconclusive. Maybe when they give their feedbacks on the HNY enhancement, I can check with them again.

[SashaWeinstein]

Sorry maybe I don't understand. Does hny_devdb get delivered to housing as a separate table? If so why do they want all the job numbers with no linked HNY job?

[td928]

yeah it is part of the output. HNY_devdb does link the hny units. They don't care about the specific HNY jobs in this table and but they asked for it and reason the entirely new HNY_devdb_lookup table is created with this pr

[SashaWeinstein]

On my machine the HNY_devdb looks like this.

There are around 254k job numbers matched to around 17k hny jobs. I had to scroll down pretty far to find a record linked to hny.
Will the new change update this?

[td928]

right right I forgot this is how it looks. I definitely think we should change it to using a inner join then. I can make this change in my PR and also let Housing know to expect this change.

[SashaWeinstein]

Ok sick. It's addressed in PR #599 so maybe it makes sense to change in your PR as well to avoid the merge conflict