case
	when Contracts.AMDNEWCONT is null then (
		select Sum(isNull(Deposits.PYPRIN, 0))
		from CO1.DEPOSIT Deposits
		where Deposits.CONTRACT = Contracts.CONTRACT
		)

	else ISNULL(
			(
			select Sum(isNull(Deposits.PYPRIN, 0))
			from CO1.DEPOSIT Deposits
			where Deposits.CONTRACT = Contracts.CONTRACT
			)
		, 0 )
		+ (
			select Sum(isNull(Deposits.PYPRIN, 0))
			from CO1.DEPOSIT Deposits
			where Deposits.CONTRACT = Contracts.AMDNEWCONT
		)

end as PrinPaid,

;WITH cte
AS
(
	SELECT De.CONTRACT, SUM(De.PYPRIN) AS PYPRIN
	FROM CO1.DEPOSIT De
	GROUP BY De.CONTRACT
)
SELECT
	Co.AMDNEWCONT AS NewAmdContractNumber,
	Co.CONTRACT AS ContractNumber,
	Cu.FULLNAME AS CustFullName,
	RTRIM(I.CarYear) + ' ' + RTRIM(I.CarMake) + ' ' + RTRIM(I.CarModel) AS Yr_Make_Model,
	T.TAXABLE AS TaxableAmount,
	ISNULL(Co.DOWNPAY, 0) + ISNULL(Co.PICKUP, 0) AS DownPayment,
	ISNULL(C1.PYPRIN, 0) + ISNULL(C2.PYPRIN, 0) AS PrinPaid,
	T.STATE AS TaxSTATE,
	Co.DATESOLD AS DateSold,
	Co.POWRDATE as WriteoffDate
FROM CO1.CONINFO Co
	-- no point in checking for W.CONTRACT IS NOT NULL in WHERE clause
	-- just make inner join.
	JOIN CO1.CONWRITE W
		ON Co.CONTRACT = W.CONTRACT
	LEFT JOIN CO1.TAXOWED T
		ON Co.CONTRACT = T.CONTRACT
			-- If leave these in WHERE, the effect will be the same as an inner join
			-- If want inner join, just remove the LEFT.
			AND T.STATE = 'TN'
			AND T.CODE = 'STATE'
	LEFT JOIN CUSTOMER Cu
		ON Co.CUSTCODE1 = Cu.CustCode
	LEFT JOIN CO1.INVEN I
		ON Co.STOCKNO = I.STOCKNO
	LEFT JOIN cte C1
		ON Co.CONTRACT = C1.CONTRACT
	LEFT JOIN cte C2
		ON Co.AMDNEWCONT = C2.CONTRACT
WHERE Co.DATESOLD >= '20090101'
	AND Co.DATESOLD < '20100101'