I have a select statement that pulls a set of accounts that I want to mark as closed.
Set Account.Closed = 'Y'
However, I have aggregates in my query and I just can't get it to look at for a specific asset value and date range. I keep setting all accounts closed in my test db (like it ignores the dates/values)
Here is the statement I'm working with.
SELECT Account.AccountNumber, Household.Name, FinancialPlanner.LastName, MAX(Transact.TradeDate) AS Expr1, Account.Closed
FROM Transact INNER JOIN
Account ON Transact.AccountID = Account.AccountID INNER JOIN
Household ON Household.HouseholdID = Account.HouseholdID INNER JOIN
FinancialPlanner ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE (Transact.AccountID IN
FROM Account AS Account_1 INNER JOIN
Asset ON Account_1.AccountID = Asset.AccountID
WHERE (Account_1.FinancialInstID <> 'X000007H2J92NVT000') AND (Account_1.Managed <> 'N') and (account.closed <> 'y')
GROUP BY Account_1.AccountID
HAVING (SUM(Asset.Value) BETWEEN '0' AND '.01')))
GROUP BY Account.AccountNumber, Household.Name, FinancialPlanner.LastName, Account.Closed
HAVING (MAX(Transact.TradeDate) BETWEEN '01/01/2006' AND '06/30/2018')
Any help on how to write this update statement would be appreciated.