基于另一查询的预定数量，按周统计符合条件的数据库行数

假设你的业务数据表名为DataTable（包含IDS和Date列），存储预定数量的表名为WeeklyQuota（包含WeekStart（每周起始日期，与你的DayStart对应）和QuotaCount（每周预定数量）列），可以通过以下SQL完成统计对比：

declare @STARTDATE date;
declare @ENDDATE date;

set @STARTDATE = '2021-11-29';
set @ENDDATE = '2022-03-01';

with Nums as
(
    select 1 as NN
    union all
    select NN + 1 as NN
    from Nums
    where NN < 1000
),
WeeklyRanges as (
    select  
        dateadd(dd, NN, @STARTDATE) as DayStart, 
        dateadd(dd, NN + 6, @STARTDATE) as DayEnd
    from 
        Nums
    where 
        dateadd(dd, NN + 6, @STARTDATE) <= @ENDDATE
        and datepart(dw, dateadd(dd, NN, @STARTDATE)) = 1 
),
WeeklyCounts as (
    select
        wr.DayStart,
        wr.DayEnd,
        count(dt.IDS) as ActualCount
    from WeeklyRanges wr
    left join DataTable dt 
        on dt.Date between wr.DayStart and wr.DayEnd
        -- 这里可以添加其他过滤条件，比如：and dt.[其他条件] = 某个值
    group by wr.DayStart, wr.DayEnd
)
select
    wc.DayStart,
    wc.DayEnd,
    wc.ActualCount,
    wq.QuotaCount,
    case 
        when wc.ActualCount >= wq.QuotaCount then '达标'
        else '未达标'
    end as Status
from WeeklyCounts wc
left join WeeklyQuota wq 
    on wc.DayStart = wq.WeekStart
order by wc.DayStart;
option (maxrecursion 0)

关键说明： #

• WeeklyRanges：复用你原有的逻辑生成每周的起始和结束日期，确保只包含指定范围内的完整周
• WeeklyCounts：关联业务表统计每周的有效行数，left join保证即使某周没有数据也会显示（实际数量为0）
• 统计对比：通过case表达式判断实际数量是否达到预定值，输出明确的状态
• 自定义调整：
  • 如果WeeklyQuota是按周编号而非起始日期关联，可在WeeklyRanges中添加datepart(week, DayStart) as WeekNumber，再用周编号关联
  • 可在DataTable的关联条件中添加业务相关的过滤规则（比如特定IDS范围、状态等）

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