Selecting list of month numbers between two dates

Question

Post reply

Selecting list of month numbers between two dates

Viewing 7 posts - 31 through 36 (of 36 total)

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

Greg Old Hand Points: 351 More actions · Answer 1

Jeff,

I am looking at your sample and I have to ask one question:

By using master.sys.all_columns, aren't you limited to 1000 days?

I am looking at generating a row for each date between a date range for 1000 stores.

I am basically doing the same thing using a user defined table that just has 1000 rows in it with one column that contains an integer from 0 to 1000.

Just curious.

Thanks,

Greg

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 2

Greg - KPS, LLC (11/1/2011)
Jeff,
I am looking at your sample and I have to ask one question:
By using master.sys.all_columns, aren't you limited to 1000 days?
I am looking at generating a row for each date between a date range for 1000 stores.
I am basically doing the same thing using a user defined table that just has 1000 rows in it with one column that contains an integer from 0 to 1000.
Just curious.
Thanks,
Greg

I certainly would be limited if I only used one copy of sys.all_columns.

I don't know what your SQL Server installation looks like but I did (and almost always do) a full install. What that means is that sys.all_columns has more than 4000 rows in it. My code uses a Cross-Join on the table to produce a maximum of more than 16 million rows. Even if you have a table with only 1,000 rows, that will still allow a Self Cross-Join to produce a million rows... certainly more days than anyone would need in a Calendar table.

If you have a table with the numbers 0 to 1000 in it, you can certainly use that to gen your dates. What you might want to do is expand on that a bit. Please see the following article...

http://www.sqlservercentral.com/articles/T-SQL/62867/

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 3

drew.georgopulos (6/21/2011)
looking forward to it!
I want you to know i am standing up at my desk to say Thank You immensely for your time and effort Mr. Moden!
drew
ps. this sucka runs like the wind!

My apologies, Drew. I get so many emails that it's difficult to make sure I get to them all. Are you all set with this particular task?

...and thanks for the kudo. I'm just sorry if it looks like I abandoned you. I just lost track of the thread. :blush:

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 4

using what you showed me, i was able to finish that piece of code, but if it gets any gnarlier ill be back. I cannot thank you enough for what you've taught me by answering my questions, let alone reading your work on the whys and wherefores extant while teaching this dust bunny to code <g>.

this site is a little oasis....actually not so little. i dont mean to slight anyone else who has helped me, Gail, Ninja's_RGR'us and Evil Kraig leap to mind, i really appreciate your help and examples.

thank you for helping me take my own itsy bitsy steps towards becoming a sql monster too.

samyuktha.vallabhaneni Say Hey Kid Points: 699 More actions · Answer 5

Hi,

My requirement is Maturity Date - (YR num+Month num + Last day of the month).

Here we have to take Maturity date from one table and Year no. and Month no. from other table.

Here we need to calculate the number of days.

How can we do this.

Thanks in Advance,

Samyuktha

samyuktha.vallabhaneni Say Hey Kid Points: 699 More actions · Answer 6

Hi,

My requirement is to calculate the Maturity Date - (Yr_Num+Month_Num+Last Day of the month) and here we have to take Maturity date from one table and Year no,Month no. from other table and need to find the number of days.

How can we do this.

Please help me out.

Thanks in Advance,

Samyuktha

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 7

hi.

the way it works here is to frame your question like this;

For better, quicker answers on T-SQL questions, click on the following...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

i can assure you that if you spend the fifteen minutes to frame your question in the manner recommended by Mr. Moden in this article, you will get more help than you know what to do with (and you will also learn a new thing or two about quotename too!)

best

drew