Generate Month end records bases on Start Date

Question

Post reply

Generate Month end records bases on Start Date

Viewing 8 posts - 16 through 22 (of 22 total)

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

Jeff Moden (5/23/2012)

65,000 months is more than 5,416 years.

You're right. That's what I get for going from memory... they say that's the first to go.

Thanks for the math lesson!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

dwain.c (5/23/2012)

Jeff Moden (5/23/2012)
65,000 months is more than 5,416 years.
You're right. That's what I get for going from memory... they say that's the first to go.
Thanks for the math lesson!

I did it from memory, as well. The memory in my calculator. 😀 The only reason why I went to the calculator is because I knew that 12*5 was 60 so something had to be amiss.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Jeff Moden (5/23/2012)
dwain.c (5/23/2012)
As the dark horse candidate in the race, it appears Jeff Moden has pulled into the lead!
Are those results ordered as #1 mine, #2 ColdCoffee and #3 Jeff?
Yes. And thanks for making my day. Except for "BitBucket", I thought I was the only one left in the world that knew what a "dark horse" was. 🙂

Nope. I know what this is as well. 9th grade Civics class. Do they still teach civics in school? I don't recall any of my kids even talking about it.

We had a Mock Congress and everything. It was actually fun, when you look back on it that is. One of the most boring classes while I was taking it.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Lynn Pettis (5/23/2012)
Jeff Moden (5/23/2012)
dwain.c (5/23/2012)
As the dark horse candidate in the race, it appears Jeff Moden has pulled into the lead!
Are those results ordered as #1 mine, #2 ColdCoffee and #3 Jeff?
Yes. And thanks for making my day. Except for "BitBucket", I thought I was the only one left in the world that knew what a "dark horse" was. 🙂
Nope. I know what this is as well. 9th grade Civics class. Do they still teach civics in school? I don't recall any of my kids even talking about it.
We had a Mock Congress and everything. It was actually fun, when you look back on it that is. One of the most boring classes while I was taking it.

I'd bet the OP wasn't expecting a Civics lesson here!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Chrissy321 SSCoach Points: 15763 More actions · Answer 5

Thanks Jeff, when I don't understand the code I prefer that it performs well and is concise.

txtPost_CommentEmoticon(':-)');

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 6

Let's help you understand the code a bit, then.

Let's take a starting date and set it.

DECLARE @StartDate DATETIME;

SELECT @StartDate = '4/3/2009';

Now... what month is that? You want to say "4" or "April". I'm going to say that it's 1,311 months since the 1st of January, 1900 (or day "0" in the MS world of date serial numbers).

SELECT DATEDIFF(mm,0,@StartDate);

Now, I'm going to add those months back to the 1st of January, 1900. Since I'm working with whole months, this will give me the first day of the month for @StartDate.

SELECT DATEADD(mm,DATEDIFF(mm,0,@StartDate),0);

If I add 14 months to that, I get the 1st of June in the next year...

SELECT DATEADD(mm,DATEDIFF(mm,0,@StartDate)+14,0);

The day that I want is really the end of the previous month which is always 1 day less the 1st of the month. So I just subtract 1 day.

DECLARE @StartDate DATETIME;

SELECT @StartDate = '4/3/2009';

SELECT DATEADD(mm,DATEDIFF(mm,0,@StartDate)+14,0)-1;

Now... what we really need is to not just add 14 months, but 15, 16, 17, 18, 19, etc, etc to that formula. We could use a loop to do that, or the Values from the Tally Table. t.N holds the values from the Tally Table so we only need to make a simple substitution. We also need to limit the count from the Tally Table to the number of months between the StartDate and today's date minus the 14 month offset we needed to get the correct first month. The WHERE clause does that part.

DECLARE @StartDate DATETIME;

SELECT @StartDate = '4/3/2009';

SELECT DATEADD(mm,DATEDIFF(mm,0,@StartDate)+t.n,0)-1

FROM dbo.Tally t

WHERE t.N BETWEEN 14 AND DATEDIFF(mm,@StartDate,GETDATE())

;

The rest of the code in my previous post is just CROSS JOINING the Tally Table with your #Company table and doing all of that to each row.

--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.allen SSC Guru Points: 76985 More actions · Answer 7

Jeff Moden (5/23/2012)
The day that I want is really the end of the previous month which is always 1 day less the 1st of the month. So I just subtract 1 day.
DECLARE @StartDate DATETIME;
SELECT @StartDate = '4/3/2009';
SELECT DATEADD(mm,DATEDIFF(mm,0,@StartDate)+14,0)-1;

You can save yourself a step here by adding the months to 1899-12-31 (-1) instead of 1900-01-01 (0). This only works, because December has the maximum number of days possible in a month.

DECLARE @StartDate DATETIME;

SELECT @StartDate = '4/3/2009';

SELECT DATEADD(mm,DATEDIFF(mm,0,@StartDate)+14,-1);

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Chrissy321 SSCoach Points: 15763 More actions · Answer 8

Excellent, I understand the code and the role of the the tally table. Thanks.