memymasta (12/4/2012)

---

@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