hi i have the following query
UPDATE m SET m.MarketValue = f.MarketValue FROM dbo.NTAISOIHoldingsTable m INNER JOIN ( SELECT [Legal Entity ID],Family,[Investment Cost], SUM([As at 30/06/2016]) MarketValue FROM dbo.NTAISOIHoldingsTable WHERE [Investment Cost]='0100' AND [Investment Cost]='0400'AND [Investment Cost]='0900'and Family=Family GROUP BY [Legal Entity ID], Family,[Investment Cost] ) f ON m.[Legal Entity ID] = f.[Legal Entity ID] AND m.Family = f.Family;
I want to sum the as at field based on the investcome equa to 0100 and 0400 and 0900.
when i use and and in the query noting happens when i use or then it doesnt sum up correct e.g et a value for 0100 and 0400 for the same family when i want just one summed value. any ideas why that is happening