1 - The Overzealous Technician

Uh oh. A technician was a LITTLE bit over-zealous in their attempt to mark a medication bottle as opened and now you have NO IDEA what is inside /sarcasm.

You notice the NDC is unscathed. How could you determine the clinical drug product based on the NDC on the label?

‣

🔎 Reveal answer

duloxetine 30 MG Delayed Release Oral Capsule

RXCUI = 596930

See

RxNorm Reconfigured

for more information.

select * 
from staging.stg_rxnorm__ndcs ndc
inner join staging.stg_rxnorm__clinical_products cp 
	on cp.rxcui = ndc.clinical_product_rxcui
where ndc = '00002324030'

Hint: SageRx normalizes all NDCs to NDC11 format.
What is NDC11 Format?

Hint: the entity relationship diagram (ERD) shows the relationship between NDCs and clinical products, as well as brand products.

Hint: the respective tables in SageRx are located in the staging schema.

You suddenly forgot if this is a brand name product. How could you tell whether this was a brand product or a generic? Use the same diagram above to solve this as well.

‣

🔎 Reveal answer

It is a brand name product (Cymbalta).

duloxetine 30 MG Delayed Release Oral Capsule [Cymbalta]

RXCUI = 596932

NOTE: RxNorm lists brand names in brackets after the name of the product. In this example, we changed from joining to the clinical_products table to the brand_products table to find that this NDC is, in fact, a brand name product.

select * 
from staging.stg_rxnorm__ndcs ndc
inner join staging.stg_rxnorm__brand_products bp
	on bp.rxcui = ndc.brand_product_rxcui
where ndc = '00002324030'

Alternatively, you could join the brand_products table to the clinical_products table to see if there is an associated brand name product. Note that the ERD has links from brand_products to both ndcs and clinical_products.

select * 
from staging.stg_rxnorm__ndcs ndc
inner join staging.stg_rxnorm__clinical_products cp 
	on cp.rxcui = ndc.clinical_product_rxcui
inner join staging.stg_rxnorm__brand_products bp
	on cp.rxcui = bp.clinical_product_rxcui
where ndc = '00002324030'

One other way you could have done it is with an intermediate table. You could start with the NDC and go to “product” which will have information about a brand product if one exists related to the NDC (product TTY will be SBD or BPCK), and also clinical product information.

select *
from intermediate.int_rxnorm_ndcs_to_products
where ndc = '00002324030'

🎉 Good job! You over-engineered a solution to a simple problem with SQL!