Combining 2 CTEs into One Query

Transact-SQL
1

I am having trouble trying to combine these two CTEs into One query. Can anyone help lead me in the right direction? I tried using comma after the first CTE and removing the 'WITH" in the second CTE, but get an error "Recursive member of a common table expression 'ANS_CTE' has multiple recursive references."

WITH ANS_CTE AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY ANS_IndividualID ORDER BY ANS_SubmitDate) AS rownum
FROM
[dbo].[vCDCP_rptANSImprove]
UNION ALL
SELECT DISTINCT
A1.ANS_IndividualID,
A1.ANS_SubmitDate,
A1.ANS_ArrSum,
A2.ANS_DepSum,
CASE
WHEN
A1.ANS_ArrSum > A2.ANS_DepSum and
not exists(select 1 from ANS_CTE A3 where A1.ANS_IndividualID = A3.ANS_IndividualID and A1.ANS_SubmitDate < A3.ANS_SubmitDate) THEN 1 ELSE 0
END AS flag
FROM
ANS_CTE AS A1
INNER JOIN
ANS_CTE AS A2 ON A1.ANS_IndividualID = A2.ANS_IndividualID
AND A1.rownum = A2.rownum + 1
),
ATQInfant_CTE AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY ATQInfant_IndividualID ORDER BY ATQInfant_Date) AS rownum
FROM
[dbo].[vCDCP_rptInfantImprove]
)
SELECT DISTINCT
A1.ATQInfant_IndividualID,
A1.ATQInfant_Date,
A1.InfantSum,
A2.InfantSum,
CASE
WHEN
A1.InfantSum > A2.InfantSum and
not exists(select 1 from ATQInfant_CTE A3 where A1.ATQInfant_IndividualID = A3.ATQInfant_IndividualID and A1.ATQInfant_Date < A3.ATQInfant_Date) THEN 1 ELSE 0
END AS flag
FROM
ATQInfant_CTE AS A1
INNER JOIN
ATQInfant_CTE AS A2 ON A1.ATQInfant_IndividualID = A2.ATQInfant_IndividualID AND A1.rownum = A2.rownum + 1

2

Is this an attempt to write recursive SQL?

The problem is most likely located around the UNION ALL

Verify that the columns from

SELECT *
		,ROW_NUMBER() OVER (PARTITION BY ANS_IndividualID ORDER BY ANS_SubmitDate) AS rownum
FROM [dbo].[vCDCP_rptANSImprove]

are the same in number and in data type as in

		SELECT DISTINCT A1.ANS_IndividualID
			,A1.ANS_SubmitDate
			,A1.ANS_ArrSum
			,A2.ANS_DepSum
			,CASE 
				WHEN A1.ANS_ArrSum > A2.ANS_DepSum
					AND NOT EXISTS (
						SELECT 1
						FROM ANS_CTE A3
						WHERE A1.ANS_IndividualID = A3.ANS_IndividualID
							AND A1.ANS_SubmitDate < A3.ANS_SubmitDate
						)
					THEN 1
				ELSE 0
				END AS flag
		FROM ANS_CTE AS A1
		INNER JOIN ANS_CTE AS A2
			ON A1.ANS_IndividualID = A2.ANS_IndividualID
				AND A1.rownum = A2.rownum + 1
		)

it is a bad habit to write

SELECT *

in production code. Only use it for a quick test.
You should name each and every column in a SELECT statement, especially if you want to use UNION later on.
Once you do that, it will become obvious where the problem is located.