I confess I don't totally understand your requirement, particularly how you are integrating the date range into the query, but I do see a couple of problems with the query you posted.

1. The alias MostRecent created in the outer query will not be accessible within the subquery you are checking for NOT EXISTS - that is why you're getting the error reported.

2. When you check for a date range, BETWEEN is not recommended. You should use >= @StartDate and <= @EndDate.

The below won't solve your problem entirely and I had to make up some sample data that may not be representative, however if you look at what I've done perhaps it will help you. It returns a NULL in the MostRecent column for any customer not having an invoice within 365 days of the start date.

CREATE TABLE #Billing

(CUSTCODE VARCHAR

,SALESID VARCHAR

,AMTPAIDSOFAR INT

,INVDATE DATETIME);

CREATE TABLE #CUSTCODE

(CUSTCODE VARCHAR

,CUSTNAME VARCHAR(20)

,SALESID VARCHAR);

INSERT INTO #CUSTCODE

SELECT '1', 'Dwain', '1' UNION ALL SELECT '2', 'llcooldre', '2' UNION ALL SELECT '3', 'Dr Dobbs', '3'

INSERT INTO #Billing

SELECT '1', '1', 400, '2012-01-01'

UNION ALL SELECT '1', '2', 400, '2012-01-20'

UNION ALL SELECT '2', '1', 400, '2012-01-31'

UNION ALL SELECT '2', '2', 400, '2012-02-28'

UNION ALL SELECT '2', '3', 400, '2013-01-01'

UNION ALL SELECT '3', '1', 400, '2011-01-01'

UNION ALL SELECT '3', '2', 400, '2011-02-01'

UNION ALL SELECT '3', '3', 400, '2011-03-01'

DECLARE @StartDate DATE = '2012-12-01'

,@EndDate DATE = '2012-12-31'

;WITH EligibleInvoices AS (

SELECT CUSTCODE, SALESID, AMTPAIDSOFAR, INVDATE

,MostRecent=ROW_NUMBER() OVER (PARTITION BY CUSTCODE ORDER BY INVDATE DESC)

FROM #Billing)

SELECT a.CUSTCODE, a.CUSTNAME

,MostRecent=CASE WHEN DATEADD(day, 365, INVDATE) > @StartDate THEN INVDATE ELSE NULL END

FROM #CUSTCODE a

LEFT JOIN EligibleInvoices b ON a.CUSTCODE = b.CUSTCODE

WHERE MostRecent = 1 AND AMTPAIDSOFAR > 0

DROP TABLE #Billing

DROP TABLE #CUSTCODE

Good luck!