select * into #tempL from (SELECT [PGroup_strCode] ,[Price_strCode] ,[Price_intSequence] ,[Price_curPrice] ,[STax_strCode] ,[Price_strDescription] ,[Price_strShortDesc] ,[Price_strPackage] ,[Price_intPackageQuantity] FROM [tblPrice] where Price_strDescription not like '*%' ) as t; with tickets (GrossValue,transnumber,cinema,fullprice,sequence) as(select T.GrossValue,t.TransNumber,t.CinemaCode, TMP.Price_curPrice,T.TransSequence from tblDWTicket T inner join #tempL TMP on t.PriceGroup=TMP.PGroup_strCode and T.PriceCode = TMP.Price_strCode where T.SalesTransDate > '20180616' and T.TransStatus not in ('C','R') and T.TransType = 'TKT' and T.SalesTransWorkstationCode like 'K%' -- group by T.GrossValue,T.TransNumber,T.CinemaCode,TMP.Price_curPrice,T.TransSequence ) select tickets.GrossValue,tickets.transnumber,tickets.cinema,tickets.fullprice, F.GrossValue as Value ,tickets.sequence from tickets left join tblDWBookingFee F on tickets.Transnumber = F.Transnumber and tickets.cinema= F.CinemaCode where tickets.GrossValue + IsNull(sum(F.GrossValue), 0) <> fullprice -- group by tickets.GrossValue,tickets.transnumber,tickets.cinema,tickets.fullprice,tickets.sequence drop table #tempL
I would like to find any tickets that have a different full price, compared to tickets.GrossValue + F.GrossValue
but I get "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."
I'm currently reading that I need an inner select to fix that but I'm not sure. Do i need to re select the entire booking and ticket?