Compare month-to-date sales fairly across unequal months

Comparing four days of this month's sales with all of last month's sales mixes observation lengths. A month-to-date comparison should define a common cutoff, use complete periods and explain any remaining differences in weekday mix, holidays or trading hours.

Matched elapsed days make the arithmetic more comparable. They do not prove why sales changed, and they do not automatically create a reliable month-end forecast.

Define the report as of a fixed time

Use the advanced SQL lab and a report prepared at the start of April 5, 2026. The eligible current period includes April 1 through April 4. The comparison includes March 1 through March 4. Both use exclusive upper boundaries at the start of day five.

The purchase table stores normalized dates and completed purchase values in paise. For timestamped data, first establish the reporting timezone and whether the current day is fully loaded. Comparing a partial day with a complete prior day adds another avoidable imbalance.

SELECT
    SUM(CASE WHEN purchased_at >= '2026-04-01'
                  AND purchased_at < '2026-04-05'
             THEN amount_paise ELSE 0 END) AS april_mtd_paise,
    SUM(CASE WHEN purchased_at >= '2026-03-01'
                  AND purchased_at < '2026-03-05'
             THEN amount_paise ELSE 0 END) AS march_matched_paise,
    SUM(CASE WHEN purchased_at >= '2026-03-01'
                  AND purchased_at < '2026-04-01'
             THEN amount_paise ELSE 0 END) AS march_full_paise
FROM purchases;

Expect 30,000, 42,000 and 52,000 paise respectively. April has purchases of 5,000, 10,000 and 15,000. The matched March period has 12,000 and 30,000; March 10 contributes another 10,000 to the full month only.

Show both the amount difference and the percentage

WITH periods AS (
    SELECT
        SUM(CASE WHEN purchased_at >= '2026-04-01' AND purchased_at < '2026-04-05'
                 THEN amount_paise ELSE 0 END) AS current_value,
        SUM(CASE WHEN purchased_at >= '2026-03-01' AND purchased_at < '2026-03-05'
                 THEN amount_paise ELSE 0 END) AS previous_value
    FROM purchases
)
SELECT current_value, previous_value,
       current_value - previous_value AS difference_paise,
       100.0 * (current_value - previous_value) / NULLIF(previous_value, 0)
           AS change_pct
FROM periods;

The difference is −12,000 paise and the matched-period change approximately −28.57%. Comparing April's partial 30,000 against March's full 52,000 would produce approximately −42.31%, answering a different and usually less useful progress question.

If the previous value is zero, percentage change is undefined under this formula. Keep the absolute difference and label the percentage unavailable. A missing source period must also remain distinguishable from a confirmed zero-sales period.

Handle short months and trading calendars deliberately

At day 31, the preceding month may not have 31 days. Choose and document a policy: compare through the shorter month's final day, use matched trading-day counts, or compare a fixed trailing interval. Each answers a different question.

A trading-day comparison requires a calendar for the relevant business and region. Excluding weekends is insufficient when holidays, scheduled closures or store-specific operating days matter. Date utilities can construct boundaries, but they cannot decide the business policy for you. See SQLite's date-function reference for its supported date operations.

If stores opened or closed between periods, consider an explicitly defined comparable-store population. Display total-business results separately so that a same-store view does not conceal the contribution of expansion or closures.

Keep explanation separate from measurement

The matched decline does not establish weak demand, a failed campaign or a pricing effect. The small fixture has only a few purchases, so one transaction changes the result substantially. Inspect order count, average order value, returns and source completeness before proposing a commercial explanation.

Save report_as_of, source_loaded_through and comparison boundaries with the output. Those fields let a reviewer reproduce a previously shared result after late records arrive.

Exercise: add a purchase exactly at April 5. Verify it stays outside this report. Then design a matched trading-day comparison and write down how it treats a holiday rather than silently relying on calendar dates.

NeuraPath's Data Analytics with Generative AI course develops reporting skills across SQL and business interpretation. A strong monthly-sales analysis shows its periods and completeness controls as clearly as its percentage change.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL percentiles for skewed delivery times.
• Continue with SQL slowly changing dimensions in a historical customer report.