更新时间:2023-02-04 20:25:27
您的业务规则并非 100% 明确.
your business rule is not 100% clear.
我认为正确的结果可能不止一个.
I think there can be more than one correct result.
在一个地方,它是硬编码的,因为我想 100% 确定需求.我没有使用游标.它只有 24 个循环.
In one place it is hard coded,because i want to be 100% sure of requirement. I am not using cursor.Its only 24 loops.
declare @StartYear datetime='2017-01-01'
declare @endYear datetime ='2017-12-31'
declare @gap int =14 --Bimonthly means gap of 14 days or 15 days whatever
;With CTE as
(
select dateadd(day,@gap, @StartYear) Bimonthly
,1 rn
UNION ALL
select
case
when (rn+1)%2=0 and datename(m, Bimonthly)='February' THEN
'2017-02-28'
when (rn+1)%2=0 and datename(m, Bimonthly)!='February'
then dateadd(day,@gap, Bimonthly)
else
dateadd(day,@gap, dateadd(month, datediff(month,0,dateadd(month,1,Bimonthly)),0))
END
,rn+1
from cte
where rn< (datediff(month,@StartYear, @endYear)+1)*2
)
select
case WHEN datename(dw, Bimonthly)='Saturday' THEN
dateadd(day,-1, Bimonthly)
WHEN datename(dw, Bimonthly)='Sunday' THEN
dateadd(day,-2, Bimonthly)
else
Bimonthly
end
,rn
from cte