InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 10:47:04 GMT20http://www.sqlservercentral.com/Forums/Topic1353687-392-1.aspx[quote][b]rka (9/3/2012)[/b][hr]I have got some Monthly Data which I want to pro-rata it on a Daily Basis.[/quote]So... "Enquiring minds want to know"... are you all set now or is there something else?Tue, 04 Sep 2012 22:55:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353687-392-1.aspx[quote][b]dwain.c (9/4/2012)[/b][hr]Just a guess, but if you divide 5000/31 and add that result up 31 times, it won't equal 5000 when you're through.If you need it to, you can consult this article for how you can "fudge round" it so that it does.[url]http://www.sqlservercentral.com/articles/Financial+Rounding/88067/[/url][/quote]Nah... route the unrounded partial penny errors to my bank account so I can buy a red stapler and retire early. :-DTue, 04 Sep 2012 22:52:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353687-392-1.aspxJust a guess, but if you divide 5000/31 and add that result up 31 times, it won't equal 5000 when you're through.If you need it to, you can consult this article for how you can "fudge round" it so that it does.[url]http://www.sqlservercentral.com/articles/Financial+Rounding/88067/[/url]Tue, 04 Sep 2012 19:25:18 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1353687-392-1.aspx[quote][b]li_ning123 (9/3/2012)[/b][hr][quote][b]Jeff Moden (9/3/2012)[/b][hr]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"...[url]http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]You've also used DATEFROMPARTS, which I believe is a 2012 function, on a 2008 forum.[/quote]That's good point, it's going to get a performance hit. Thanks for the info. To avoid this, I would have to use a supplemental table with numbers from 1 to 31, and then do a cross join to manipulate month and day, which will look similiar to your query.[/quote]Also as mentioned by Jeff DATEFROMPARTS is not available in 2008 and the solution will work for the current year 2012 only :-)Mon, 03 Sep 2012 23:43:16 GMTrhythmkhttp://www.sqlservercentral.com/Forums/Topic1353687-392-1.aspx[quote][b]Jeff Moden (9/3/2012)[/b][hr]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"...[url]http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]You've also used DATEFROMPARTS, which I believe is a 2012 function, on a 2008 forum.[/quote]That's good point, it's going to get a performance hit. Thanks for the info. To avoid this, I would have to use a supplemental table with numbers from 1 to 31, and then do a cross join to manipulate month and day, which will look similiar to your query.Mon, 03 Sep 2012 23:02:07 GMTli_ning123http://www.sqlservercentral.com/Forums/Topic1353687-392-1.aspx[quote][b]li_ning123 (9/3/2012)[/b][hr]Looks like an interesting design. Here is how I would do it:[code="sql"]--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 dailydataorder by YearMonth, DayCount[/code][/quote]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"...[url]http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]You've also used DATEFROMPARTS, which I believe is a 2012 function, on a 2008 forum.Mon, 03 Sep 2012 22:35:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353687-392-1.aspxLooks like an interesting design. Here is how I would do it:[code="sql"]--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(CHAR(10),dateadd(day,daycount-1,YearMonth_Date),103) as [Day], DailyNumber as [Value] from dailydataorder by YearMonth, DayCount[/code]Mon, 03 Sep 2012 22:32:50 GMTli_ning123http://www.sqlservercentral.com/Forums/Topic1353687-392-1.aspx[quote][b]rka (9/3/2012)[/b][hr]Has anyone done any similar SQL Script to generate this?[/quote]Quite possibly thousands of times. :-DThe first thing you need is the Swiss Army Knife for T-SQL known as a Tally Table. It has hundreds of uses and this is one of them. Please see the following article for what a Tally Table is and how it can be used to replace certain WHILE loops with incredible performance.[url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]Here's how to build a "unit based" Tally Table.[code="sql"]--===== Do this in a nice safe place that everyone has -- (You can build a permanent one in any database) USE TempDB; IF OBJECT_ID('TempDB..Tally','U') IS NOT NULL DROP TABLE Tally;GO--===================================================================-- Create a Tally table from 1 to 11000--===================================================================--===== Create and populate the Tally table on the fly. SELECT TOP 11000 IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.sys.ALL_Columns ac1 CROSS JOIN Master.sys.ALL_Columns ac2;--===== Add a CLUSTERED Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100;--===== Allow the general public to use it GRANT SELECT ON dbo.Tally TO PUBLIC;GO[/code]After that, the Tally Table makes your problem easy to solve with a little help from some date/time functions and a CROSS JOIN...[code="sql"]--===== Conditionally drop the test table to make reruns in SSMS easier. -- This is just a test table and is not a part of the solution. IF OBJECT_ID('tempdb..#YourTable','U') IS NOT NULL DROP TABLE #YourTable;--===== Create the test table. -- This is just a test table and is not a part of the solution. CREATE TABLE #YourTable ( YearMonth INT, [Value] INT );--===== Populate the table with test data. -- This is just test data and is not a part of the solution. INSERT INTO #YourTable (YearMonth,[Value]) SELECT 201207,5000 UNION ALL SELECT 201208,4000 UNION ALL SELECT 201201,3100 UNION ALL SELECT 200002,2900;--===== Solve the problem. SELECT YearMonth, [Day] = CONVERT(CHAR(10),DATEADD(mm,((YearMonth/100)-1900)*12 + YearMonth%100-1,0)+(t.N-1),103), [Value] = ([Value]+0.0)/DAY(DATEADD(mm,((YearMonth/100)-1900)*12 + YearMonth%100,0)-1) FROM #YourTable CROSS JOIN dbo.Tally t WHERE t.N <= DAY(DATEADD(mm,((YearMonth/100)-1900)*12 + YearMonth%100,0)-1) ORDER BY YearMonth, t.N;[/code]Mon, 03 Sep 2012 22:04:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353687-392-1.aspxI have got some Monthly Data which I want to pro-rata it on a Daily Basis.E.g.The Current Data looks like:[code="other"]YearMonth Value-------- ------201207 5000201208 4000[/code]I want to generate daily records based on the above so that I get the following:[code="other"]YearMonth Day Value--------- ------------ ------201207 01/07/2012 5000 divide by No Of Days in the month i.e 5000/31201207 02/07/2012 5000 divide by No Of Days in the month i.e 5000/31201207 03/07/2012 5000 divide by No Of Days in the month i.e 5000/31201207 04/07/2012 5000 divide by No Of Days in the month i.e 5000/31201207 05/07/2012 5000 divide by No Of Days in the month i.e 5000/31...201208 01/08/2012 4000 divide by No of Days in the month i.e. 4000/31[/code]and so forth...I do understand that the "Value" field will have repeating value. But this is how I wantto produce data. Has anyone done any similar SQL Script to generate this?Mon, 03 Sep 2012 21:17:59 GMTrka