SQL月度考勤报表生成：解决不同月份天数引发的日期无效问题

问题背景 #

需要生成月度考勤报表，输入数据结构如下：

期望输出格式：

注：考勤总数为月度内"yes"的次数

当前通过手动编写CASE语句处理每日数据，示例代码如下：

select user_fullname as usrname,
nvl(
    max(CASE WHEN to_char(datefrom,'dd') = '01' and status = 1 THEN 'yes' else 'no' end)
,'-') as "01",

尝试通过CASE判断月份天数来处理31日列时，出现无效日期错误——原因是SQL会验证所有分支代码，即使分支不会被执行：

case when to_date('01-11-2022','dd-mm-yyyy')-to_date('01-12-2022','dd-mm-yyyy') = 30
then 
nvl(
    max(CASE WHEN to_char(datefrom,'dd') = '31' and status = 1 THEN 'yes' else 'no' end)
,'-') 
else 'X' end as "31",

解决方案 #

方法1：用安全日期函数判断月份天数 #

放弃直接通过日期相减判断天数，改用last_day函数获取当月最后一天，再提取日期数字判断该月是否有31天，避免无效日期计算：

select 
    user_fullname as usrname,
    -- 其他日期列的处理逻辑（如01、02...30）
    case 
        when extract(day from last_day(to_date('01-11-2022','dd-mm-yyyy'))) = 31 
        then nvl(max(case when to_char(datefrom,'dd') = '31' and status = 1 then 'yes' else 'no' end), '-')
        else 'X' 
    end as "31",
    -- 计算月度考勤总数
    sum(case when status = 1 then 1 else 0 end) as "考勤总数"
from your_table
where datefrom between to_date('01-11-2022','dd-mm-yyyy') and last_day(to_date('01-11-2022','dd-mm-yyyy'))
group by user_fullname;

last_day函数能安全返回当月最后一天，不会产生无效日期，只有当该月确实有31天时，分支里的日期判断逻辑才会被实际执行，从根源避免了无效日期验证问题。

方法2：动态SQL适配任意月份天数 #

如果需要适配所有月份的天数差异，可以用动态SQL根据目标月份的实际天数生成对应列：

declare
    v_month date := to_date('01-11-2022','dd-mm-yyyy');
    v_last_day number := extract(day from last_day(v_month));
    v_sql clob;
begin
    v_sql := 'select user_fullname as usrname';
    
    -- 生成当月实际天数对应的列
    for i in 1..v_last_day loop
        v_sql := v_sql || ', nvl(max(case when to_char(datefrom,''dd'') = '''||lpad(i,2,'0')||''' and status = 1 then ''yes'' else ''no'' end), ''-'') as "'||lpad(i,2,'0')||'"';
    end loop;
    
    -- 补全不足31天的日期列为X
    if v_last_day < 31 then
        for i in v_last_day+1..31 loop
            v_sql := v_sql || ', ''X'' as "'||lpad(i,2,'0')||'"';
        end loop;
    end if;
    
    -- 添加考勤总数计算
    v_sql := v_sql || ', sum(case when status = 1 then 1 else 0 end) as "考勤总数" ';
    v_sql := v_sql || 'from your_table ';
    v_sql := v_sql || 'where datefrom between :v_month and last_day(:v_month) ';
    v_sql := v_sql || 'group by user_fullname';
    
    execute immediate v_sql using v_month, v_month;
end;
/

动态SQL会根据目标月份的天数自动生成对应列，不存在无效日期的问题，同时自动补全不足31天的日期列为X，完全适配所有月份。

方法3：调整CASE逻辑避免无效日期触发 #

即使在分支中，也可以通过last_day替代直接判断日期数字，确保逻辑不依赖可能不存在的日期：

case 
    when extract(day from last_day(to_date('01-11-2022','dd-mm-yyyy'))) = 31 
    then nvl(max(case when datefrom = last_day(to_date('01-11-2022','dd-mm-yyyy')) and status = 1 then 'yes' else 'no' end), '-')
    else 'X' 
end as "31",

这里用last_day获取当月最后一天，代替直接判断to_char(datefrom,'dd') = '31'，即使月份没有31天，last_day也会返回有效日期，不会触发无效日期错误。

内容的提问来源于stack exchange，提问作者Fish out of water