如何修改现有SQL查询以获取2016-01-01至2016-12-31时间段的结果

要实现查询2016年全年的业务数据，只需将原代码中动态计算的近一年时间边界，统一替换为2016年的固定起止时间即可，具体修改如下：

修改点说明 #

• 所有动态计算的起始时间DATEADD(month, -12, GETDATE())统一替换为2016年起始日期'2016-01-01'
• 所有作为统计边界的GETDATE()统一替换为2016年结束日期'2016-12-31'
• 修正原代码中两处错误硬编码的'2020-01-01'，替换为'2016-01-01'（原逻辑需要取统计区间起始日的星期几，原硬编码属于逻辑错误，同步修复）

调整后完整代码 #

sum(case 
 when ci.docdate#2 >= '2016-01-01'  and ci.docdate#5 >= '2016-01-01' and ci.docdate#5 <= '2016-12-31' then (DATEDIFF(day,ci.docdate#2,ci.docdate#5)-(DATEDIFF(week,ci.docdate#2,ci.docdate#5)*2))+1-(CASE WHEN DATENAME(weekday, ci.docdate#2) = 'Sunday' THEN 1 ELSE 0 END)
        - (CASE WHEN DATENAME(weekday, ci.docdate#2) = 'Saturday' THEN 1 ELSE 0 END)
 when ci.docdate#2 >= '2016-01-01'   and (ci.docdate#5 > '2016-12-31' or year(ci.docdate#5) <2000) then (DATEDIFF(day,ci.docdate#2,'2016-12-31')-(DATEDIFF(week,ci.docdate#2,'2016-12-31')*2))+1-(CASE WHEN DATENAME(weekday, ci.docdate#2) = 'Sunday' THEN 1 ELSE 0 END)
        - (CASE WHEN DATENAME(weekday, ci.docdate#2) = 'Saturday' THEN 1 ELSE 0 END)
 when ci.docdate#2 < '2016-01-01'  and  ci.docdate#5 >= '2016-01-01' and ci.docdate#5 <= '2016-12-31'  then (DATEDIFF(day,'2016-01-01',ci.docdate#5)-(DATEDIFF(week,'2016-01-01',ci.docdate#5)*2))+1 - (CASE WHEN DATENAME(weekday, '2016-01-01') = 'Sunday' THEN 1 ELSE 0 END)
        - (CASE WHEN DATENAME(weekday, '2016-01-01') = 'Saturday' THEN 1 ELSE 0 END)
 when ci.docdate#2 < '2016-01-01' and (ci.docdate#5 > '2016-12-31' or year(ci.docdate#5) <2000) then (DATEDIFF(day,'2016-01-01','2016-12-31')-(DATEDIFF(week,'2016-01-01','2016-12-31')*2))+1 - (CASE WHEN DATENAME(weekday, '2016-01-01') = 'Sunday' THEN 1 ELSE 0 END)
        - (CASE WHEN DATENAME(weekday, '2016-01-01') = 'Saturday' THEN 1 ELSE 0 END)
 when year(ci.docdate#2) < 1900  and  ci.docdate#5 >= '2016-01-01'  then (DATEDIFF(day,ci.deldate,ci.docdate#5)-(DATEDIFF(week,ci.deldate,ci.docdate#5)*2))+1-(CASE WHEN DATENAME(weekday, ci.deldate) = 'Sunday' THEN 1 ELSE 0 END)
        - (CASE WHEN DATENAME(weekday, ci.deldate) = 'Saturday' THEN 1 ELSE 0 END)
 when year(ci.docdate#2) < 1900  and (ci.docdate#5 > '2016-12-31' or year(ci.docdate#5) <2000) then (DATEDIFF(day,ci.deldate,'2016-12-31')-(DATEDIFF(week,ci.deldate,'2016-12-31')*2))+1-(CASE WHEN DATENAME(weekday, ci.deldate) = 'Sunday' THEN 1 ELSE 0 END)
        - (CASE WHEN DATENAME(weekday, ci.deldate) = 'Saturday' THEN 1 ELSE 0 END)
 else 0
 end) as DaysonHire

内容的提问来源于stack exchange，提问作者nicolás Lautieri