DM+D: ampps with DT and supplier information

[sebbacon]

SELECT
  vmpp.id AS vmpp_id,
  ampp.id AS ampp_id,
  ampp.bnf_code AS ampp_bnf_code,
  vmpp.bnf_code AS vmpp_bnf_code,
  ampp.nm AS ampp_name,
  vmpp.nm AS vmpp_name,
  dtinfo.price AS dt_price,
  priceinfo.price AS price,
  priceinfo.pricedt AS price_valid_on,
  priceinfo.price_prev AS price_prev,
  pricebasis.descr AS price_basis,
  route.descr AS licensed_route,
  supplier.descr AS supplier,
  route.descr IS NULL AS possible_wholesaler,
  dtpaymentcategory.descr AS category,
  CASE
    WHEN supplier.descr = "A A H Pharmaceuticals Ltd" THEN 2
    WHEN supplier.descr = "Alliance Healthcare (Distribution) Ltd" THEN 2
    WHEN supplier.descr = "Actavis UK Ltd" THEN 1
    WHEN supplier.descr = "Teva UK Ltd" THEN 1
    WHEN supplier.descr = "Accord Healthcare Ltd" THEN 1
  ELSE
  NULL
END
  AS cat_a_weighting
FROM
  `ebmdatalab.dmd2.vmpp` vmpp
INNER JOIN
  `ebmdatalab.dmd2.ampp` ampp
ON
  ampp.vmpp = vmpp.id
LEFT JOIN
  ebmdatalab.dmd2.discontinuedind
ON
  discontinuedind.cd = disc
LEFT JOIN
  ebmdatalab.dmd2.legalcategory
ON
  legalcategory.cd = legal_cat
LEFT JOIN
  ebmdatalab.dmd2.dtinfo
ON
  dtinfo.vmpp = vmpp.id
INNER JOIN
  `ebmdatalab.dmd2.dtpaymentcategory` dtpaymentcategory
ON
  dtpaymentcategory.cd = dtinfo.pay_cat
LEFT JOIN
  ebmdatalab.dmd2.priceinfo
ON
  priceinfo.ampp = ampp.id
INNER JOIN
  ebmdatalab.dmd2.pricebasis
ON
  priceinfo.price_basis = pricebasis.cd
LEFT JOIN
  ebmdatalab.dmd2.licroute
ON
  ampp.amp = licroute.amp
LEFT JOIN
  ebmdatalab.dmd2.route
ON
  route.cd = licroute.route
INNER JOIN
  `ebmdatalab.dmd2.amp` amp
ON
  amp.id = ampp.amp
LEFT JOIN
  `ebmdatalab.dmd2.supplier` supplier
ON
  supplier.cd = amp.supp
WHERE
  (disc IS NULL
    OR disc != 1)
  AND ampp.invalid = FALSE
  AND NOT (ampp.bnf_code IS NULL
    AND vmpp.bnf_code IS NULL)