ON - Syntax - SERVOS

Most schemas can date a row more than one way. A room night has the night itself and the day it was booked. A cancelled booking has its booking, arrival and cancellation days. ON picks the date that the range, time groupings such as GROUP BY month, and comparisons use. Each schema has a default, so most queries leave ON out.

ON <time axis>

Time axes

Schema	Time axes
`rooms`	`stay_night` (default): the night itself. `booking_date`: the day the stay was booked.
`revenue`	`service_date` (default): the day the service happens, with a stay spread over its nights. `booking_date`: the day the line was booked. `posting_date`: the day the order was booked, or the day a fee was charged.
`activities`	`service_date` (default), `booking_date` and `posting_date`, as in `revenue`.
`bookings`	`booking_date` (default), `arrival_date`, `departure_date` and `cancellation_date`.
`payments`	`payment_date`: the day the money moved.
`customers`	`first_order_date` (default): the day of the customer's first order. `customer_added_date`: the day the customer was added to SERVOS.
`activity_capacity`	`service_date`: the day the event or class runs.

Room revenue booked last month

By default, rooms counts the nights in the range. ON booking_date counts what was booked in the range instead, whatever night it is for.

FROM rooms
  SHOW room_revenue, room_nights_sold
  ON booking_date
  DURING last_month

Cancellations by the day they happened

FROM bookings
  SHOW cancellations
  ON cancellation_date
  DURING last_month
  GROUP BY cancellation_source

Arrivals over the next week

FROM bookings
  SHOW stays, guests
  ON arrival_date
  DURING next_7_days
  GROUP BY day

Metrics with their own dates

Some metrics only make sense on some axes. Occupancy is about nights, so it can't be dated by booking day:

FROM rooms
  SHOW occupancy
  ON booking_date
  DURING last_month

A few metrics have their own default. departures is dated by departure_date and checked_in_stays by arrival_date. Next to a metric dated by booking_date, the query must say which date it means:

FROM bookings
  SHOW stays, checked_in_stays
  DURING last_month

FROM bookings
  SHOW stays, checked_in_stays
  ON arrival_date
  DURING last_month

Each schema page lists the axes every metric can use.