li_ning123 (9/3/2012)
Looks like an interesting design. Here is how I would do it:
--Create sample tablecreate table MonthlyData
(
YearMonth nchar(6),
Value decimal(7)
)
insert into MonthlyData
Values
('201207',5000),
('201208',4000)
go
--Query data
;With DaysInMonth as
(
select
YearMonth,
Value,
DATEFROMPARTS(cast(SUBSTRING(yearmonth,1,4) as int),cast(SUBSTRING(yearmonth,5,2) as int),1) as YearMonth_Date
From
MonthlyData
),
DailyData as
(
select
YearMonth,
Value,
YearMonth_Date,
1 as DayCount,
datepart(day,dateadd(month,datediff(month,'2012-01-31',YearMonth_Date),'2012-01-31')) as NumberOfDays,
Value / datepart(day,dateadd(month,datediff(month,'2012-01-31',YearMonth_Date),'2012-01-31')) as DailyNumber
from DaysInMonth
union all
Select
YearMonth,
Value,
YearMonth_Date,
d.DayCount + 1,
NumberOfDays,
d.DailyNumber
From
DailyData d
where
d.DayCount < NumberOfDays
)
select YearMonth,Convert(VARCHAR(10),YearMonth_Date,103) as [Day], DailyNumber as [Value] from dailydata
order by YearMonth, DayCount
Be careful now... That uses a "counting Recursive CTE" and they're notorious for bad performance. Please see the following article for why you shouldn't use "counting Recursive CTEs"...
http://www.sqlservercentral.com/articles/T-SQL/74118/
You've also used DATEFROMPARTS, which I believe is a 2012 function, on a 2008 forum.
--Jeff Moden
Change is inevitable... Change for the better is not.