Adding fake rows on a result of a query

Question

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 T1
    CREATE 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 query
    declare @dataBegin datetime
    declare @dataEnd datetime
    set @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 year
    FROM T1
    WHERE 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 /year
    0001/ 300.00000 /11 /2013
    0001/ 400.00000 /12 /2013

The problem is that I want also the months that has a value of zero as totalsales. So the result must be:

department    /totsales/  month /year
0001/ 0   /1  /2013
0001/ 0   /2  /2013
0001/ 0   /3  /2013
0001/ 0   /4  /2013
0001/ 0   /5  /2013
0001/ 0   /6  /2013
0001/ 0   /7  /2013
0001/ 0   /8  /2013
0001/ 0   /9  /2013
0001/ 0   /10 /2013
0001/ 300.00000   /11 /2013
0001/ 400.00000   /12 /2013

How can I do that?

Answer 1

you could create a table Months and do a Left Join with it

SELECT *
FROM Months M
LEFT JOIN T1 T ON M.month = T.Month