September 3, 2012 at 9:18 pm
I have got some Monthly Data which I want to pro-rata it on a Daily Basis.
E.g.
The Current Data looks like:
YearMonthValue
--------------
2012075000
2012084000
I want to generate daily records based on the above so that I get the following:
YearMonthDayValue
---------------------------
20120701/07/20125000 divide by No Of Days in the month i.e 5000/31
20120702/07/20125000 divide by No Of Days in the month i.e 5000/31
20120703/07/20125000 divide by No Of Days in the month i.e 5000/31
20120704/07/20125000 divide by No Of Days in the month i.e 5000/31
20120705/07/20125000 divide by No Of Days in the month i.e 5000/31
...
20120801/08/20124000 divide by No of Days in the month i.e. 4000/31
and so forth...
I do understand that the "Value" field will have repeating value. But this is how I want
to produce data. Has anyone done any similar SQL Script to generate this?
September 3, 2012 at 10:04 pm
rka (9/3/2012)
Has anyone done any similar SQL Script to generate this?
Quite possibly thousands of times. 😀
The 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.
http://www.sqlservercentral.com/articles/T-SQL/62867/
Here's how to build a "unit based" Tally Table.
--===== 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
After that, the Tally Table makes your problem easy to solve with a little help from some date/time functions and a CROSS JOIN...
--===== 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
;
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2012 at 10:32 pm
Looks like an interesting design. Here is how I would do it:
--Create sample table
create 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 dailydata
order by YearMonth, DayCount
September 3, 2012 at 10:35 pm
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.
September 3, 2012 at 11:02 pm
Jeff Moden (9/3/2012)
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.
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.
September 3, 2012 at 11:43 pm
li_ning123 (9/3/2012)
Jeff Moden (9/3/2012)
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.
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.
Also as mentioned by Jeff DATEFROMPARTS is not available in 2008 and the solution will work for the current year 2012 only 🙂
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
September 4, 2012 at 7:25 pm
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.
http://www.sqlservercentral.com/articles/Financial+Rounding/88067/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 4, 2012 at 10:52 pm
dwain.c (9/4/2012)
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.
http://www.sqlservercentral.com/articles/Financial+Rounding/88067/
Nah... route the unrounded partial penny errors to my bank account so I can buy a red stapler and retire early. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2012 at 10:55 pm
rka (9/3/2012)
I have got some Monthly Data which I want to pro-rata it on a Daily Basis.
So... "Enquiring minds want to know"... are you all set now or is there something else?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply