Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MikeTimney
Starting Member
1 Post |
 Posted - 2012-11-09 : 08:49:29
|
| I need to create a table (or view) that will create new rows based on an initial, and subsequent rows. I have a table fields called 'next replacement date' and ' lifecycle years'. For each record (in my case each component within a house) I need to work out when all subsequent replacement dates will occur based on the initial 'next replacement date' and the 'lifecycle years'The initial calculation would be:Next replacement date (from table)+ lifecycle years (from table)= Date 1Then I would want to calculate subsequent dates like this:Date 1 + lifecycle years = date 2Date 2 + lifecycle years = date 3 Date 3 + lifecycle years = date 4And so on, up to a limit of say, maximum date = 1/1/2050 I think this is what’s known as a recursive function (?) but I’m stumped as to how to do itCan anyone help? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-09 : 09:47:38
|
| You can do it using recursive queries, but seems like it can also be done without recursion. If my understanding is correct, the formula would be the following, which is a straight query rather than recursive query.Date1 = NextReplacementDate + Life cycle Years;Date2 = NextReplacementDate + Life cycle Years * 2Date3 = NextReplacementDate + Life cycle Years * 3DateN = NextReplacementDate + Life cycle Years * N |
 |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2012-11-11 : 17:56:04
|
| I'd just use a Tally Table for such a thing. If you'd post the CREATE TABLE and INSERT scripts to create an example table with some data, I could show you how easy it is.--Jeff Moden |
|
|
|
|
|
|
Recursive date increment problem