InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 22:36:27 GMT20http://www.sqlservercentral.com/Forums/Topic1391845-1291-1.aspx[quote][b]Sachin 80451 (12/3/2012)[/b][hr]I need to refer to a temp table which has all the months and dynamically shows the years. for example I need rolling 12 months to show in this table:[/quote]Since there are no DATETIME reference columns in that table, I have to ask... WHY? What and how are you going to use this? I'm not trying to be difficult here. I just see a world of hurt coming up and I'm trying to help you avoid it. In order to do that, I need to know what and how you intend to use this table.Tue, 25 Dec 2012 22:14:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1391845-1291-1.aspx[quote][b]Cadavre (12/3/2012)[/b][hr][code="sql"]CREATE TABLE #yourTempTable([MonthName] VARCHAR(9), [Year] INT);WITH CTE(N) AS (SELECT 1 FROM (SELECT 1 UNION ALL SELECT 1)a(N)),CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),TALLY(N) AS (SELECT 0 UNION ALL SELECT TOP 11 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM CTE3),DATETALLY(N) AS (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0) FROM TALLY)INSERT INTO #yourTempTableSELECT DATENAME(month,N), YEAR(N)FROM DATETALLY;SELECT *FROM #yourTempTable;[/code][/quote]Jeff Moden would probably be disappointed with me if I didn't post this somewhat terser version:[code="sql"]SELECT Month=DATENAME(month, MyDate), Year=DATEPART(year, MyDate)FROM [master].dbo.spt_values TallyCROSS APPLY ( SELECT DATEADD(month, number-1, GETDATE())) a (MyDate)WHERE [Type] = 'P' AND Number BETWEEN 1 AND 12[/code]Mon, 24 Dec 2012 19:03:34 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1391845-1291-1.aspx[quote][b]Sachin 80451 (12/6/2012)[/b][hr]Unfortunately i dont have the faintest idea.Is there any way you can break it down for me?[/quote]Okay let me thave a go.Cadavre introduces the concept of a tally table. In his solution he creates the table on the fly which is great but as this numbers table is so useful I highly recommend you create this table permanently in a utility database perhaps?Here is one way of creating the Tally table:[code="sql"]If exists (select 1 from information_schema.tables where table_name = 'Tally')drop table dbo.[Tally]GOCREATE TABLE dbo.Tally (N INT,CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))DECLARE @Counter INT SET @Counter = 0WHILE @Counter <= 10000 BEGIN INSERT INTO dbo.Tally (N) VALUES (@Counter)SET @Counter = @Counter + 1 [/code]Now that we have our numbers we can get the first day of each month as follows:[code="sql"]SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0) as N FROM TALLY[/code]The above bit of code looks complicated but I will try and explain. Our goal is to get one record per month as per yor requirement. To do this we start with:[code="sql"]SELECT DATEDIFF(month, 0, GETDATE())+NFROM TALLY[/code]This gives us the number of months since 01/01/1900 this is the date you get when you cast the value 0 as a date. So the above query will run somethign like this.1) Number of months since 01/01/1900 to this day = 1355 + N (tally table starts at 0) so = 13552) Number of months since 01/01/1900 to this day = 1355 + N (next value of N is 1 ) so = 1356and so on.If we then add the number of months back to 01/01/1900 we will get back 1 record per monthAnd to get the name we can make the above a derived table or CTE as follows:[code="sql"]select DATENAME(month,N), YEAR(N)FROM (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0) as N FROM TALLY) as a[/code] Hope this helps.Thu, 06 Dec 2012 04:02:35 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1391845-1291-1.aspxUnfortunately i dont have the faintest idea.Is there any way you can break it down for me?Thu, 06 Dec 2012 03:33:28 GMTSachin 80451http://www.sqlservercentral.com/Forums/Topic1391845-1291-1.aspx[quote][b]Sachin 80451 (12/3/2012)[/b][hr]Fantastic thank you[/quote]No problem. Do you understand how it works?Mon, 03 Dec 2012 04:48:02 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1391845-1291-1.aspxFantastic thank youMon, 03 Dec 2012 04:43:55 GMTSachin 80451http://www.sqlservercentral.com/Forums/Topic1391845-1291-1.aspx[code="sql"]CREATE TABLE #yourTempTable([MonthName] VARCHAR(9), [Year] INT);WITH CTE(N) AS (SELECT 1 FROM (SELECT 1 UNION ALL SELECT 1)a(N)),CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),TALLY(N) AS (SELECT 0 UNION ALL SELECT TOP 11 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM CTE3),DATETALLY(N) AS (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0) FROM TALLY)INSERT INTO #yourTempTableSELECT DATENAME(month,N), YEAR(N)FROM DATETALLY;SELECT *FROM #yourTempTable;[/code]Mon, 03 Dec 2012 04:38:53 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1391845-1291-1.aspxI need to refer to a temp table which has all the months and dynamically shows the years. for example I need rolling 12 months to show in this table:November 2012December 2012January 2013February 2013March 2013April 2013May 2013June 2013July 2013August 2013September 2013October 2013And when we are in december I would expect december 2012 to be at the top and an extra line for November 2013. (November 2012 to dissapear).Is this possible?Mon, 03 Dec 2012 04:27:25 GMTSachin 80451