Q:

SQL Server : Join If Between

I have 2 tables:

Query1: contains 3 columns, Due_Date, Received_Date, Diff
where Diff is the difference in the two dates in days
QueryHol with 2 columns, Date, Count

This has a list of dates and the count is set to 1 for everything. All these dates represent public holidays. 
I want to be able to get the sum of QueryHol["Count"] if QueryHol["Date"] is between Query1["Due_Date"] and Query1["Received_Date"]
Result Wanted: a column joined onto Query1 to state how many public holidays fell into the date range so they can be subtracted from the Query1["Diff"] column to give a reflection of working days.

Because the 01-01-19 is a bank holiday i would want to minus that from the Diff to end up with results like below

Let me know if you require any more info.

A:

Here's an option:
SELECT query1.due_date
, query1.received_date
, query1.diff
, queryhol.count
, COALESCE(query1.diff - queryhol.count, query1.diff) as DiffCount

FROM Query1

OUTER APPLY(
 SELECT COUNT(*) AS count
 FROM QueryHol
  WHERE QueryHol.Date <= Query1.Received_Date
  AND QueryHol.Date >= Query1.Due_Date
) AS queryhol

You may need to play around with the join condition - as it is assumes that the Received_Date is always later than the Due_Date which there is not enough data to know all of the use cases.

