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
Hi.
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?
thanks