Help with an Update Query

1

I have a select statement that pulls a set of accounts that I want to mark as closed.
Update Account
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
(SELECT Account_1.AccountID
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.

2 
UPDATE a
  SET 
      a.Closed = 'Y'
FROM Transact t
     INNER JOIN Account a ON t.AccountID = a.AccountID
     INNER JOIN Household h ON h.HouseholdID = a.HouseholdID
     INNER JOIN FinancialPlanner fp ON h.FinancialPlannerID = fp.FinancialPlannerID
WHERE t.AccountID IN
(
    SELECT Account_1.AccountID
    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'
)
    --AND MAX(t.TradeDate) BETWEEN '01/01/2006' AND '06/30/2018'; --(changed to address error)
AND t.TradeDate BETWEEN '01/01/2006' AND '06/30/2018';
1 Like
3

I still got an error/am having issues with the aggregate

Msg 147, Level 15, State 1, Line 19

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

4

Does this work:

UPDATE a
  SET 
      a.Closed = 'Y'
FROM Transact t
    INNER JOIN Account a ON t.AccountID = a.AccountID
    INNER JOIN Household h ON h.HouseholdID = a.HouseholdID
    INNER JOIN FinancialPlanner fp ON h.FinancialPlannerID = fp.FinancialPlannerID
    INNER JOIN (
    SELECT Account_1.AccountID
    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'
    ) b on t.AccountID = b.AccountID 
WHERE t.TradeDate BETWEEN '01/01/2006' AND '06/30/2018';
5

This is working. I do have one question so I can understand this properly. what does the "b" stand for in the line b on t.AccountID = b.AccountID

6

Also, I need to updated the account.closed date. Sorry to be a pain, I don't have a strong grasp on the Update function

SELECT Account.AccountNumber, Account.Closed, Account.ClosedDate
FROM Account LEFT OUTER JOIN
Transact ON Account.AccountID = Transact.AccountID
WHERE (Account.Closed = 'Y')
group by Account.AccountNumber, account.closed, account.ClosedDate
HAVING max(Transact.TradeDate) BETWEEN '01/01/2006' AND '06/30/2006'

7

b is an alias for the subquery in parenthesis

the criteria Account.closed = 'Y' is included in the subquery, and that, inner joined to account will filter the "outer" query.