I am trying to put together a query which allows me to view overall restaurant reservation metrics but then using the filter have the ability to remove certain values from the query.
The query I’ve put together is below, I tried using !=, Not in() and NOT to filter out the data but all three options give the same error: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
If anyone has any idea why this is happening and how I can work around it, I would really appreciate it.
select
DATE_TRUNC(‘day’, reservations.created_at + interval ‘4 hours’) as day,
SUM(reservations.covers) filter (where reservations.status not in (‘canceled’,‘denied’, ‘no_show’, ‘partially_arrived’, ‘not_confirmed’)) as Confirmed,
SUM(reservations.covers) filter (where reservations.status = ‘denied’) as Denied,
SUM(reservations.covers) filter (where reservations.status = ‘canceled’) as Canceled,
SUM(reservations.covers) filter (where reservations.status = ‘not_confirmed’) as Not_Confirmed,
SUM(reservations.covers) filter (where reservations.status = ‘no_show’) as No_Show
from reservations
join restaurants on restaurants.id = reservations.restaurant_id
join regions on regions.id = restaurants.region_id
where
restaurants.call_center = ‘TRUE’
and reservations.origin != ‘in_house’
and regions.country_code != ‘ZZ’
[[and restaurants.name != ‘{{Name}}’]]
[[and restaurants.relationship_type != ‘{{Relationship}}’]]
[[and reservations.origin != ‘{{Status}}’]]
[[and reservations.referrer_tag != (’{{Referrer}}’)]]
[[and regions.name != ‘{{Region}}’]]
group by day
order by day desc
limit 30