This is effectively the same SQL we use to calculate NCSO costings on the site, but adapted to be run on BigQuery

ncso_sql_for_bigquery.sql

SELECT
  ncso.date AS month,
  vmpp.bnf_code AS bnf_code,
  vmpp.nm AS product_name,
  rx.quantity AS quantity,
  rx.pct AS pct,
  rx.practice AS practice,
  dt.price_pence
    * rx.quantity
    * CASE WHEN
        -- For some presentations "quantity" means "number of packs" rather
        -- than e.g. tablets. In these cases we don't want to divide by the
        -- quantity value of a pack. This is implemented via a flag in our
        -- databse but this data isn't in BiqQuery so we just have a hardcoded
        -- list of BNF codes here
        vmpp.bnf_code in ('0206010F0AACJCJ', '1202010U0AAAAAA')
      THEN
        1
      ELSE
        1 / vmpp.qtyval
      END
    -- This is the "discount factor" which applies the National Average 7.2%
    -- discount to estimate Actual Cost from Net Ingredient Cost and also
    -- converts figures from pence to pounds
    * 0.00928
    AS tariff_cost,
  COALESCE(ncso.price_pence - dt.price_pence, 0)
    * rx.quantity
    * CASE WHEN
        vmpp.bnf_code in ('0206010F0AACJCJ', '1202010U0AAAAAA')
      THEN
        1
      ELSE
        1 / vmpp.qtyval
      END
    * 0.00928
    AS additional_cost,
  TIMESTAMP(ncso.date) != rx.month AS is_projection
FROM
  dmd.ncsoconcession AS ncso
JOIN
  dmd.tariffprice AS dt
ON
  ncso.vmpp = dt.vmpp AND ncso.date = dt.date
JOIN
  dmd.vmpp AS vmpp
ON
  vmpp.id=ncso.vmpp
JOIN
  hscic.prescribing AS rx
ON
  rx.bnf_code = vmpp.bnf_code
AND
-- Where we have prescribing data for the corresponding month we use
-- that, otherwise we use the latest month of prescribing data to
-- produce an estimate
(
  rx.month = TIMESTAMP(ncso.date)
  OR
  (
    -- This should be set to the latest date for which we have prescribing
    -- data
    rx.month = TIMESTAMP('2019-11-01')
    AND
    TIMESTAMP(ncso.date) > rx.month
  )
)