Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generation of Records Expand / Collapse
Author
Message
Posted Monday, September 3, 2012 9:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 7, 2014 5:45 PM
Points: 55, Visits: 343
I have got some Monthly Data which I want to pro-rata it on a Daily Basis.

E.g.
The Current Data looks like:
YearMonth	Value
-------- ------
201207 5000
201208 4000

I want to generate daily records based on the above so that I get the following:

YearMonth	Day		Value
--------- ------------ ------
201207 01/07/2012 5000 divide by No Of Days in the month i.e 5000/31
201207 02/07/2012 5000 divide by No Of Days in the month i.e 5000/31
201207 03/07/2012 5000 divide by No Of Days in the month i.e 5000/31
201207 04/07/2012 5000 divide by No Of Days in the month i.e 5000/31
201207 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

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?
Post #1353687
Posted Monday, September 3, 2012 10:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353695
Posted Monday, September 3, 2012 10:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 14, 2014 9:24 PM
Points: 162, Visits: 171
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



----------------------------------------------------------------------------------------------
Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
Email: Louis.Li@rrlminc.com | Blog | LinkedIn
Post #1353699
Posted Monday, September 3, 2012 10:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
li_ning123 (9/3/2012)
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(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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353701
Posted Monday, September 3, 2012 11:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 14, 2014 9:24 PM
Points: 162, Visits: 171
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.


----------------------------------------------------------------------------------------------
Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
Email: Louis.Li@rrlminc.com | Blog | LinkedIn
Post #1353705
Posted Monday, September 3, 2012 11:43 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 11:45 PM
Points: 562, Visits: 887
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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1353713
Posted Tuesday, September 4, 2012 7:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:18 AM
Points: 3,417, Visits: 5,332
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1354293
Posted Tuesday, September 4, 2012 10:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1354314
Posted Tuesday, September 4, 2012 10:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1354315
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse