使用行号统计日期范围内行数？动态日期场景技术求助

Got it, let's work through this. Since you already nailed the fixed date range (2018-01-01 to 2018-04-01) and got 7 rows, switching to dynamic dates from a temp table just requires mapping that logic to pull your start/end dates from the temp table instead of hardcoding them.

Scenario 1: Temp table has a single dynamic date range #

If your temp table only holds one set of start/end dates (like you're running a single dynamic query at a time), you can directly subquery the temp table to get your bounds:

-- Replace #DateRanges with your actual temp table name
-- Replace YourPayCycleTable with your main table storing pay cycles
-- Adjust AuthorizationDate to match your actual date column name
SELECT COUNT(*) AS PayCycleRowCount
FROM YourPayCycleTable
WHERE AuthorizationDate BETWEEN 
    (SELECT StartDate FROM #DateRanges)
    AND 
    (SELECT EndDate FROM #DateRanges);

Scenario 2: Temp table has multiple date ranges to count #

If you need to calculate row counts for multiple dynamic date ranges stored in the temp table, use a JOIN with grouping to get results for each range:

SELECT
    dr.StartDate,
    dr.EndDate,
    COUNT(pc.AuthorizationDate) AS PayCycleRowCount
FROM #DateRanges dr
LEFT JOIN YourPayCycleTable pc
    ON pc.AuthorizationDate BETWEEN dr.StartDate AND dr.EndDate
GROUP BY dr.StartDate, dr.EndDate;

Key Notes to Match Your Original Fixed Query #

• Double-check the date boundary logic: BETWEEN includes both the start and end dates. If your original fixed query used something like AuthorizationDate >= '2018-01-01' AND AuthorizationDate < '2018-04-02' (to avoid including midnight of the next day), adjust the join/where clause to match exactly—this ensures you get the same 7-row count when using dynamic dates.
• Make sure the date columns in your temp table and main table use the same data type (e.g., DATE, DATETIME) to avoid unexpected implicit conversion issues.

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