SQL for Revenue Cycle Analysts: Denials, A/R, and Underpayment Queries

SQL for revenue cycle analysts answers one question over and over: where is the money stuck, and why? Charges, claims, remittances, and adjustments live in separate tables with separate owners. This article shows the query patterns that connect them: days in A/R, charge lag, denial analysis, clean claim rate, and underpayment detection.

I have worked with patient accounting and revenue integrity teams on hospital and physician billing projects. The call usually came when a KPI moved and nobody could say why. The schema below is generic, with Epic mapping hints where I am confident. Names vary by site and version, so confirm them in your data dictionary. Your finance leaders own the metric definitions.

Revenue Cycle KPIs and the Definitions That Cause Arguments

Every revenue cycle meeting argues about a number. The argument is almost always about the definition. Agree on these seven before you write any SQL.

The Healthcare Financial Management Association publishes MAP Keys that standardize many of these metrics. Whether you adopt them or your own, write the definition beside the number. Use the KPI definition sheet template later in this article to capture each decision.

Hospital and professional billing are different ledgers

Hospital billing (facility) and professional billing (physician) run on different account structures, even in one organization. A hospital account may bundle a whole stay into one claim. A professional claim covers a provider’s services on a date. Never sum the two without labeling them, because contractual rules and denial patterns differ.

Epic stores the two in separate transaction structures, so queries for one rarely work on the other. Check which side your question belongs to before you begin.

The Revenue Cycle Data Flow: Where to Measure

Revenue cycle work splits into three stages. Each has its own tables, owners, and KPIs. A problem found late is expensive, so measure early where you can.

Front end
Scheduling, registration,
eligibility, authorization
KPIs
Registration error rate
Authorization on file
Point-of-service cash

Mid cycle
Charge capture, coding,
documentation, claim build
KPIs
Charge lag, DNFB days
Coding accuracy
Clean claim rate

Back end
Submission, adjudication,
payment, denials, collections
KPIs
Denial rate, days in A/R
Net collection rate
Underpayment recovery

Tie-out: account balances, payments, and adjustments must reconcile to the general ledger
Front-end defects surface as back-end denials. Query the earliest stage that can show the cause.

Denials are a back-end symptom of front-end and mid-cycle causes. A missing authorization shows up as a denial three weeks later. Trace each denial category to its origin stage before you recommend a fix.

A Reference Schema for Revenue Cycle Queries

Revenue cycle databases reduce to six structures. The generic names below keep the examples portable. The Epic column gives my best mapping, and you should verify each against your Clarity Data Dictionary.

Remittance and denial detail is the most site-specific part. Many organizations load 835 files into a reporting table through their clearinghouse or billing tool. The Epic Clarity SQL guide covers core encounter tables. The PAT_ENC table guide shows how an encounter links to the hospital account that carries the charges.

Sign conventions need a decision at the start. In most billing systems, charges are positive, while payments and adjustments are negative against the account balance. Confirm the convention in your tables, because one flipped sign inverts every collection metric.

Eleven SQL Patterns for Revenue Cycle Analysts

All patterns use SQL Server syntax and the generic schema. Amounts follow the convention that charges are positive and payments and adjustments are negative against the balance.

Pattern 1: Days in A/R with an explicit revenue window

DECLARE @as_of date = '2026-09-30';

WITH ar AS (
    SELECT SUM(t.tx_amount) AS net_ar
    FROM   rc_transactions t
    WHERE  t.post_date <= @as_of),
rev AS (
    SELECT SUM(t.tx_amount) / 90.0 AS avg_daily_net_revenue
    FROM   rc_transactions t
    WHERE  t.tx_type IN ('CHARGE', 'CONTRACTUAL_ADJ')       -- net revenue = charges less contractuals
      AND  t.post_date >  DATEADD(day, -90, @as_of)
      AND  t.post_date <= @as_of)
SELECT  ar.net_ar, rev.avg_daily_net_revenue,
        CAST(ar.net_ar / NULLIF(rev.avg_daily_net_revenue, 0) AS decimal(9,1)) AS days_in_ar
FROM    ar CROSS JOIN rev;

Name the window in the report title: “Days in A/R (trailing 90 days, net).” A 30-day window swings with seasonality. A fiscal year-to-date window lags a sudden change. Tie the A/R balance to the ledger control account, as shown in the financial reporting queries article.

Pattern 2: A/R aging by payer

SELECT  p.payer_name,
        SUM(CASE WHEN DATEDIFF(day, a.first_bill_date, @as_of) <= 30  THEN a.balance ELSE 0 END) AS d0_30,
        SUM(CASE WHEN DATEDIFF(day, a.first_bill_date, @as_of) BETWEEN 31 AND 60  THEN a.balance ELSE 0 END) AS d31_60,
        SUM(CASE WHEN DATEDIFF(day, a.first_bill_date, @as_of) BETWEEN 61 AND 90  THEN a.balance ELSE 0 END) AS d61_90,
        SUM(CASE WHEN DATEDIFF(day, a.first_bill_date, @as_of) BETWEEN 91 AND 120 THEN a.balance ELSE 0 END) AS d91_120,
        SUM(CASE WHEN DATEDIFF(day, a.first_bill_date, @as_of) > 120 THEN a.balance ELSE 0 END) AS d120_plus,
        SUM(a.balance) AS total_ar
FROM    rc_accounts a
JOIN    rc_payers p ON p.payer_id = a.primary_payer_id
WHERE   a.balance <> 0
GROUP BY p.payer_name
ORDER BY total_ar DESC;

Age from the first bill date, not the service date, when your question is payer performance. Age from the service date when the question is timely filing. The two answer different questions, and mixing them misleads.

Pattern 3: Charge lag, with the tail

WITH l AS (
    SELECT department_id, DATEDIFF(day, service_date, post_date) AS lag_days
    FROM   rc_transactions
    WHERE  tx_type = 'CHARGE' AND post_date >= '2026-07-01' AND post_date < '2026-10-01')
SELECT DISTINCT department_id,
       COUNT(*)      OVER (PARTITION BY department_id)                                   AS charges,
       AVG(lag_days * 1.0) OVER (PARTITION BY department_id)                             AS avg_lag_days,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lag_days) OVER (PARTITION BY department_id) AS median_lag_days,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY lag_days) OVER (PARTITION BY department_id) AS p90_lag_days
FROM   l
ORDER BY p90_lag_days DESC;

SELECT DISTINCT collapses the window results to one row per department. Report the tail. A department with a two-day average and a 20-day 90th percentile has a problem the average hides.

Pattern 4: Discharged, not final billed (DNFB)

SELECT  a.account_id, a.discharge_date,
        DATEDIFF(day, a.discharge_date, @as_of) AS days_since_discharge,
        a.hold_reason, SUM(t.tx_amount) AS unbilled_charges
FROM    rc_accounts a
JOIN    rc_transactions t ON t.account_id = a.account_id AND t.tx_type = 'CHARGE'
WHERE   a.discharge_date IS NOT NULL
  AND   a.final_bill_date IS NULL
GROUP BY a.account_id, a.discharge_date, a.hold_reason
ORDER BY unbilled_charges DESC;

Group by hold reason. Coding backlog, missing documentation, and pending authorization need different owners. A single DNFB total tells the CFO there is a problem and tells nobody what to fix.

Pattern 5: Clean claim and first-pass rate

SELECT  FORMAT(c.submitted_date, 'yyyy-MM') AS submit_month,
        COUNT(*) AS claims,
        SUM(CASE WHEN c.rejected_at_clearinghouse = 0 THEN 1 ELSE 0 END)                         AS accepted,
        SUM(CASE WHEN c.rejected_at_clearinghouse = 0 AND c.first_remit_paid = 1 THEN 1 ELSE 0 END) AS first_pass_paid,
        CAST(100.0 * SUM(CASE WHEN c.rejected_at_clearinghouse = 0 THEN 1 ELSE 0 END) / COUNT(*) AS decimal(5,1)) AS clean_claim_pct,
        CAST(100.0 * SUM(CASE WHEN c.rejected_at_clearinghouse = 0 AND c.first_remit_paid = 1 THEN 1 ELSE 0 END) / COUNT(*) AS decimal(5,1)) AS first_pass_pct
FROM    rc_claims c
WHERE   c.is_original_submission = 1
GROUP BY FORMAT(c.submitted_date, 'yyyy-MM')
ORDER BY submit_month;

Filter to original submissions. Including corrected and rebilled claims inflates the clean rate, because a claim fixed after rejection looks like a success the second time.

Pattern 6: Denial Pareto by reason code

Denial reason codes come from the remittance advice. The X12 Claim Adjustment Reason Codes list maintained by X12 defines the standard codes, such as 16 (claim lacks information) and 50 (not medically necessary). Group by code to find the few reasons that drive most of the lost dollars.

WITH d AS (
    SELECT r.carc_code, COUNT(DISTINCT r.claim_id) AS denied_claims, SUM(r.adjustment_amount) AS denied_amount
    FROM   rc_remits r
    WHERE  r.remit_date >= '2026-07-01' AND r.remit_date < '2026-10-01'
      AND  r.adjustment_group = 'CO'                              -- contractual obligation
      AND  r.carc_code NOT IN ('45', '253')                       -- exclude routine contractual write-downs
    GROUP BY r.carc_code)
SELECT  carc_code, denied_claims, denied_amount,
        CAST(100.0 * SUM(denied_amount) OVER (ORDER BY denied_amount DESC
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(denied_amount) OVER () AS decimal(5,1)) AS cumulative_pct
FROM    d
ORDER BY denied_amount DESC;

Exclude codes that describe expected contractual write-downs. Code 45, charges exceeding the fee schedule, is a normal adjustment and not a denial. Your revenue integrity team should confirm which codes count as denials at your organization.

Pattern 7: Net collection rate

SELECT  FORMAT(a.discharge_date, 'yyyy-MM') AS cohort_month,
        SUM(CASE WHEN t.tx_type = 'CHARGE'          THEN  t.tx_amount ELSE 0 END) AS gross_charges,
        SUM(CASE WHEN t.tx_type = 'CONTRACTUAL_ADJ' THEN -t.tx_amount ELSE 0 END) AS contractual_adj,
        SUM(CASE WHEN t.tx_type = 'PAYMENT'         THEN -t.tx_amount ELSE 0 END) AS payments,
        CAST(100.0 * SUM(CASE WHEN t.tx_type = 'PAYMENT' THEN -t.tx_amount ELSE 0 END)
             / NULLIF(SUM(CASE WHEN t.tx_type = 'CHARGE' THEN t.tx_amount ELSE 0 END)
                    - SUM(CASE WHEN t.tx_type = 'CONTRACTUAL_ADJ' THEN -t.tx_amount ELSE 0 END), 0) AS decimal(5,1)) AS net_collection_pct
FROM    rc_accounts a
JOIN    rc_transactions t ON t.account_id = a.account_id
WHERE   a.discharge_date >= '2026-01-01' AND a.discharge_date < '2026-07-01'
GROUP BY FORMAT(a.discharge_date, 'yyyy-MM');

Group by the discharge or service cohort, not by payment month. A payment-month view mixes old and new accounts, and a billing backlog makes collections look worse than they are. Cohorts older than 120 days give the cleanest read.

Pattern 8: Underpayment detection against contract terms

SELECT  c.claim_id, p.payer_name, c.procedure_code,
        e.expected_amount,
        r.paid_amount + r.patient_resp_amount AS received,
        e.expected_amount - (r.paid_amount + r.patient_resp_amount) AS shortfall
FROM    rc_claims c
JOIN    (SELECT claim_id, SUM(paid_amount) paid_amount, SUM(patient_resp_amount) patient_resp_amount
         FROM rc_remits GROUP BY claim_id) r ON r.claim_id = c.claim_id
JOIN    contract_rates e ON e.payer_id = c.payer_id AND e.procedure_code = c.procedure_code
                        AND c.service_date BETWEEN e.effective_from AND e.effective_to
JOIN    rc_payers p ON p.payer_id = c.payer_id
WHERE   c.service_date >= '2026-07-01'
  AND   e.expected_amount - (r.paid_amount + r.patient_resp_amount) > 5.00      -- tolerance
ORDER BY shortfall DESC;

Match the contract rate effective on the service date, not today’s rate. Add a tolerance so rounding does not flood the work queue. The expected amount must include the patient’s share. Compare it to payer payment plus patient responsibility, or you flag every copay as an underpayment.

Pattern 9: Payer mix by financial class

SELECT  fc.financial_class_name,
        COUNT(DISTINCT a.account_id)  AS accounts,
        SUM(t.tx_amount)              AS gross_charges,
        CAST(100.0 * SUM(t.tx_amount) / SUM(SUM(t.tx_amount)) OVER () AS decimal(5,1)) AS pct_of_charges
FROM    rc_accounts a
JOIN    financial_classes fc ON fc.financial_class_id = a.financial_class_id
JOIN    rc_transactions t    ON t.account_id = a.account_id AND t.tx_type = 'CHARGE'
WHERE   a.discharge_date >= '2026-01-01' AND a.discharge_date < '2026-10-01'
GROUP BY fc.financial_class_name
ORDER BY gross_charges DESC;

Pattern 10: Timely filing risk list

SELECT  a.account_id, p.payer_name, a.service_date,
        p.timely_filing_days,
        DATEADD(day, p.timely_filing_days, a.service_date) AS filing_deadline,
        DATEDIFF(day, @as_of, DATEADD(day, p.timely_filing_days, a.service_date)) AS days_left
FROM    rc_accounts a
JOIN    rc_payers p ON p.payer_id = a.primary_payer_id
WHERE   a.final_bill_date IS NULL
  AND   DATEDIFF(day, @as_of, DATEADD(day, p.timely_filing_days, a.service_date)) BETWEEN 0 AND 30
ORDER BY days_left;

Timely filing limits come from payer contracts and vary widely. Store them in a table you maintain, with an effective date. This list earns its place on the revenue cycle director’s desk every Monday, because a missed deadline turns recoverable revenue into a write-off.

Pattern 11: Credit balances and refund exposure

SELECT  p.payer_name, COUNT(*) AS credit_accounts, SUM(a.balance) AS total_credit
FROM    rc_accounts a
JOIN    rc_payers p ON p.payer_id = a.primary_payer_id
WHERE   a.balance < 0
GROUP BY p.payer_name
ORDER BY total_credit;

A credit balance means the organization holds more than it billed. Net A/R understates gross exposure when credits offset debits. Report credits separately, since refund obligations and state unclaimed property rules apply to them.

Scenario: Denial Rate Climbs from 6.1% to 11.4% After a Build Change

A health system moved outpatient imaging scheduling to a new workflow in August. By mid-September, the revenue cycle director saw the denial rate for outpatient claims rise from 6.1 to 11.4 percent. The IT team asked for evidence before touching the build.

I started with Pattern 6, then split the result by department and by the month the claim was created. The reason code with the sharpest rise was 16, claim lacks information needed for adjudication. Nearly every code 16 denial came from imaging.

SELECT  d.department_name,
        COUNT(DISTINCT c.claim_id) AS claims,
        SUM(CASE WHEN r.carc_code = '16' THEN 1 ELSE 0 END) AS carc16_claims,
        SUM(CASE WHEN r.carc_code = '16' AND c.referring_npi IS NULL THEN 1 ELSE 0 END) AS carc16_blank_npi
FROM    rc_claims c
JOIN    departments d ON d.department_id = c.department_id
LEFT JOIN rc_remits r ON r.claim_id = c.claim_id
WHERE   c.created_date >= '2026-08-15' AND c.created_date < '2026-10-01'
GROUP BY d.department_name
HAVING  SUM(CASE WHEN r.carc_code = '16' THEN 1 ELSE 0 END) > 0
ORDER BY carc16_claims DESC;

The pattern was clear: 1,806 of 1,862 imaging denials had no referring NPI. The build team made the field required for self-scheduled imaging and added a pre-bill edit. Denials for that code returned to baseline within five weeks. Reworking the 1,862 claims took revenue integrity staff most of a month, which is the real cost of the defect.

A related lesson: tie every denial initiative to the earliest stage that can prevent it. A rework team can recover claims. Only a front-end edit can stop the next 1,800.

Scenario: Underpayments Hidden Inside Routine Contractual Adjustments

A hospital’s patient accounting system auto-adjusted any payment shortfall under $50 as a contractual write-down. The team assumed the payer’s fee schedule drove every shortfall. A contract analyst suspected that one Medicare Advantage plan was paying below its contracted rate.

I ran Pattern 8 on six months of claims for the plan, joined to the contract rate table with effective dates. The shortfall was small per claim and consistent. Individually, none of them crossed the work-queue threshold.

Total shortfall reached $74,420, and most of it sat below the write-down limit. The pattern pointed to a rate table loaded with last year’s conversion factor. The contracting team confirmed the error, the payer reprocessed the claims, and revenue cycle added a monthly underpayment report to the close checklist.

Two cautions apply. Contract terms include carve-outs, stop-loss rules, and multiple procedure reductions that a simple rate lookup cannot model. Treat Pattern 8 output as leads for a contract analyst, not as proof. And confirm the expected amount against the payer’s own explanation of payment before you dispute anything.

Decision Tree: Where Is the Cash Stuck?

When A/R rises or collections fall, walk the stages in order. Stop at the first stage that explains the movement.

A/R up or collections down
Locate the stage before proposing a fix

1. Are accounts unbilled? (DNFB, charge lag)
Patterns 3 and 4. Owner: coding, charge capture

2. Are claims rejected before the payer?
Pattern 5. Owner: billing edits, clearinghouse

3. Are adjudicated claims denied?
Pattern 6. Owner: front end and denial management

4. Are paid claims underpaid?
Pattern 8. Owner: contracting, payer relations

5. Patient balances, credits, timely filing (Patterns 10, 11)

Most of the time I find the cause by stage two or three. Underpayment and credit balances take longer to surface, because no one looks for them until a contract review or audit.

Building the Denial Reference Table

Every denial query depends on a mapping from reason codes to categories. Hard-coded lists inside queries drift, and each analyst keeps a slightly different copy. Build the mapping once, as a governed table.

CREATE TABLE ref.denial_code_map (
    carc_code        VARCHAR(10)   NOT NULL,
    adjustment_group VARCHAR(2)    NOT NULL,      -- CO, PR, OA, PI
    is_denial        BIT           NOT NULL,      -- 0 for routine contractual write-downs
    category         VARCHAR(40)   NOT NULL,      -- Registration, Authorization, Coding, Clinical, Payer error
    owner_team       VARCHAR(40)   NOT NULL,
    effective_from   DATE          NOT NULL,
    effective_to     DATE          NULL,
    CONSTRAINT pk_denial_code_map PRIMARY KEY (carc_code, adjustment_group, effective_from)
);

INSERT INTO ref.denial_code_map VALUES
 ('16',  'CO', 1, 'Registration', 'Patient Access',  '2020-01-01', NULL),
 ('50',  'CO', 1, 'Clinical',     'Utilization Review', '2020-01-01', NULL),
 ('197', 'CO', 1, 'Authorization','Patient Access',  '2020-01-01', NULL),
 ('45',  'CO', 0, 'Contractual',  'Contracting',     '2020-01-01', NULL);

The category and owner columns turn a Pareto chart into an action list. The date columns handle changes, such as a payer that begins using a different code for the same problem. Review the table each quarter with revenue integrity, and log each change.

Seed values here are examples. Confirm the category for each code with your denial management team. Some organizations route code 16 to coding, others to registration, depending on which field is missing.

Tracing One Denied Claim from Charge to Remittance

Aggregates tell you where to look. A single traced claim tells you what happened. Pick a denied claim from your Pareto top code and follow it through each stage.

Use a test account or an approved sample of three to five claims. For privacy, work inside the secured reporting environment and record only claim IDs in your notes. The scenario above used this walk on five imaging claims, and all five stopped at step five.

Validating KPI Queries Against the Billing System’s Own Reports

Your SQL becomes trusted when it matches the numbers the billing system or finance already reports. Run the comparison before you publish, and keep the evidence. A table like the one below goes into the test record.

Every difference needs an owner and a reason. Differences with a known cause are not defects. Differences without one stop the release. The figures above are illustrative, and the pattern is real: most gaps trace to a window, a cutoff, or a code list.

Scheduling KPI Rollups Without Rebuilding History Each Night

KPI queries over several years of transactions get slow. Pre-aggregate to a daily rollup table and refresh only recent days. Closed history stays untouched, which also helps reproducibility.

-- Refresh the last 35 days of the rollup, leave older days alone
DELETE FROM rpt.rc_daily_rollup WHERE post_date >= DATEADD(day, -35, CAST(GETDATE() AS date));

INSERT INTO rpt.rc_daily_rollup (post_date, payer_id, tx_type, tx_count, tx_amount)
SELECT  t.post_date, t.payer_id, t.tx_type, COUNT(*), SUM(t.tx_amount)
FROM    rc_transactions t
WHERE   t.post_date >= DATEADD(day, -35, CAST(GETDATE() AS date))
GROUP BY t.post_date, t.payer_id, t.tx_type;

The 35-day window catches late postings and corrections. Choose a window that covers your longest normal posting delay, and run a monthly full reconciliation of the rollup to the source to catch drift. Always record the refresh time in the rollup so reports can show data currency.

Template: KPI Definition Sheet for Business Analysts

Copy this sheet for each KPI before development begins. Two minutes spent on the “trap” rows save weeks of reconciliation later.

Denial work queue specification

The reference table that maps reason codes to denial categories is a business asset. Version it, name an owner, and review it every quarter. The X12 Claim Adjustment Reason Codes list changes over time, and new codes will appear in your remittance files before anyone updates your table.

Role Checklists for Revenue Cycle Analytics

Common Revenue Cycle SQL Mistakes

When I Would Use SQL and When I Would Use Other Tools

I use SQL when I need to prove a cause or reconcile to the ledger. I avoid it for work queues, where the billing system already enforces routing and audit trails. Practice patterns like these on safe data, and see the SQL for IT Analysts hub for related guides.

Edge Cases That Break Clean Revenue Cycle Logic

PHI and Revenue Cycle Data

Account numbers, service dates, and payer IDs identify patients when combined. Use account-level data inside the secured environment, and aggregate before you share. The HIPAA-safe Clarity SQL article covers minimum necessary access, small-cell suppression, and masked views. Standard electronic transactions, which CMS oversees under CMS HIPAA administrative simplification, carry their own security requirements.

Questions Analysts Ask About Revenue Cycle SQL

What is a good days in A/R number?

It depends on payer mix, service lines, and definition. Compare against your own trend and peer data that uses the same formula. A number without its definition has no meaning.

Where do denial reason codes come from?

From the electronic remittance advice, which carries standard claim adjustment reason codes and remark codes. Your billing system or clearinghouse loads them into reporting tables.

Should I use charges or net revenue for KPIs?

Use net revenue for A/R metrics and gross charges for volume and charge lag. State the basis next to the number.

How often should I rebuild denial reports?

Weekly for work queues and monthly for trend analysis. Remittances arrive continuously, so a weekly view catches build defects while the volume is still small.

Run This Before Your Next Revenue Cycle Review

Pick the KPI your leaders discuss most. Write its definition in one sentence on the report itself, then run the Pareto in Pattern 6 for the last quarter. If one reason code drives more than a fifth of the lost dollars, trace it to its origin stage before the meeting. One well-traced code is worth more than a dashboard of averages.

Sources and Further Reading

• X12: Claim Adjustment Reason Codes. Standard code list used in remittance advice.
• CMS: HIPAA Administrative Simplification. Overview of the standard electronic transactions, including claims and remittance.
• HFMA MAP Keys and your payer contracts, for KPI definitions and rate terms. Your organization’s finance policy decides which apply.

Table names are generic, with Epic mapping hints marked as likely. Confirm each in your own data dictionary. Contract and coding rules vary by payer, so involve revenue integrity and contracting before acting on any query result.