Voids and repairs are two of the most reported areas in social housing, and the relationship between them is more direct than it first looks. The temptation is to reconstruct it by matching a property and a date range. NEC already records the link on the works order itself, and using the date overlap instead is exactly where the numbers go wrong.
The link is a column, not an inference
A works order that belongs to a void carries WOR_VIN_REFNO, a direct reference to the void instance. Void works are simply the works orders where that column is populated. You do not have to guess from property and dates; the schema states the relationship outright.
SELECT vin.vin_pro_refno,
wor.wor_srq_no || '/' || wor.wor_seqno AS works_order
FROM void_instances vin
JOIN works_orders wor
ON wor.wor_vin_refno = vin.vin_refnoWhy inferring it over-counts
Match every works order on the property between the void’s start and end and you sweep in responsive repairs that had nothing to do with the void, while still risking void works dated outside your window. The date-overlap heuristic feels reasonable and is wrong. WOR_VIN_REFNO is the answer, and it is unambiguous.
Mind property versus communal
One more nuance: a works order attaches to a property through WOR_PRO_REFNO or to a communal admin unit through WOR_AUN_CODE, never both. The universal location key is NVL(WOR_PRO_REFNO, WOR_AUN_CODE). Void works sit at property level, so decide up front whether communal work on the block during a void belongs in your figures, because it will not carry the property’s void reference.
The relationship is stated, not implied. Use the column NEC gives you, and the void repair numbers stop drifting.