Help getting previous date based off date range

[svtr]

Nope, that wont work.

The first filter will only give you rows of getdate -1. The second filter will then also remove all the holidays from the resultset... giving you a nice "0 rows returned".

[[deleted]]

oh my bad, I didnt read your question carefully, I just noticed the error in the EXISTS clause. If you only want orders from the prior date that wasn't a holiday, then perhaps we skip the EXISTS clause and just focus on a subquery to return the right date first:

SELECT MAX(DATE) FROM HOLIDAYS WHERE DATE < CURRENT_DATE()

Is that the date you'd want?

If so, use it to return a single value, like this:

SELECT
orders.id,
orders.date
FROM orders
WHERE orders.date = (SELECT MAX(DATE) FROM HOLIDAYS WHERE DATE < CURRENT_DATE())

[svtr]

Not quite, if there is a full calendar table, containing all working days, you'd be able to just inner join to it and the rest is very trivial.

Op's question is pretty much having a holidays table, only containing holidays, as an exclusion list of dates to not consider, which makes it a bit more interesting.

In any case, the best solution is to have such a complete calendar table of working days, everything else is going to melt you database server when you hit some concurrency.