Break up monthly data into daily

sql-server-2008, t-sql

Solution

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

(There's an implicit conversion from integer to floating point in the budget, as otherwise the daily rate will be rounded to the nearest dollar - this will not be necessary if the budget datatype is already held as something like `numeric(10,2)`.)

(SQLFiddle here)

Problem

I have budget data for a company in the following montly format. SqlFiddle link here. ``` Dept# YearMonth Budget($) -------------------------- 001 201301 100 001 201302 110 001 201303 105 .. ..... ... 002 201301 200 ... ..... ... ``` I am required to break this up into daily records, which would look like this: ``` Dept# Date Budget($) -------------------------- 001 20130101 xxx 001 20130102 xxx 001 20130103 xxx .. ..... ... ``` I need to generate daily records from each record in the source table. I don't want to assume that each month has 30 days. How do I determine the actual number of days for each month and break it up in the format shown above? I appreciate any kind of help. Thanks!

Original source