@cadavre - Thats that did the trick!
@celko - Very useful insight, i like it. One thing i don't understand is:
That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year.
Do you store that as date?
SELECT CAST('2012-01-00' AS DATE)
This don't seem to work.
@greg Snidow - Ah yes! Your small change made it work. Updated the code a bit and it looks really good now.
CREATE TABLE [#a](
[id] [int] NOT NULL,
[recorddate] [date] NULL,
[myvalue] [int] NULL,
) ON [PRIMARY]
GO
INSERT INTO [#a] (id, [recorddate], [myvalue])
SELECT 1,'2012-01-02',10 UNION ALL
SELECT 2,'2012-01-13',20 UNION ALL
SELECT 3,'2012-02-02',30 UNION ALL
SELECT 4,'2012-02-24',40 UNION ALL
SELECT 5,'2012-08-02',50 UNION ALL
SELECT 6,'2012-12-01',60 UNION ALL
SELECT 7,'2012-12-28',70
GO
WITH myCTE (c)
AS
(
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+1,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+2,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+3,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+4,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+5,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+6,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+7,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+8,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+9,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+10,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+11,0)
)
SELECT DISTINCT c, ISNULL(SUM(myvalue),0) as myValue
FROM myCTE LEFT JOIN #a ON myCTE.c=CAST(YEAR(#a.recorddate) as CHAR(4))+'-'+CAST(MONTH(#a.recorddate) as CHAR(2))+'-'+'01'
GROUP BY c
GO
DROP TABLE #a