修改SQL查询解决SSRS校历报表假期运行失效且无法改表问题

这是我之前提出的问题的后续，原问题为Stack Overflow上的《SQL Query to pull date range based on dates in a table》。

我查询的数据库里，dbo.TblSchoolManagementTermDates表存储了学校的学期日期，但没有包含假期的日期范围。这就导致按计划每周一运行的报表如果刚好赶上假期，会因为参数无效运行失败被禁用，我得等开学后重新手动启用报表。

我们没有权限给这个表加额外的行，所以只能修改查询来获取适配的日期。理想状态是假期时报表不用实际出数据但保持启用状态，等下次运行日期落到学期内再正常执行，但SSRS的定时调度没有这么灵活的配置项。

我想到了两种可行的方案：

• 第一种：匹配不到学期日期时，把报表触发当日同时赋值给txtStartDate和txtFinishDate字段，收件人收到无效报表直接删掉邮件就行。
• 第二种：报表统一按学年（每年9月1日到次年8月31日）返回日期范围，自动匹配正确年份，比如2022年1月1日运行报表时，返回开始日期2021年9月1日，结束日期2022年8月31日。

我现在用的SQL查询如下：

SELECT
    CONVERT(varchar(17), MIN(txtStartDate), 111) AS start_date,
    CONVERT(varchar(17), MAX(txtFinishDate), 111) AS end_date
FROM  TblSchoolManagementTermDates
WHERE (CONVERT(date, GETDATE()) BETWEEN txtStartDate AND txtFinishDate)

欢迎提供符合需求的修改方案，也可以提出其他可行思路，提前感谢。

Stuart

附相关说明：

• 学期日期表结构可参考截图：
• 当前查询当日运行输出结果参考：
• 补充规则：每学年分3个学期，时段为9月1日至次年8月31日，可直接用该时段作为学年统计范围。两种方案的具体要求：第一种是查询不到匹配的学期日期时，直接返回报表运行当日作为起止日期；第二种是无论运行日期是否在学期内，都返回当前所属学年的起止日期，即2021年9月1日至2022年8月31日之间的任意日期运行，都返回2021-09-01为开始日期、2022-08-31为结束日期，规则需每年动态生效。

下面分别提供两种方案的实现代码，可根据实际需求选择：

方案1：无匹配学期时返回当日作为起止日期 #

用COALESCE处理空值，当原查询没有返回结果时，自动填充当前日期作为起止日期，改动最小，完全兼容原有逻辑：

SELECT
    COALESCE(CONVERT(varchar(17), MIN(txtStartDate), 111), CONVERT(varchar(17), GETDATE(), 111)) AS start_date,
    COALESCE(CONVERT(varchar(17), MAX(txtFinishDate), 111), CONVERT(varchar(17), GETDATE(), 111)) AS end_date
FROM  TblSchoolManagementTermDates
WHERE (CONVERT(date, GETDATE()) BETWEEN txtStartDate AND txtFinishDate)

假期时返回起止日期相同的结果，不会触发SSRS参数校验错误，报表不会被禁用。

方案2：始终返回当前所属学年的起止日期 #

先动态计算当前日期对应的学年度起止时间，不管是否在学期内都返回固定的学年范围：

DECLARE @CurrentDate DATE = GETDATE()
DECLARE @AcademicStart DATE, @AcademicEnd DATE

-- 动态计算学年开始日期：如果当前月份>=9，学年从今年9月1日开始，否则从去年9月1日开始
SET @AcademicStart = CASE WHEN MONTH(@CurrentDate) >=9 
                          THEN DATEFROMPARTS(YEAR(@CurrentDate),9,1)
                          ELSE DATEFROMPARTS(YEAR(@CurrentDate)-1,9,1)
                     END
-- 学年结束日期为开始日期加1年减1天
SET @AcademicEnd = DATEADD(DAY, -1, DATEADD(YEAR, 1, @AcademicStart))

SELECT
    CONVERT(varchar(17), @AcademicStart, 111) AS start_date,
    CONVERT(varchar(17), @AcademicEnd, 111) AS end_date

如果需要优先取当前所在学期的起止，没有匹配项再返回学年范围，可使用合并版本：

DECLARE @CurrentDate DATE = GETDATE()
DECLARE @TermStart DATE, @TermEnd DATE
DECLARE @AcademicStart DATE, @AcademicEnd DATE

-- 计算学年起止
SET @AcademicStart = CASE WHEN MONTH(@CurrentDate) >=9 
                          THEN DATEFROMPARTS(YEAR(@CurrentDate),9,1)
                          ELSE DATEFROMPARTS(YEAR(@CurrentDate)-1,9,1)
                     END
SET @AcademicEnd = DATEADD(DAY, -1, DATEADD(YEAR, 1, @AcademicStart))

-- 查询当前学期起止
SELECT 
    @TermStart = MIN(txtStartDate),
    @TermEnd = MAX(txtFinishDate)
FROM TblSchoolManagementTermDates
WHERE @CurrentDate BETWEEN txtStartDate AND txtFinishDate

-- 优先返回学期范围，无匹配则返回学年范围
SELECT
    CONVERT(varchar(17), COALESCE(@TermStart, @AcademicStart), 111) AS start_date,
    CONVERT(varchar(17), COALESCE(@TermEnd, @AcademicEnd), 111) AS end_date

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