CTE output not what I'm expecting? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CTE output not what I'm expecting?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-06-19 : 13:53:24

[code]
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
[/code]

The sixth record (P1-0017) doesn't appear at all?

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-06-19 : 14:04:05

P1-0017 fails to meet the "AND w.CC = t.CC AND w.CO_CC = t.CO_CC" condition of the WHERE clause.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-19 : 14:05:27

This is because for P1-0017, the very first month is 5; in the anchor portion of your CTE, you are eliminating that by specifying the WHERE clause as MONTH=1.

If you just want to get that row in, you could add a row number to the table, or construct one on the fly like shown below. But if you wanted to show running total as zero for P1-0017 for months 1-4, you would need to use a calendar table or something similar:

;WITH CTE AS 
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY CC,CO_CC,YEAR ORDER BY MONTH ) AS RN
	FROM
		#T 
),
TEST AS
(SELECT *, VALUE AS RUNNING_SUM FROM CTE WHERE RN=1
UNION ALL
SELECT w.*, w.VALUE + t.RUNNING_SUM FROM CTE 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.RN > 1)

shan007
Starting Member

17 Posts

Posted - 2013-06-19 : 14:22:12

As said it's because CC- '0017' and CO-CC - 'P1-0017' are not there in anchor rows before 'union all', hence the where condition "AND w.CC = t.CC AND w.CO_CC = t.CO_CC" skips P1-0017.

==============================
I'm here to learn new things everyday..

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-06-19 : 14:45:12

Thanks for the responses.

Any guidance on incorporating a calendar table so that P1-0017 appears from the beginning?

Also, any advice on why INT works but DECIMAL(15, 2) causes it to fall over?

Do CTEs not like DECIMAL?

shan007
Starting Member

17 Posts

Posted - 2013-06-19 : 15:23:11

The expression "w.VALUE + t.RUNNING_SUM FROM CTE w" will return the type that is not necessarily decimal(15,2), it can return decimal(5,2) controlled by CTE. So it cannot union those two values after that. To fix this, you need to cast as decimal(15,2) after sum, like in below script. I don't know about calendar table incorporation.. let me explore and get back if i get something.

;WITH TEST
AS
(SELECT *, VALUE AS RUNNING_SUM FROM #T WHERE MONTH = 1
UNION ALL
SELECT w.*, CAST(w.VALUE + t.RUNNING_SUM as decimal(15,2)) 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)

==============================
I'm here to learn new things everyday..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources