Definition

The mkb_drugpack model provides pharmaceutical packaging details, linking medications to their commercial forms. It combines preparation data with pack configurations including pricing, quantities, and supplier information.

Information

The MKB drugpack model provides packaging information for pharmaceutical products used within the healthcare system. It consolidates medication preparation data with their associated pack configurations, including pricing, unit quantities, and supplier details.

This model offers a standardized view of drug packaging across participating organizations.

The model can be used in conjunction with the drug_code model for common query patterns such as retrieving detailed packaging information for a specific drug via preparation_code_id which corresponds to code_id in the drug pack model. This model also uses dmd_product_code_id for mapping with the mapping dmd preparation model.

Schema updates for data maintenance

The is_deleted column has been added to indicate whether a clinical code has been marked as deleted

Benefit

The is_deleted column enables more effective data maintenance by allowing customers to identify and manage deleted clinical codes

Examples

Detailed packaging information for a specific drug

SELECT
  drug.term AS drug_name,
  drug.dmd_product_code_id,
  pack.pack_description,
  pack.units_per_pack,
  pack.unit_of_measure,
  pack.pack_price,
  pack.price_per_unit
FROM hive.explorer_open_safely.drug_code AS drug
JOIN hive.explorer_open_safely.mkb_drugpack AS pack
  ON drug.code_id = pack.preparation_code_id
WHERE
  drug.term LIKE '%metformin%'
ORDER BY
  drug.term, pack.units_per_pack

All packaging options for a specific medication

SELECT
  term AS drug_name,
  supplier_name,
  pack_description,
  units_per_pack,
  unit_of_measure,
  pack_price,
  price_per_unit
FROM hive.explorer_open_safely.mkb_drug_pack
WHERE
  term LIKE '%Amoxicillin%'
  AND is_deleted = FALSE
ORDER BY
  price_per_unit ASC

Medications with the most packaging options

SELECT
  term AS drug_name,
  COUNT(DISTINCT preparation_pack_id) AS pack_options_count,
  COUNT(DISTINCT supplier_name) AS supplier_count,
  MIN(price_per_unit) AS min_price_per_unit,
  MAX(price_per_unit) AS max_price_per_unit,
  AVG(price_per_unit) AS avg_price_per_unit
FROM hive.explorer_open_safely.mkb_drug_pack
WHERE
  is_deleted = FALSE
  AND price_per_unit > 0
GROUP BY
  term
HAVING
  COUNT(DISTINCT preparation_pack_id) > 3
ORDER BY
  pack_options_count DESC,
  supplier_count DESC
LIMIT 20