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
SELECT
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
Here is something that may work. Since I don't know all the business rules, it may not be exactly what you need, but the query should run without errors.
SELECT 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
AND b.BookedDate
BETWEEN '2018-04-01' AND '2018-05-22'
-- the above moved to the join. If you do that in where clause,
-- the left join will effectively become an inner join.
LEFT JOIN [dbo].[acomEstab_tbl] ae
ON bt.MasterEstabId = ae.estabid
AND ae.title IS NOT NULL
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 'bookingsCY' = 0
-- Th above commented out because it will always be false.
--The string literal 'bookingsCY' is never equal to the string literal 0
GROUP BY
bt.title,
p.countryId,
p.provinceId,
bt.MasterEstabId,
pr.ActiveSearch
ORDER BY EstabName ASC,
country ASC,
bookingsPY DESC;