如何替换SQL语句中的静态日期，基于Transaction Date进行计算？

Hey there! Let's work through replacing that static date with a calculation based on TransactionDate.

First, let's recap what your original query does: it sums TotalValue-TotalPaidtoDate for rows where the number of days between TransactionDate and '2018/05/09' falls between 100 and 600. To make this reference date dynamic and tied to TransactionDate, here are a few common scenarios you might be targeting:

Scenario 1: Use the same month/day, match the year of TransactionDate #

If you want the reference date to be May 9th of the same year as each TransactionDate, build the date using TransactionDate's year value:

SUM(CASE 
    WHEN DATEDIFF(d, TransactionDate, DATEFROMPARTS(YEAR(TransactionDate), 5, 9)) BETWEEN 100 AND 600 
    THEN (TotalValue-TotalPaidtoDate) 
END) AS [30DaysAmount]

This way, every transaction will compare to May 9th of its own year instead of the fixed 2018 date.

Scenario 2: Offset TransactionDate by a fixed number of days #

If you want the reference date to be a set number of days before/after TransactionDate, use DATEADD. For example, if the [30DaysAmount] alias was intentional (your original condition uses 100-600 days, which might be a typo), you could adjust the logic to target a 30-day window:

-- Reference date is 30 days after TransactionDate
SUM(CASE 
    WHEN DATEDIFF(d, TransactionDate, DATEADD(d, 30, TransactionDate)) BETWEEN 0 AND 30 
    THEN (TotalValue-TotalPaidtoDate) 
END) AS [30DaysAmount]

Side note: In this exact example, DATEDIFF will always equal 30, so you’d likely want to tweak the BETWEEN clause to match your actual intended date range.

Scenario 3: Compare to the current date (dynamic, query-run time) #

If you want to use the current date as the reference point (still relative to TransactionDate in terms of day difference), use GETDATE() (for SQL Server) or CURRENT_DATE (for databases like PostgreSQL):

SUM(CASE 
    WHEN DATEDIFF(d, TransactionDate, GETDATE()) BETWEEN 100 AND 600 
    THEN (TotalValue-TotalPaidtoDate) 
END) AS [30DaysAmount]

This will calculate the day gap between TransactionDate and today every time you run the query.

Quick check on your alias #

You named the result [30DaysAmount], but your condition checks for days between 100 and 600. That might be a typo—if you actually want to sum values for transactions within 30 days of the reference date, be sure to adjust the BETWEEN values to match (like BETWEEN 0 AND 30).

内容的提问来源于stack exchange，提问作者sabbegk