Would be great if somebody could help me with the query I am trying to build below. Ideally I want to end up with a table with 7 columns: EstabName, Country, Province, EstablishmentID, bookingsCY, bookingsLY and Searches. I know im close; however as it stands I keep getting the error message: 'Each GROUP BY expression must contain at least one column that is not an outer reference' amongst others. Would appreciate it so much if someone could take the below script and tweak it for me to get it working.
Thanks so much
bt.title as EstabName
,p.countryId as country
,p.provinceId as Province
,bt.MasterEstabId as EstablishmentId
,count(distinct(bt.bookingid)) as bookingsCY
,count(distinct(bt.bookingid)) as bookingsPY
,pr.ActiveSearch as Searches
FROM [dbo].[Booking_tbl] as bt
left join [dbo].[Booking_tbl] b on bt.BookingId= b.BookingId
left join [dbo].[acomEstab_tbl] ae on bt.MasterEstabId = ae.estabid
left join [dbo].[BookingMeta] p on bt.BookingId= p.BookingId
left join [dbo].[acomProvince_tbl] pr on p.ProvinceId= pr.ProvinceId and p.CountryId= pr.CountryId
where bt.BookedDate between '2019-04-01' and '2019-05-22'
and b.BookedDate between '2018-04-01' and '2018-05-22'
and ae.title is not null
and 'bookingsCY' = 0
group by 'bt.EstabName'
order by 1 asc, 2 asc, 6 desc