CTE not generating what I'm expecting?

Question

The sixth record in this sample is missing from my CTE output; I'm guessing it's because it only appears once? Is there a way to get it to appear?

Apologies if this a stupid question, I'm only just getting my head around CTEs.

CREATE TABLE #T (MONTH INT, YEAR INT, CC VARCHAR(4), CO_CC VARCHAR(7), VALUE INT)

INSERT INTO #T VALUES (1, 2011, '0000', 'P1-0000', 10)
INSERT INTO #T VALUES (2, 2011, '0000', 'P1-0000', 20)
INSERT INTO #T VALUES (3, 2011, '0000', 'P1-0000', 30)
INSERT INTO #T VALUES (4, 2011, '0000', 'P1-0000', 40)
INSERT INTO #T VALUES (5, 2011, '0000', 'P1-0000', 50)
INSERT INTO #T VALUES (5, 2011, '0017', 'P1-0017', 50)
INSERT INTO #T VALUES (1, 2012, '0000', 'P1-0000', 10)
INSERT INTO #T VALUES (2, 2012, '0000', 'P1-0000', 20)
INSERT INTO #T VALUES (3, 2012, '0000', 'P1-0000', 30)
INSERT INTO #T VALUES (4, 2012, '0000', 'P1-0000', 40)
INSERT INTO #T VALUES (5, 2012, '0000', 'P1-0000', 50)
INSERT INTO #T VALUES (1, 2011, '0006', 'P1-0006', 10)
INSERT INTO #T VALUES (2, 2011, '0006', 'P1-0006', 20)
INSERT INTO #T VALUES (3, 2011, '0006', 'P1-0006', 30)
INSERT INTO #T VALUES (4, 2011, '0006', 'P1-0006', 40)
INSERT INTO #T VALUES (5, 2011, '0006', 'P1-0006', 50)
INSERT INTO #T VALUES (1, 2012, '0006', 'P1-0006', 10)
INSERT INTO #T VALUES (2, 2012, '0006', 'P1-0006', 20)
INSERT INTO #T VALUES (3, 2012, '0006', 'P1-0006', 30)
INSERT INTO #T VALUES (4, 2012, '0006', 'P1-0006', 40)
INSERT INTO #T VALUES (5, 2012, '0006', 'P1-0006', 50)

GO

WITH TEST
AS
(SELECT *, VALUE AS RUNNING_SUM FROM #T WHERE MONTH = 1
UNION ALL
SELECT w.*, w.VALUE + t.RUNNING_SUM FROM #T w 
INNER JOIN TEST t
ON w.MONTH = t.MONTH + 1 
AND w.YEAR = t.YEAR 
AND w.CC = t.CC
AND w.CO_CC = t.CO_CC
WHERE w.MONTH > 1)

SELECT * FROM TEST ORDER BY YEAR, MONTH OPTION (MAXRECURSION 0)

DROP TABLE #T

Plus, if I declare VALUE as DECIMAL (15, 2) the CTE falls over with some error about anchors and recursive types being incompatible?

Answer 1

Ok, you are missing the row because of your JOIN conditions:

INNER JOIN TEST t
ON w.MONTH = t.MONTH + 1 
AND w.YEAR = t.YEAR 
AND w.CC = t.CC
AND w.CO_CC = t.CO_CC

So, you are saying that you need the rows for the next month (though this won't work for december), on the same year and the same CC. That particular row has a value for CC of '0017', wich doesn't exist on the previous month, hence it won't appear on your recursive CTE. As to the incompatibility issue, I'm not sure exactly whay this is happening, but if you use an explicit conversion on the second SELECT, then there is no problem:

SELECT w.*, CAST(w.VALUE + t.RUNNING_SUM AS DECIMAL(15,2))

UPDATE

So, as Martin Smith said on a comment, the reason for the incompatibility issue is that the CTE defined the columns data type according to your first SELECT:

SELECT *, VALUE AS RUNNING_SUM

So, RUNNING_SUM would be a DECIMAL(15,2). On your second select, that column comes from this calculation:

w.VALUE + t.RUNNING_SUM

Since both columns are DECIMAL(15,2), then the result is a DECIMAL(16,2), according to this, so both columns are incompatible, hence, the need for an explicit CAST.

Answer 2

The sixth record will never come because in UNION ALL when you join CTE with actual table on W.MONTH = #T.MONTH + 1 AND w.YEAR = t.YEAR AND w.CC = t.CC AND w.CO_CC = t.CO_CC That means Month 4 with join with Month 5 and Year 2011 and Month 4 CC (0000) = Month 5 CC (0017) which is not and Month 4 CO_CC (P1-0000) = Month 5 CO_CC (P1-0017) which is again not true. That is the reason sixth row is not coming. I hope it is clear to you now.

Answer 3

Your WHERE criteria is excluding that line because MONTH never = 1 for CC = '0017'

Instead of starting with MONTH = 1, you could use a ROW_NUMBER OVER (PARTITION BY CC,YEAR ORDER BY MONTH) to identify the first month.