search

open-contracting / bi.open-contracting.org

0 stars 0 forks source link

`contracts[]/value/amount` is incorrect 99% of the time, if `awards[]/items[]/unit/enteredValue` is present #109

Open jpmckinney opened 9 months ago

jpmckinney commented 9 months ago

If it's within 0.05 (rounding) of awards[]/items[]/unit/enteredValue/amount awards[]/items[]/quantity, it should be changed to awards[]/items[]/unit/correctedValue/amount awards[]/items[]/quantity.

SELECT
  data->>'ocid',
  i.value->'unit'->'enteredValue'->>'amount' entered,
  i.value->'unit'->'correctedValue'->>'amount' corrected,
  i.value->>'quantity' quantity,
  round((i.value->'unit'->'enteredValue'->>'amount')::numeric * (i.value->>'quantity')::numeric, 2) entered_mul,
  round((i.value->'unit'->'correctedValue'->>'amount')::numeric * (i.value->>'quantity')::numeric, 2) corrected_mul,
  jsonb_path_query_array(data, '$.contracts[*].value.amount') contract_amounts
FROM ecuador_sercop_bulk
CROSS JOIN jsonb_array_elements(data->'awards') a
CROSS JOIN jsonb_array_elements(a.value->'items') i
WHERE
  i.value->'unit' ? 'enteredValue'
  AND jsonb_path_query_array(data, '$.contracts[*].value.amount') @> to_jsonb(round((i.value->'unit'->'enteredValue'->>'amount')::numeric * (i.value->>'quantity')::numeric, 2));