When we try and use the local variable NetQty (see below) in the WHERE clause we receive the following error in SQL Analyzer.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'NetQty'.
SELECT NetQty = Sum(Qty - QtyClosed),UserID, BookName, Contract, Currency, Exchange, PricingDate FROM books
LEFT OUTER JOIN TicketHeaders ON TicketHeaders.TicketNumber LIKE Books.TicketNumber
WHERE PricingDate > '20041124' AND UserID LIKE 'JAMES' AND BookName LIKE '1' AND NetQty <> 0
GROUP BY UserID, BookName, Contract, Currency, Exchange, PricingDate
We only want to include the row if this Qty - QtyClosed <> 0 - HOW?