singletons.sql

  WITH ampp_meta AS (
  SELECT
    *
  FROM (
    SELECT
      apid,
      COUNT(*) AS available_ampp_count
    FROM
      public.dmd_ampp
    WHERE
      disccd IS NULL
      OR disccd = 0
    GROUP BY
      apid) continuing
  RIGHT JOIN (
    SELECT
      apid AS apid2,
      MAX(gtin) AS gtin,
      MAX(dmd_ampp.appid) AS appid,
      MAX(startdt) AS gtin_startdt,
      MAX(enddt) AS gtin_enddt,
      MAX(discdt) AS most_recent_discontinued_date,
      MAX(price) AS max_list_price
    FROM
      dmd_ampp
    LEFT JOIN
      dmd_price_info
    ON
      dmd_price_info.appid = dmd_ampp.appid
    LEFT JOIN
      dmd_gtin
    ON
      CAST(dmd_gtin.appid AS bigint) = dmd_ampp.appid
    GROUP BY
      apid) prices_and_dates
  ON
    prices_and_dates.apid2 = continuing.apid),
  dt_meta AS (
  SELECT
    vpid,
    MAX(price) AS tariff_price,
    MAX(pay_catcd) AS tariff_category
  FROM
    dmd_vmpp
  INNER JOIN
    dmd_dtinfo
  ON
    dmd_dtinfo.vppid = dmd_vmpp.vppid
  GROUP BY
    dmd_vmpp.vpid )
SELECT
  dmd_vmp.vpid,
  MIN(bnf_code) AS bnf_code
FROM
  public.dmd_amp
LEFT JOIN
  ampp_meta
ON
  ampp_meta.apid2 = dmd_amp.apid
LEFT JOIN
  public.dmd_lookup_supplier
ON
  dmd_lookup_supplier.cd = dmd_amp.suppcd
LEFT JOIN
  dmd_vmp
ON
  dmd_vmp.vpid = dmd_amp.vpid
LEFT JOIN
  dt_meta
ON
  dt_meta.vpid = dmd_vmp.vpid
LEFT JOIN
  dmd_vtm
ON
  dmd_vmp.vtmid = dmd_vtm.vtmid
LEFT JOIN
  dmd_product
ON
  (dmd_product.dmdid = dmd_amp.apid)
LEFT JOIN
  dmd_lookup_dt_payment_category
ON
  dmd_product.tariff_category = dmd_lookup_dt_payment_category.cd
LEFT JOIN
  public.dmd_lookup_virtual_product_non_avail
ON
  dmd_lookup_virtual_product_non_avail.cd = dmd_vmp.non_availcd
LEFT JOIN
  public.dmd_lookup_virtual_product_pres_status
ON
  dmd_lookup_virtual_product_pres_status.cd = dmd_vmp.pres_statcd
WHERE
  (dmd_lookup_virtual_product_non_avail.cd IS NULL
    OR dmd_lookup_virtual_product_non_avail.cd = 0) -- no non-availability listed; 1 = actual products not available
  (dmd_vmp.pres_statcd IS NULL
    OR dmd_vmp.pres_statcd <> 2) -- not marked as invalid to prescribe in primary care
  AND dmd_amp.lic_authcd <> 3 -- 1 = unlicensed, 2 = medicines, 3 = devices, 4 = trad herbal, 5 = unknow
  AND parallel_import IS NULL
  AND (gtin IS NOT NULL) -- restricts to brand owner
GROUP BY
  dmd_vmp.vpid
HAVING
  SUM(ampp_meta.available_ampp_count) = 1 AND --
  COUNT(dmd_vmp.nm <> dmd_amp.nm) = 1
  AND COUNT(*) = 2;