help loop through rows to expand (date, number of months) to multiple rows

Question

help loop through rows to expand (date, number of months) to multiple rows

dandenson

Mr or Mrs. 500

Points: 566
More actions
January 20, 2011 at 12:05 pm

#232517

I am trying to normalize some data, as follows:
seq, date, months to apply
------------------------------
1,2010-01-01,2
2,2010-02-15,4
to:
seq,date
---------------
1,2010-01-01
1,2010-02-01
2,2010-02-15
2,2010-03-15
2,2010-04-15
2,2010-05-15
have tried a cursor but never got real results. Everything I do only processes each row once and I dont know how to get the 'months to apply' into a counter for a cursor.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jason Norsworthy SSChasing Mays Points: 636 More actions · Answer 1

I'm too busy to work out a script right now, but maybe you, or somebody else could benefit from hearing how I'd solve it:

Build a Tally table[/url] if you don't already have one

Join your source table to the tally table where your # of months is <= N in tally table

In your select clause, use dateadd to add the number of months to the given date using N from the tally table

Insert results into your new table

LutzM SSC Guru Points: 107049 More actions · Answer 2

CROSS APPLY together with some sort of a tally table should help here.

If you're not using a tally table yet, I recommend reading the article referenced in my signature.

DECLARE @tbl TABLE(seq INT, dateval DATE, months INT)

INSERT INTO @tbl

VALUES(1,'2010-01-01',2),

(2,'2010-02-15',4)

SELECT seq,DATEADD(mm,number,dateval) AS datevals

FROM @tbl

CROSS APPLY

(SELECT number FROM master..spt_values WHERE TYPE='P' AND number < months)subTally

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dandenson Mr or Mrs. 500 Points: 566 More actions · Answer 3

Thanks! I was able to adapt this to my needs. Very interesting read on the Tally tables too!

LutzM SSC Guru Points: 107049 More actions · Answer 4

Glad I could help.

And I agree, the tally table article is a helpful read. But if you're actually start using the concept, it becomes a powerful tool.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dandenson Mr or Mrs. 500 Points: 566 More actions · Answer 5

lutzm, Just throwing out anther thanks here, I now live and breath tally tables. I have been converting views that have always been slow to use the tally table method and seeing some amazing performance improvements. awesome.

LutzM SSC Guru Points: 107049 More actions · Answer 6

dandenson (4/7/2011)
lutzm, Just throwing out anther thanks here, I now live and breath tally tables. I have been converting views that have always been slow to use the tally table method and seeing some amazing performance improvements. awesome.

Thank you for the compliment. But... I'm not the author of that great article. It's been Jeff Moden who wrote it and for sure did help an uncountable number of developers to improve the performance of their code.

Thank you for the feedback though. It's always great to hear SSC (SQL Server Central) has been a source for better SQL coding. That's what this site is all about.

Again, thanx for the feedback. Very much appreciated.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]