Here is the structure of my tables i am using.

CREATE TABLE Billing (

CUSTCODE VARCHAR,

SALESID VARCHAR,

AMTPAIDSOFAR INT,

INVDATE DATETIME,

);

CREATE TABLE CUSTCODE (

CUSTCODE VARCHAR,

CUSTNAME VARCHAR,

SALESID VARCHAR,

);

With my query I wanted to get the customers most recent invoice date and check within 365 days of the most recent date. I declared parameters @starting and @ending that will be databound to an ASP.net form so the manager can put in their own interval. Everything worked to find the most recent invoice date until I added the not exists clause. I am getting an error saying Invalid column name 'MostRecent', but I thought that MostRecent was declared in the beginning?

SELECT Billing.CustCode, CustCode.CustName, CustCode.SalesID, MAX(Billing.InvDate) AS MostRecent

FROM Billing INNER JOIN CustCode ON Billing.CustCode = CustCode.CustCode

WHERE (Billing.InvDate BETWEEN @starting AND @ending) AND (Billing.AmtPaidSoFar > 0) AND (NOT EXISTS

(SELECT CustCode, InvDate, AmtPaidSoFar

FROM Billing AS Billing1

WHERE (InvDate >= DATEADD(DAY, - 365, MostRecent))))

GROUP BY Billing.CustCode, CustCode.CustName, CustCode.SalesID