September 4, 2011 at 10:37 am
tamil.selvanmca (9/2/2011)
WITH DateYear AS(
SELECT 0 AS num
UNION ALL
SELECT num + 1 FROM DateYear
WHERE num < 11
)
SELECT CONVERT(DATE,DATEADD(MONTH,num,'2011')) AS Date from DateYear
Please see the following article for why [font="Arial Black"]I'm begging people to stop showing people how to use recursive CTE's for such a thing[/font].
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2011 at 11:25 am
ColdCoffee (9/4/2011)
Added more details in code and optimized it.
Be careful, CC... The WHERE clause keeps it from being "optimized". The entire 1,000 rows in the "Numbers" CTE will always "materialize" behind the scenes even if you only use 1 month.
Also, try a "StartDate" of '02-28-1985' and see if the results are actually what you expected.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2011 at 11:40 am
paul_ramster (8/31/2011)
In just a couple of years, this table is only going to contain past dates.If you're going to create a calendar table, it should contain more years than this, I would have thought - or be dynamically created as and when required spanning enough years before and after the current date.
Also, Lowell's suggestion is needlessly complicated - the months have the same name every year, so there is no need to provide a variable year in the MonthName calculation:
selectt.Number as [year],m.[month]
,DateName(month,DateAdd(month,(m.[month]-1),0)) as MonthName
fromtally t
cross join(values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m([month])
wheret.Number between DatePart(year,GetDate()) - 100 and DatePart(year,GetDate()) + 200
Of course, this assumes that you have a Tally table handy, but if you haven't, then you should! 😉
Nicely done, Paul. That's one of the fastest ways I've seen to do such a thing even if the VALUES clause is converted to 12 UNION ALL's for 2005.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2011 at 10:54 pm
ColdCoffee (9/4/2011)
And coming to my code, a 0-based will throw the CASE statement out of sync. That's why i din't use it 🙂
You're right, I had thought of that but in the end I still forgot to add "1 +" to the T.N where the case used to be to compensate for the shifted outcome.
About adding the zero in front of the row_number() generated sequence: that's an even nicer trick. Hadn't thought of that yet, thanks.
September 4, 2011 at 11:11 pm
Why not store the information in a table as opposed to dynamically generating the data?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 4, 2011 at 11:15 pm
Jeff is 100% right on his assessment of my code. There can be MANY tweaks. As a humble student of his, i wanted to impress my mentor. Jeff if u are still on thread, can you please comment on my latest version?
IF OBJECT_ID('TempDB..#Numbers') IS NOT NULL
DROP TABLE #Numbers
DECLARE @StartDATE DATETIME
,@EndDate DATETIME
,@YearofEndDate INT
,@NumOfMonths INT
SELECT @StartDATE = '02-28-1985'
, @EndDate = GETDATE()
SET @YearofEndDate = YEAR(@EndDate)
SET @NumOfMonths = DATEDIFF(MONTH,@StartDATE,@EndDate) + 1
select @NumOfMonths
-- Reset @StartDATE and @EndDate to start of respective months
SELECT @StartDATE = DATEADD( MONTH , DATEDIFF(MONTH , 0 , @StartDATE ) , 0)
, @EndDate = DATEADD( MONTH , DATEDIFF(MONTH , 0 , @EndDate ) , 0)
;WITH Tens (N) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0
),
Thousands (N) AS
(
SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3
),
Numbers ( N ) AS
(
SELECT 0
UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands
)
SELECT N
INTO #Numbers
FROM Numbers
CREATE CLUSTERED INDEX IX_Tally_N ON #Numbers( N )
SELECT DATEPART(MONTH,DATEADD(M,N,@StartDATE)) [MonthNum]
, DATEPART(YY,DATEADD(M,N,@StartDATE)) [Year]
, DATENAME(MM,DATEADD(M,N,@StartDATE)) [Month]
, DATEADD(M,N,@StartDATE) [StartDateOfMonth]
, DATEADD (DAY , -1, DATEADD(MONTH , 1 , DATEADD(M,N,@StartDATE)) ) EndDateOfMonth
FROM #Numbers T
WHERE T.N <= @NumOfMonths
ORDER BY [Year] , MonthNum
I guess i have fixed both WHERE and startdate.
As a side bar, i copy pasted the code from my other post where the OP's request was to create the calendar table and his start date will always be the start of the month. I apologize for not reading the real requirement of the post.
To R.P.Rozema, i have based the tally at zero and made changes to the code 🙂 Thanks for improving the code.
December 17, 2012 at 11:39 pm
December 17, 2012 at 11:40 pm
December 18, 2012 at 3:00 am
sandeepmittal11 (12/17/2012)
http://itdeveloperzone.blogspot.in/2012/11/generate-list-of-months-in-sql-server.html%5B/quote%5D
Couple of issues.
1) You've replied to a thread that is over a year old.
2) You've posted a recursive CTE counting method. See this article --> http://www.sqlservercentral.com/articles/T-SQL/74118/%5B/url%5D, for why that's a bad idea.
I'd reply to your blog, but I have to login which I'm not willing to do. Try this: -
WITH CTE([monthName],[monthNumber]) AS (
SELECT N+1, DATENAME(MONTH,DATEADD(MONTH,N,CAST('1900' AS DATETIME)))
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 UNION ALL SELECT 11
)a(N)
)
SELECT *
FROM CTE;
or this: -
WITH CTE([monthName],[monthNumber]) AS (
SELECT N+1, DATENAME(MONTH,DATEADD(MONTH,N,CAST('1900' AS DATETIME)))
FROM (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
)a(N)
)
SELECT *
FROM CTE;
As alternatives to your recursive CTE.
December 18, 2012 at 3:34 am
ColdCoffee,
I like your solution.
I pushed it to run over the largest dataset I could think of with every month between 1753 and 9999 and it returned in about 2 seconds to SSMS, however I did find that around 15% of the query was building the cusltered index on the Temp table.
By Dropping the Index create there was no significant overhead in the final query, so I'd probably leave the index off in this case.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 18, 2012 at 6:13 am
Yowch! Nasty web site. It want's to build a half dozen trusted frames and download temporary fonts. I don't allow that on my machine. When I said "No" to all of that, the site started opening new connections and ate a good amount of memory.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2012 at 6:16 am
Jason-299789 (12/18/2012)
ColdCoffee,I like your solution.
I pushed it to run over the largest dataset I could think of with every month between 1753 and 9999 and it returned in about 2 seconds to SSMS, however I did find that around 15% of the query was building the cusltered index on the Temp table.
By Dropping the Index create there was no significant overhead in the final query, so I'd probably leave the index off in this case.
If you need to use such dates on a regular basis, it would probably be better to build a permanent narrow calendar table with the clustered index intact.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2012 at 7:11 am
Id agree with that Jeff, a permenant calendar table (or similar) would be the best option.
I just thought it strange to put a clustered index on a temporary tally table, with little actual gain in performance on a 192k dataset.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 4, 2016 at 1:51 am
Agree with Jeff, but i think it would not make much difference when you have such small list of data
February 4, 2016 at 7:24 am
sandeepmittal11 (2/4/2016)
Agree with Jeff, but i think it would not make much difference when you have such small list of data
"It Depends". You have to take into consideration how many times an hour it's going to be hit and whether it will get bigger or not. You also have to remember that someone else may see your code and use it for something bigger.
The bottom line is that you should always do it right and not write slower or more resource intensive code just because it's supposedly for a small number of rows. It just doesn't take any longer to write strong code all the time. It's kind of like playing the piano... if you always practice hitting the right notes, it'll get easier and the end product will get better. Unless you're a piano comedian that needs to hit the wrong notes, why would you practice hitting the wrong notes?
The databases and stored procedures that we have at work are filled with code where someone said that it's for a small amount of rows and wrote the code without regard to performance for larger stuff. They didn't realize that some of their code was going to be hit several hundred thousand times a day. Since that attitude prevailed throughout the entire project, we're having to fix the entire project to get performance back to reasonable levels. For example, we just repaired one piece of code that "only" took 250ms to execute but it was executed enough times in an 8 hour period to use 14 hours of CPU time. That's almost like it using close to 2 CPUs steady for the 8 hour period.
Milliseconds always matter. Do it right all the time.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply