March 4, 2009 at 3:21 pm
I have a table where each record shows an amount of money invested between two dates. I need to turn this into a second table that takes this value and spreads it evenly across each of those days then creates 1 record per day.
i.e.
from:
DescriptionValue From To
Spend 11000 01/01/200810/01/2008
to:
Date DescriptionValue
01/01/2008Spend 1100
02/01/2008Spend 1100
03/01/2008Spend 1100
04/01/2008Spend 1100
05/01/2008Spend 1100
06/01/2008Spend 1100
07/01/2008Spend 1100
08/01/2008Spend 1100
09/01/2008Spend 1100
10/01/2008Spend 1100
Would anyone have any idea how to achieve this?
Many thanks in advance,
John
March 4, 2009 at 11:30 pm
Heh... must be budget planning time again. This should do it, John.... I'm using Master.dbo.spt_Values as if it were a Tally table...
DECLARE @Description VARCHAR(20),
@Value MONEY,
@From DATETIME,
@To DATETIME
SELECT @Description = 'Spend 1',
@Value = CONVERT(MONEY,'1000'),
@From = '01/01/2008',
@To = '10/01/2008'
SELECT DATEADD(mm,t.Number,@From) AS [Date],
@Description AS [Description],
@Value/(DATEDIFF(mm,@From,@To)+1) AS [Value]
FROM Master.dbo.spt_Values t
WHERE t.Type = 'P'
AND t.Number BETWEEN 0 AND DATEDIFF(mm,@From,@To)
For more info on what a Tally table is and how it's used to replace many While Loops, please see the following article...
http://www.sqlservercentral.com/articles/TSQL/62867/
Oh yeah... almost forgot... the spt_Values table only has "P" values from 0 to 255. If you need more months than than, then you will need a Tally table and may need to make some minor mods to the code above to handle a Tally table that starts at 1. Of course, if you start it at 0, all you'll need to do is change the FROM clause and the word NUMBER to just N.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2009 at 1:53 pm
Thanks for that Jeff - all working perfectly!
March 6, 2009 at 8:42 pm
...and thank you for the feedback!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy