Orange Book Change History

The RxDataLab Orange Book dataset has a the complete monthly change history since the dataset’s tracking baseline. The current export covers 29 monthly releases (October 2023 through March 2026).

Typical monthly activity:

Month	Added	Updated	Removed
2026-03-01	164	431	7
2026-02-01	148	304	2
2026-01-01	197	412	5

The high updated count relative to added/removed is characteristic of Orange Book data: FDA corrects patent expiry dates, adjusts TE codes, and updates exclusivity records every month. The change log captures all of it.

The Four Change Tables #

Table	What it tracks
`product_changes`	Products added, updated, or removed (also includes `appl_type` column for filtering without joining `applications`)
`patent_changes`	Patent listings added, updated, or removed
`exclusivity_changes`	Exclusivity grants added (new grants) or removed (expired/withdrawn)
`application_changes`	NDA/ANDA-level changes: new applications, sponsor renames, removals

All four share the same structural pattern: an event column, an ob_release_date column, and then all data columns from the corresponding current-snapshot table.

The Four Event Types #

Event	Meaning
`beginning_of_history`	Row was present in the Orange Book when RxDataLab first imported it. The drug may have been approved decades earlier. Not a genuine addition.
`added`	Row appeared in the OB for the first time after the baseline (or was re-added after a removal)
`updated`	One or more fields changed versus the prior release
`removed`	Row disappeared from the OB

Beginning of History

Always filter event != 'beginning_of_history' for trend analysis.

The beginning_of_history event marks rows that were already present when the dataset’s tracking window began (October 2023 in the current export). A drug approved in 1997 that was in the October 2023 Orange Book snapshot gets a beginning_of_history event in product_changes — it is emphatically not a “new addition” in 2023.

-- CORRECT: trend analysis, genuine additions only
SELECT ob_release_date, COUNT(*) AS new_products
FROM product_changes
WHERE event = 'added'
GROUP BY ob_release_date ORDER BY ob_release_date;

`ob_release_date` Is the Snapshot Date, Not the Approval Date #

The ob_release_date column records the FDA Orange Book publication date — always the first day of the month (e.g., 2026-03-01). It is not the FDA approval date, the ANDA submission date, or any regulatory action date. It is the date the change first appeared in the published Orange Book.

FDA typically approves drugs throughout the month, but the Orange Book is published once monthly. A drug approved April 15th will appear in the May 1st release as an added event.

`updated` Events Show New Values Only #

When a record changes (patent expiry corrected, TE code updated, sponsor name changed), updated events record only the new values. To see what changed, self-join to the most recent preceding event for the same record key.

This is the most important pattern for patent research: detecting when a patent expiry date changed (a PTE was granted or a court settlement modified the timeline).

SQL Queries #

All changes in the latest OB release

SELECT event, appl_type, appl_no, trade_name, ingredient
FROM product_changes
WHERE ob_release_date = (SELECT MAX(ob_release_date) FROM product_changes)
  AND event != 'beginning_of_history'
ORDER BY event, trade_name;

New NDA and ANDA additions in the last 3 months

SELECT pc.ob_release_date, pc.appl_type, pc.appl_no,
       pc.trade_name, pc.ingredient, pc.approval_date,
       a.sponsor_name
FROM product_changes pc
JOIN applications a ON a.appl_no = pc.appl_no
WHERE pc.event = 'added'
  AND pc.ob_release_date >= date('now', '-3 months')
ORDER BY pc.ob_release_date DESC, pc.appl_type, pc.trade_name;

Month-by-month activity trend (additions, updates, removals)

SELECT ob_release_date,
    SUM(CASE WHEN event = 'added'   THEN 1 ELSE 0 END) AS added,
    SUM(CASE WHEN event = 'updated' THEN 1 ELSE 0 END) AS updated,
    SUM(CASE WHEN event = 'removed' THEN 1 ELSE 0 END) AS removed
FROM product_changes
WHERE event != 'beginning_of_history'
GROUP BY ob_release_date
ORDER BY ob_release_date DESC;

Patent expiry date changes: extensions and corrections

Query for LOE monitoring. When patent_expire_date changes in an updated event, it can signal a newly granted Patent Term Extension, a court settlement, or an FDA database correction.

SELECT curr.ob_release_date, curr.appl_no, curr.patent_no, curr.use_code,
       prev.patent_expire_date AS old_expire_date,
       curr.patent_expire_date AS new_expire_date,
       a.sponsor_name,
       MIN(p.trade_name) AS trade_name
FROM patent_changes curr
JOIN patent_changes prev
  ON  prev.appl_no        = curr.appl_no
  AND prev.product_no     = curr.product_no
  AND prev.patent_no      = curr.patent_no
  AND prev.use_code       = curr.use_code
  AND prev.pediatric_excl = curr.pediatric_excl
  AND prev.id = (
      SELECT MAX(id) FROM patent_changes
      WHERE appl_no        = curr.appl_no
        AND product_no     = curr.product_no
        AND patent_no      = curr.patent_no
        AND use_code       = curr.use_code
        AND pediatric_excl = curr.pediatric_excl
        AND id             < curr.id
  )
JOIN applications a ON a.appl_no = curr.appl_no
JOIN products p     ON p.appl_no = curr.appl_no AND p.product_no = curr.product_no
WHERE curr.event = 'updated'
  AND curr.patent_expire_date != prev.patent_expire_date
GROUP BY curr.appl_no, curr.patent_no, curr.use_code, curr.pediatric_excl,
         curr.ob_release_date, prev.patent_expire_date, curr.patent_expire_date
ORDER BY curr.ob_release_date DESC;

SELECT curr.ob_release_date AS changed_on, curr.appl_no, curr.appl_type,
       prev_ev.sponsor_name AS old_name,
       curr.sponsor_name    AS new_name
FROM application_changes curr
JOIN application_changes prev_ev ON prev_ev.id = (
    SELECT MAX(id) FROM application_changes
    WHERE appl_no = curr.appl_no
      AND id < curr.id
      AND event IN ('beginning_of_history', 'added', 'updated')
)
WHERE curr.event = 'updated'
  AND curr.sponsor_name IS NOT NULL
  AND prev_ev.sponsor_name IS NOT NULL
  AND curr.sponsor_name != prev_ev.sponsor_name
ORDER BY curr.ob_release_date DESC;

Point-in-time snapshot for a single application

What products were in the OB for NDA 021446 as of a given date? Logic: include rows with added or beginning_of_history on or before the target date, exclude any subsequently removed.

SELECT DISTINCT pc.appl_no, pc.trade_name, pc.ingredient,
                pc.dosage_form_route, pc.product_no
FROM product_changes pc
WHERE pc.appl_no = '021446'
  AND pc.ob_release_date <= '2025-06-01'
  AND pc.event IN ('beginning_of_history', 'added', 'updated')
  AND NOT EXISTS (
      SELECT 1 FROM product_changes rm
      WHERE rm.appl_no    = pc.appl_no
        AND rm.product_no = pc.product_no
        AND rm.event      = 'removed'
        AND rm.ob_release_date <= '2025-06-01'
  )
ORDER BY pc.product_no;

How many rows were in the initial baseline load?

SELECT
    (SELECT history_start FROM export_metadata)                                        AS initial_load_date,
    (SELECT COUNT(*) FROM product_changes     WHERE event = 'beginning_of_history')   AS products_at_baseline,
    (SELECT COUNT(*) FROM patent_changes      WHERE event = 'beginning_of_history')   AS patents_at_baseline,
    (SELECT COUNT(*) FROM exclusivity_changes WHERE event = 'beginning_of_history')   AS exclusivity_at_baseline,
    (SELECT COUNT(*) FROM application_changes WHERE event = 'beginning_of_history')   AS applications_at_baseline;