question

This query returns results that aren't grouped in the way the query expects it to. This started only happening recently and has been occurring at least twice a week. SELECT UserN, Program, CPair, PosCcy1, OpenPosOpt, CrossR, OptPrem, DailyOptValue, OptPandL FROM ( (SELECT UserN, Program, CPair,PosCcy1,OpenPosOpt,RevalC1, RevalC2, CrossR, OptPrem,DailyOptValue,OptValue, OptPandL FROM (SELECT UserN, Program, CPair, ROUND(SUM(Ccy1Amount),4) AS PosCcy1, ROUND(SUM(OpenOptPos),4) AS OpenPosOpt, MAX(RevalC1) as RevalC1, MAX(RevalC2) as RevalC2, case when MAX(RevalC2) = 0 and MAX(RevalC1) = 0 then 0 else MAX(RevalC2)/MAX(RevalC1) end as CrossR, ROUND(SUM(OptPrem),2) AS OptPrem, ROUND(SUM(DailyOp),4) AS DailyOptValue, ROUND(SUM(OptValue),4) AS OptValue, ROUND(SUM(OpPandL),4) AS OptPandL, sum(Prem) as Prem FROM AReport rr GROUP BY UserN, Program, CPair) a WHERE not(PosCcy1= 0 and OpenPosOpt= 0 and Prem = 0) )) results The 'AReport' is a view. As could be seen I expect the query to return the data grouped by UserN, Program and CPair. Almost always I get that result, but all of a sudden at times the results aren't grouped like that. When this happens SQL Server returns the un-grouped results the whole day. I've run parts of the query on SSMS during then and the results I get are grouped as expected for the two inner nested selects. The moment I highlight the outer most select to be included the results set is un-grouped. I've looked for errors in the error log and the event viewer which doesn't seem to suggest anything. This behaviour is automatically rectified by the following day. Could someone tell me if there's anything in the way the query is written that's causing this behaviour.