Try:
with cte as
(select [dept#], [YearMonth], convert(datetime,[YearMonth]+'01',112) [Date], [Budget($)]
from budget
union all
select [dept#], [YearMonth], dateadd(d, 1, [Date]) [Date], [Budget($)]
from cte
where datediff(m,[Date],dateadd(d, 1, [Date]))=0
)
select [dept#], [Date],
1.0*[Budget($)] / count(*) over (partition by [dept#], [YearMonth]) [DailyBudget($)]
from cte
order by 1,2
(预算中存在从整数到浮点的隐式转换,否则每日费率将四舍五入到最接近的美元 - 如果预算数据类型已经保存为类似numeric(10,2)
.)
(SQLFiddlehere http://sqlfiddle.com/#!3/09263/16)