The arithmetic behind a pharmacy narcotic count, and why the schedule depends on the province

NarcCount — Sun, 25 Oct 2026 21:22:29 +0000

NarcCount makes narcotic reconciliation software for Canadian pharmacies. This post is not a product tour. It walks through the arithmetic every controlled-substance reconciliation rests on, the data problems that make it harder than it looks, and the one parameter that changes from province to province: how often the count has to happen. If you build inventory, health or compliance software, the shape of the problem is worth knowing.

One line of arithmetic

For each drug and strength:

expected = last_count + received + returned_to_stock - dispensed - destroyed
variance = counted - expected

A variance of zero means the shelf matches the records. Everything else in a reconciliation, from the log format to the investigation and the signed record, exists to make sure each of those inputs is complete and dated correctly.

A worked example. One drug and strength has an opening balance of 48 tablets. A wholesaler order adds 100. Dispenses over the period total 56. Six expired tablets are destroyed and recorded. Six tablets come back to stock from a prescription that was filled but never picked up, after the claim is reversed.

48 + 100 + 6 - 56 - 6 = 92

A physical count of 92 agrees. Had the unclaimed prescription gone back on the shelf without the dispense being reversed, the count would still find 92 but the records would expect 86: an overage of 6 with no obvious cause.

The window is the subtle part

"Last count" means the most recent prior count that included this drug, not the last count the pharmacy did. Receipts, dispenses and destructions are summed over the window that runs from the day after that prior count up to and including the current count date. A drug that has never been counted has no baseline, so its first count simply sets the opening balance and reports no variance.

Here is a minimal sketch of that logic in TypeScript. It is an illustration of the rule, not production code:

type Kind = "received" | "returned" | "dispensed" | "destroyed";
type Txn = { drugId: string; date: string; qty: number; kind: Kind };
type Count = { drugId: string; date: string; counted: number };

// Dates are ISO strings (YYYY-MM-DD), so string comparison orders them correctly.
function reconcile(current: Count, priorCounts: Count[], txns: Txn[]) {
  const prior = priorCounts
    .filter(c => c.drugId === current.drugId && c.date < current.date)
    .sort((a, b) => b.date.localeCompare(a.date))[0];

  // First count of this drug: it becomes the opening balance, no variance.
  if (!prior) return { expected: null, variance: null };

  const inWindow = txns.filter(t =>
    t.drugId === current.drugId && t.date > prior.date && t.date <= current.date);
  const sum = (k: Kind) =>
    inWindow.filter(t => t.kind === k).reduce((n, t) => n + t.qty, 0);

  const expected = prior.counted + sum("received") + sum("returned")
    - sum("dispensed") - sum("destroyed");
  return { expected, variance: current.counted - expected };
}

Why balances drift

The formula is trivial. The data feeding it is not. When a count and the records disagree, the cause is usually one of a short list:

Part fills recorded as the full quantity, or the balance of a part fill dispensed without its own entry
Unclaimed prescriptions returned to the shelf without reversing the dispense
Patient returns added back into stock instead of being set aside for destruction
A dispense or receipt recorded under an interchangeable brand's DIN, so one product's balance runs short and the other's runs over
Transactions dated just before or after the count cut-off
Destructions carried out but not entered, which show as a shortage
Unit mismatches, such as a box of 5 patches received and entered as 1
Measuring variance on liquids, which produces small, steady differences rather than whole units

Two of those are data-modelling problems more than counting problems: product identity (which DIN a transaction belongs to) and units (a pack versus the units inside it). Expected on-hand is only as accurate as the transaction records behind it, so an import that gets identity or units wrong produces a variance that nobody on the shelf can explain.

Corrections are rows, not edits

A balance that can be overwritten is a balance with no history. The habits that keep a record trustworthy are the same whether it lives in a spreadsheet or a database: add rows for new transactions instead of editing old ones, record corrections as adjusting rows with a reason, and keep a dated, read-only copy of every count so that record cannot change afterward.

When an investigation explains a variance, the fix is to add the missing or corrected transaction, for example the unentered destruction, so the balance becomes right for a traceable reason. When no cause is found, an adjusting entry that brings the balance to the counted quantity, with a note linking it to the investigation, keeps the evidence. Overwriting the balance to match the shelf erases it.

The parameter that changes by province

Canada has no single national schedule for counting narcotics and controlled drugs in a pharmacy. Each provincial regulator sets its own interval, and that interval is a minimum rather than a timetable:

Nova Scotia: the physical count and its reconciliation are completed and documented monthly.
Prince Edward Island: narcotic and controlled drugs are counted monthly; targeted substances are reconciled at least once every six months.
Alberta, British Columbia, Saskatchewan, Manitoba, and Newfoundland and Labrador: reconciliation is generally required every three months. Alberta's standard also calls for an audit of the perpetual inventory each time there is a change in proprietor or licensee.
Ontario: the Ontario College of Pharmacists expects narcotics and controlled substances to be reconciled at least every six months.

A pharmacy group with locations in two provinces therefore runs two different intervals, and each location follows its own college. In a data model, the interval belongs to the location, not to the account.

Why many pharmacies count more often than the minimum

The difficulty of explaining a variance grows with the number of transactions behind it. Take a pharmacy that dispenses around 40 prescriptions of hydromorphone 2 mg between monthly counts. A shortage of 6 tablets on a monthly count means reviewing about 40 dispenses, a handful of receipts and any destructions. The same shortage found on a count at the Ontario minimum means reviewing roughly six times as many entries. That is why fast-moving products often get spot counts between full counts, while slow-moving items follow the full schedule.

Sources

The intervals above come from each province's college; regulations change, so confirm the current requirement with the source before relying on it. The province-by-province breakdown with its primary sources is in our guide to controlled substance inventory frequency in Canada.

General information, not legal or professional advice.

DEV Community: NarcCount