Make a query for listing all available variations of Phenytoin

[sebbacon]

WITH ampp_meta AS ( -- we want to select AMPs where at least one AMPP has a null discdt; that means at least one AMPP is available
  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.nm AS vmp_name,
  dmd_amp.nm AS amp_name, 
  dmd_lookup_dt_payment_category.desc as tariff_category,
  tariff_price,
  max_list_price,
  dmd_lookup_supplier."desc" AS supplier,
  gtin_startdt AS brand_owner_start_date, -- not completely reliable as a marker for brand owners when it comes to generics. Most generics (17,500) have no GTIN associated, though some (4929) do
  ampp_meta.most_recent_discontinued_date,
  gtin_enddt AS brand_owner_end_date, 
  dmd_vmp.nm = dmd_amp.nm AS is_manufactured_generic, -- based on if the VMP and AMP are called the same thing
  ampp_meta.available_ampp_count,
  -- dmd_lookup_virtual_product_non_avail."desc" AS non_availability, -- remove `dmd_lookup_virtual_product_non_avail` to see this
  dmd_lookup_virtual_product_pres_status."desc" AS prescribability,
  bnf_code, dmd_vmp.vpid, appid
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
  -- "actual products not available" is not always reliable. Sometimes all the actual products have discontinued dates, hence:
  AND  (ampp_meta.available_ampp_count > 0)  -- there are non-discontinued products, i.e. available ones
  AND (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 dmd_amp.nm ilike '%lemsip%';
  -- AND bnf_code like '1103010H0%'
  --AND (gtin IS NOT NULL and gtin_enddt IS NULL) -- restricts to brand owner
  -- AND dmd_vmp.nm ILIKE 'Fusidic acid 1% modified-release eye%'
  -- AND dmd_vmp.nm ILIKE 'Phenytoin%100%cap%'
    AND dmd_vmp.nm ILIKE 'Levothyroxine sodium 25%tab%'
    ---AND dmd_vmp.vpid = '319617005' -- fragmin
   ---AND bnf_code like '0407041R0____AB'
   --AND apid2 = '29984911000001105'
ORDER BY
  most_recent_discontinued_date, brand_owner_start_date;