InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 20:12:24 GMT20http://www.sqlservercentral.com/Forums/Topic1423014-391-1.aspxA tally table will perform far better for this than a recursive cte. The biggest challenge at this point is that we can't help much because we don't know what your tables look like. Please take a few minutes and read the article in my signature about best practices when posting questions.Fri, 22 Feb 2013 10:06:29 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423014-391-1.aspxthank you for your replies. I wiil read them.how about something like the following(using CTE):declare @dataBegin datetime;declare @dataEnd datetime;set @dataEnd = '21/05/2013';set @dataBegin = DATEADD(month,-11, @dataEnd) - (DAY(@dataEnd)-1);set @dataEnd = DATEADD(month,1, @dataEnd) - (DAY(@dataEnd));WITH dates AS ( SELECT CAST(@dataBegin AS DATETIME) 'date' UNION ALL SELECT DATEADD(MONTH, 1, t.date) FROM dates t WHERE DATEADD(month, 1, t.date) <= @dataEnd)select month(dates.date) as mese, YEAR(dates.date) as anno,departmentfrom dates,(SELECT distinct departmentFROM T1) t2the result is that i have all the dates i need with the departments but i need to join this result with SELECT department,SUM(totalsales) AS totsales, MONTH(dateofsale) as month, YEAR(dateofsale) as yearFROM T1WHERE dateofsale >= @dataBegin AND dateofsale< @dataEnd GROUP BY department,MONTH(dateofsale), YEAR(dateofsale)ORDER BY department,MONTH(dateofsale), YEAR(dateofsale)the problem is that i cant do this join...any idea?thank's againFri, 22 Feb 2013 09:59:40 GMTevaldhttp://www.sqlservercentral.com/Forums/Topic1423014-391-1.aspxI've done the same thing using the more generic Tally or Numbers table.Here's a good article by Jeff about it.[url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]Fri, 22 Feb 2013 08:29:40 GMTErin Ramsayhttp://www.sqlservercentral.com/Forums/Topic1423014-391-1.aspxYou need to use a calendar table for this type of thing.[url=http://www.sqlservercentral.com/articles/T-SQL/70482/]http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]Fri, 22 Feb 2013 08:11:55 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423014-391-1.aspxHello everybody,i have a table(T1) with the following columns: department,dateofsale,totalsales.What i want to achieve is to have the sales for department per month in one year from a start date and going backward 1 year.Maybe the following query will show better what i want to achieve.-- Create the table T1CREATE TABLE [dbo].[T1]( [department] [nvarchar](50) NULL, [dateofsale] [datetime] NULL, [totalsales] [decimal](18, 5) NULL) ON [PRIMARY]-- Add some data INSERT [dbo].[T1] ([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A29B00000000 AS DateTime), CAST(200.00000 AS Decimal(18, 5)))INSERT [dbo].[T1] ([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A27D00000000 AS DateTime), CAST(300.00000 AS Decimal(18, 5)))INSERT [dbo].[T1] ([department], [dateofsale], [totalsales]) VALUES (N'0001', CAST(0x0000A29C00000000 AS DateTime), CAST(200.00000 AS Decimal(18, 5)))-- The querydeclare @dataBegin datetimedeclare @dataEnd datetimeset @dataEnd = '21/12/2013'set @dataBegin = DATEADD(month,-11, @dataEnd) - (DAY(@dataEnd)-1)set @dataEnd = DATEADD(month,1, @dataEnd) - (DAY(@dataEnd))SELECT department,SUM(totalsales) AS totsales, MONTH(dateofsale) as month, YEAR(dateofsale) as yearFROM T1WHERE dateofsale >= @dataBegin AND dateofsale< @dataEnd GROUP BY department,MONTH(dateofsale), YEAR(dateofsale)ORDER BY department,MONTH(dateofsale), YEAR(dateofsale)With the data added before the result of the query will be the following:department /totsales/ month /year0001/ 300.00000 /11 /20130001/ 400.00000 /12 /2013The problem is that i want also the months that has a value of zero as totalsales. So the result must be:department /totsales/ month /year0001/ 0 /1 /20130001/ 0 /2 /20130001/ 0 /3 /20130001/ 0 /4 /20130001/ 0 /5 /20130001/ 0 /6 /20130001/ 0 /7 /20130001/ 0 /8 /20130001/ 0 /9 /20130001/ 0 /10 /20130001/ 300.00000 /11 /20130001/ 400.00000 /12 /2013How can i do that?Thank youFri, 22 Feb 2013 06:11:05 GMTevald