Several date totals do not require several independent scans of the sales table. Period-to-date totals can share one filtered input and separate conditional sums. The report still needs a precise week definition and an as-of boundary.
Define the Reporting Instant First
Today-to-date, week-to-date, month-to-date, and year-to-date all depend on when the report stops. Use one captured as-of timestamp for every expression. Calling the clock separately can create slightly different boundaries. Decide which business time zone that timestamp represents.
I capture the boundary once and name the week convention explicitly. A Monday week and a Sunday week are both legitimate business choices. They simply produce different totals. The query cannot choose that policy from the reader's language or the server's default settings.
The example uses one local datetime2 timeline. It includes future rows intentionally so the upper bound can prove they are excluded. Use a disposable session and artificial amounts. These inputs explain semantics rather than claim real sales results.
CREATE TABLE #PeriodSales
(SaleID int PRIMARY KEY,OrderDate datetime2(0) NOT NULL,
Amount decimal(12,2) NOT NULL);
INSERT #PeriodSales VALUES
(1,'2026-01-02T10:00:00',10),(2,'2026-09-01T10:00:00',20),
(3,'2026-09-21T10:00:00',30),(4,'2026-09-26T09:00:00',40),
(5,'2026-09-26T15:00:00',50),(6,'2026-10-01T10:00:00',60);
CREATE INDEX IX_PeriodSales_Date ON #PeriodSales(OrderDate) INCLUDE(Amount);Compute Period Starts for Period-to-Date Totals
SQL Server 2022 introduced DATETRUNC. Use day, month, and year for their natural starts. Use iso_week for a Monday-start week independent of DATEFIRST. These starts inherit the input type and represent boundaries, not formatted display labels.
DECLARE @AsOf datetime2(0)='2026-09-26T12:00:00';
DECLARE @TodayStart datetime2(0)=DATETRUNC(day,@AsOf),
@WeekStart datetime2(0)=DATETRUNC(iso_week,@AsOf),
@MonthStart datetime2(0)=DATETRUNC(month,@AsOf),
@YearStart datetime2(0)=DATETRUNC(year,@AsOf);
SELECT @AsOf AS AsOfTime,@TodayStart AS TodayStart,
@WeekStart AS WeekStart,@MonthStart AS MonthStart,@YearStart AS YearStart;A Monday week can begin in the preceding calendar year during early January. Therefore the earliest required boundary is not always YearStart. Compute the minimum of all selected period starts instead of assuming the year start includes every requested row.
The upper bound is exclusive in this report. Rows exactly at @AsOf are outside the captured interval. Choose and document that convention. A complete-through-today report would instead use tomorrow's start as its exclusive end, which is a different question from now-to-date.
Use One Sargable Filter for Period-to-Date Totals
Keep OrderDate unwrapped in the WHERE predicate. Calculate the boundary variables first, then compare the original column with them. The conditional sums decide which already-qualified rows contribute to each period. They do not require separate scans by themselves.
DECLARE @AsOf datetime2(0)='2026-09-26T12:00:00';
DECLARE @TodayStart datetime2(0)=DATETRUNC(day,@AsOf),
@WeekStart datetime2(0)=DATETRUNC(iso_week,@AsOf),
@MonthStart datetime2(0)=DATETRUNC(month,@AsOf),
@YearStart datetime2(0)=DATETRUNC(year,@AsOf),@Earliest datetime2(0);
SELECT @Earliest=MIN(v) FROM
(VALUES(@TodayStart),(@WeekStart),(@MonthStart),(@YearStart)) d(v);
SELECT COALESCE(SUM(CASE WHEN OrderDate>=@TodayStart THEN Amount ELSE 0 END),0)
AS TodayTotal,
COALESCE(SUM(CASE WHEN OrderDate>=@WeekStart THEN Amount ELSE 0 END),0)
AS WeekTotal,
COALESCE(SUM(CASE WHEN OrderDate>=@MonthStart THEN Amount ELSE 0 END),0)
AS MonthTotal,
COALESCE(SUM(CASE WHEN OrderDate>=@YearStart THEN Amount ELSE 0 END),0)
AS YearTotal
FROM #PeriodSales WHERE OrderDate>=@Earliest AND OrderDate<@AsOf;
GOCOALESCE returns zero for an empty input under this report's policy. That does not mean zero always represents missing business data correctly. A reporting pipeline can require a separate completeness flag when a source load has not finished. Keep that status distinct from a legitimate zero-sales result.
With the sample rows, my run returned 40.00, 70.00, 90.00, and 100.00. The 15:00 sale and the October sale fall after the noon cutoff, so neither one counts.
Demonstrate DATEFIRST's Week Effect
DATETRUNC(week) uses the session's first-day setting. Compare it with iso_week under two DATEFIRST values. Preserve and restore the original setting so the demonstration does not alter later work in the same connection. My run returned September 20 for the Sunday week and September 21 for every Monday result.
DECLARE @SavedDateFirst int=@@DATEFIRST;
DECLARE @d datetime2(0)='2026-09-26T12:00:00';
SET DATEFIRST 7;
SELECT DATETRUNC(week,@d) AS SundayWeek,DATETRUNC(iso_week,@d) AS MondayWeek;
SET DATEFIRST 1;
SELECT DATETRUNC(week,@d) AS SessionMondayWeek,DATETRUNC(iso_week,@d) AS MondayWeek;
SET DATEFIRST @SavedDateFirst;
GOA connection pool can reuse session state. Relying on an assumed DATEFIRST can therefore make a report change without any source-data change. Prefer an explicit policy in the expression or set the policy deliberately in the reporting boundary.
Provide Equivalent Older Date Arithmetic
For earlier SQL Server versions, DATEADD and DATEDIFF can compute the same starts. The Monday calculation uses a fixed known Monday and normalized modulo. It does not depend on DATEPART weekday or DATEFIRST. Use these variables in the same conditional aggregation pattern.
DECLARE @AsOf datetime2(0)='2026-09-26T12:00:00';
DECLARE @Date date=CONVERT(date,@AsOf),@Anchor date='19000101';
DECLARE @TodayStart datetime2(0)=CONVERT(datetime2(0),@Date),
@WeekStart datetime2(0),@MonthStart datetime2(0),@YearStart datetime2(0);
SET @WeekStart=DATEADD(day,-((DATEDIFF(day,@Anchor,@Date)%7+7)%7),@TodayStart);
SET @MonthStart=DATEADD(month,DATEDIFF(month,@Anchor,@Date),
CONVERT(datetime2(0),@Anchor));
SET @YearStart=DATEADD(year,DATEDIFF(year,@Anchor,@Date),
CONVERT(datetime2(0),@Anchor));
SELECT @TodayStart AS TodayStart,@WeekStart AS WeekStart,
@MonthStart AS MonthStart,@YearStart AS YearStart;
GODo not replace the fixed anchor with a timestamp carrying a non-midnight time. That would shift the derived boundaries. Compare the older expressions with DATETRUNC in a current test environment across year ends and leap days before maintaining two implementations.
Verify Period-to-Date Totals and the Plan Separately
I inspect the actual plan to confirm the shared input shape. SQL expresses one input relation here, but the optimizer still chooses the physical access method. The date index can support a range seek when it is useful. A broad year-to-date request can reasonably favor a scan.
Use STATISTICS IO to compare the whole report with any separate-query baseline. Keep its as-of value, filters, and sums identical. Which repeated read work disappears without changing the period definitions? A prettier query is only useful when the results and operational cost both support it.
Include Boundaries and Business Filters
Test midnight, the first day of a month, the first day of a year, and a week spanning two years. Include future-dated rows and rows exactly at the exclusive upper bound. Those cases expose errors hidden by ordinary midmonth inputs.
Apply currency, tenant, and sale-status filters consistently before aggregation. A refund or canceled order needs the same approved handling in every total. Cross-zone reports need a documented conversion to the business reporting timeline. Four neat totals do not become meaningful until their shared sales definition is correct.
Keep Consistency Across Concurrent Changes
One statement reduces the chance of separate queries observing different moments, but isolation still governs the read. Under a versioned isolation model, the statement can see an appropriate consistent snapshot. Under ordinary locking behavior, review the application's consistency requirement before claiming that every row represents one immutable business instant. The captured as-of timestamp defines the predicate boundary; it does not freeze the source table by itself.
If the report needs an approved reconciled daily snapshot, read the appropriate reporting dataset or use the established transaction design. Do not add broad locking hints merely to make a short example sound consistent. Those hints can block writers and change the operational cost.
Also retain the as-of timestamp in the returned report metadata. A total labeled today looks incomplete when the reader does not know it stopped at noon. A later refresh can legitimately change all four totals. The report should identify its cutoff and load-completeness status so that difference does not become a false discrepancy. This is particularly useful when a cached application page and a freshly executed database report are compared by different readers.
Retain the agreed period definitions beside the report, so a later interface change does not silently redefine its week or cutoff. Period-to-date totals need the same sales definition and cutoff across every output column. Retain those inputs with period-to-date totals so later comparisons use the same reporting contract.
Related reading on this blog: A Walkthrough: DATETRUNC Function in SQL Server and Optimize DATE in WHERE Clause: SQL in Sixty Seconds #189.
Period-to-date reporting is not four unrelated date queries, it is one consistent interval with explicitly defined conditional totals.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
