SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CTE output not what I'm expecting?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 06/19/2013 :  13:53:24  Show Profile  Reply with Quote

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


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
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 06/19/2013 :  14:04:05  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 06/19/2013 :  14:05:27  Show Profile  Reply with Quote
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)
Go to Top of Page

shan007
Starting Member

USA
17 Posts

Posted - 06/19/2013 :  14:22:12  Show Profile  Reply with Quote
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..
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 06/19/2013 :  14:45:12  Show Profile  Reply with Quote
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?
Go to Top of Page

shan007
Starting Member

USA
17 Posts

Posted - 06/19/2013 :  15:23:11  Show Profile  Reply with Quote
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..
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000