SQL Server 2005报表：仅展示指定时段已开发票的付款累计

看起来你需要调整现有报表，只统计那些对应指定时段内开具发票的付款数据，而不是所有付款记录。我来帮你修改这个脚本，同时保留原有的每日和累计统计逻辑：

首先先明确我们的核心调整方向：先锁定指定时段内的发票，然后只统计那些关联到这些发票的付款，同时维持原有的日期范围（11月1日到30日）的每日统计结构。

原数据表结构与数据 #

create table Invoices (InvoiceID int, InvoiceDate date, Total money);
insert into Invoices (InvoiceID, InvoiceDate, Total) values 
(1,'2020-11-01', 20), 
(2,'2020-11-01', 14), 
(3,'2020-11-02', 40), 
(4,'2020-11-02', 35), 
(5,'2020-11-03', 10), 
(6,'2020-11-04', 63), 
(7,'2020-11-04', 42);

create table Payments (InvoiceID int, PaymentDate date, Total money);
insert into Payments (InvoiceID, PaymentDate, Total) values 
(5,'2020-11-07', 10), 
(6,'2020-11-08', 63), 
(4,'2020-11-09', 35), 
(2,'2020-11-10', 14), 
(7,'2020-11-11', 42), 
(11,'2020-11-13', 20), -- 无对应发票，会被过滤
(13,'2020-11-14', 15); -- 无对应发票，会被过滤

修改后的脚本 #

-- 定义指定的发票时段，可根据需求随时调整
DECLARE @StartInvoiceDate DATE = '2020-11-01';
DECLARE @EndInvoiceDate DATE = '2020-11-04';

with DateRange as (
 select convert(date, '2020-11-01') as DateValue
 union all
 select dateadd(day, 1, dr.DateValue) from DateRange dr where dr.DateValue < '2020-11-30'
),
-- 新增CTE：筛选指定时段内的有效发票ID，作为付款过滤的核心依据
ValidInvoices as (
 select InvoiceID from Invoices 
 where InvoiceDate between @StartInvoiceDate and @EndInvoiceDate
),
InvoicedTotal as (
 select dr.DateValue, isnull(sum(i.Total), 0) as Invoiced
 from DateRange dr 
 left join Invoices i on i.InvoiceDate = dr.DateValue
 -- 可选：如果只需要统计指定时段的发票，打开下面的过滤条件
 -- where (i.InvoiceDate between @StartInvoiceDate and @EndInvoiceDate) or i.InvoiceDate is null
 group by dr.DateValue
),
PaidTotal as (
 select dr.DateValue, isnull(sum(p.Total), 0) as Paid
 from DateRange dr 
 -- 左连接日期范围，确保每日都有记录
 left join Payments p on p.PaymentDate = dr.DateValue
 -- 内连接有效发票，只保留对应指定时段发票的付款
 inner join ValidInvoices vi on p.InvoiceID = vi.InvoiceID
 group by dr.DateValue
)
select convert(varchar(10), dr.DateValue, 102) as [YYYY.MM.DD],
 it1.Invoiced as [Invoiced],
 it3.Invoiced as [CumInvoiced],
 pt1.Paid as [Paid],
 pt3.Paid as [CumPaid],
 it3.Invoiced - pt3.Paid as [RunningTotal]
from DateRange dr
join InvoicedTotal it1 on it1.DateValue = dr.DateValue
join PaidTotal pt1 on pt1.DateValue = dr.DateValue
cross apply (
 select sum(it2.Invoiced) as Invoiced from InvoicedTotal it2 where it2.DateValue <= dr.DateValue
) it3
cross apply (
 select sum(pt2.Paid) as Paid from PaidTotal pt2 where pt2.DateValue <= dr.DateValue
) pt3
order by dr.DateValue;

关键修改点说明 #

• 新增ValidInvoicesCTE：专门筛选指定时段内开具的发票ID，作为后续付款过滤的判断标准，确保只统计关联有效发票的付款。
• 调整PaidTotalCTE：通过inner join ValidInvoices过滤付款记录，同时保留left join DateRange保证每日都有统计行（即使当天没有符合条件的付款，也会显示0）。
• 可配置的时段变量：使用@StartInvoiceDate和@EndInvoiceDate变量，方便你快速修改目标发票时段，无需改动核心逻辑。
• 可选的发票统计过滤：如果需要InvoicedTotal也只统计指定时段的发票，可以打开注释中的where条件，进一步缩小发票统计范围。

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