How to check billboard availability for a future date

To see which billboards are free for a campaign starting next quarter, ask for availability on the campaign’s start date, not today’s. If your inventory list marks sites as booked when those bookings end before your campaign begins, it is answering a question about this morning. Pick a site you know frees up in six weeks and see whether your system can show it as free from then.

Here is how that looked at one business, and what fixing it took.

A salesperson building a proposal for a campaign starting in two months was looking at a list that labelled half the inventory as booked. All of those bookings ended before the campaign would begin. The list was not wrong about anything; it was answering a different question from the one being asked.

Availability had been built as a property of a site: for each site, find the first active booking whose window covers today, and if there is one, the site is not available. One flag, computed against the current date, rendered as a chip.

That is the right answer for the operations screen, where somebody is looking at what is on the boards right now. It is the wrong answer everywhere a sale is being planned, which is most of the places anyone actually uses it.

Make the date an argument

The change is small to describe and it is the whole article. Availability takes a date. The picker in the proposal builder gained an “available by” field. The covering-booking lookup uses that date instead of today. Nothing else about the query changes — it is still “find the booking whose window contains this day” — but the day is now supplied by the caller rather than assumed by the server.

Default it to today, so every existing caller keeps its current behaviour and no client has to be updated in the same release. Validate the format strictly and fall back to today when it does not match, which is worth doing carefully here because the value is interpolated into a subquery.

The result is that the picker stops answering “what is free now” and starts answering “what would be free for the campaign I am about to pitch”, and the chips read available now, ongoing, or available from a date.

The sort is where it gets interesting

Once availability is a date question, sorting by it stops being a column sort. There is no availability column. What exists is a correlated subquery per row — the end date of the booking that covers the chosen date, or nothing at all if no booking covers it.

Sorting on that expression means deciding where the rows with nothing go, and the answer is not fixed. A salesperson sorting ascending wants the free sites first, then the ones freeing soonest, so nulls lead. Sorting descending means “show me the most committed inventory”, so nulls trail. The null placement flips with the direction. If you set it once and leave it, one of the two directions puts the free sites at the bottom of a hundred-row list and the feature looks broken in exactly half its uses.

That is a general point about derived sorts: the absence of a value is not neutral, and which end it belongs at depends on what the sort means to the person reading it. Ascending and descending are not mirror images once nulls are involved.

What I got wrong

My first version computed availability in the application layer. Fetch the page of sites, fetch the bookings that cover the date for those sites, stitch them together, attach a flag. It is easy to read and it was already there for the dropdown.

It cannot sort. Sorting has to happen before pagination, and the value being sorted on does not exist until after the rows are fetched. So the first page of a sorted-by-availability list was sorted within the page and no further, which looks convincing for ten rows and is nonsense at a hundred. That is a category of bug worth naming, because it always looks fine in testing with small data: a sort applied after pagination sorts the wrong set, and the only way to notice is to have more rows than one page.

The stitching stayed for the small dropdown payload, where there is no pagination and no sort, and the subquery form was written for the paginated list. Two implementations of the same idea is a cost I accepted deliberately rather than accidentally, and the reason is that they answer at different grains.

The chip has to say more than yes or no

Once the underlying answer is “free from this date”, the interface cannot go on rendering a boolean. Three states are needed and they are genuinely different to a salesperson: free right now, committed until a date they can read, and committed with no end agreed. That last one is the open-ended booking, and it is the one a two-state chip gets most wrong, because rendering it as “booked until” and printing a sentinel date is worse than saying nothing.

There is also the case where a site can hold several concurrent adverts, and then the honest chip is a fraction rather than a state. That is a separate change, but it lands on the same surfaces, and it is the reason I would now treat any availability chip as a slot for a short phrase rather than a red-or-green dot.

If a column in your interface answers a question about time, check whose time it is. A field team asks about today. A salesperson asks about the quarter after next. A finance report asks about a period that closed last month. The same query with a different date argument serves all three; a boolean computed on the server’s clock serves one of them and misleads the other two.

The limit I would flag: availability here still means “is there a booking covering that day”, which is not the same as “is the whole campaign window free”. A site free on the start date and committed a fortnight later shows as available, and the conflict only appears at save. Answering properly needs the whole window in the query, and that has not been done.

How to check billboard availability in your own system

1. Choose a campaign start date in the future and ask for the sites free on that date.
2. Pick a site whose booking ends before that date. It should show as free.
3. Pick a site booked past that date. It should show the date it frees up.
4. Check an open-ended booking shows as having no end agreed, not as a made-up date.
5. Remember the answer covers the start date only. Ask whether the whole campaign window is free before you confirm.

Where this ends up

That is what availability now means in Sazinga AdBoard: a question that takes the date you are selling for, answered the same way for the field team looking at this morning and the salesperson pitching the quarter after next.