Difficulty filtering by Select Column

I’m going to apologize in advance. I basically stumble through SQL as projects need done but my knowledge is rather lacking, so I apologize for any incorrect terminology or poor syntax. I would appreciate it if anyone would be able to help me out.

I have the following query.

WITH BusinessDayCalc
AS
(
    SELECT
            EstimatedClosingDate AS EstimatedClosingDate
              from SampleDB

)
SELECT
    a.*,
    (DATEDIFF(dd, GETDATE(), EstimatedClosingDate) + 1)
  -(DATEDIFF(wk,GETDATE(), EstimatedClosingDate) * 2)
  -(CASE WHEN DATENAME(dw, GETDATE()) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, EstimatedClosingDate) = 'Saturday' THEN 1 ELSE 0 END)
  -(Select Count(*)  FROM Holiday Where Date >= GETDATE() AND Date < EstimatedClosingDate)

  AS BusinessDaysUntil
FROM BusinessDayCalc a
Where EstimatedClosingDate > GetDate() AND EstimatedClosingDate < (GetDate()+17)

I have also attached pics of the current Output and the Holiday Table that is being referenced.

My issue is that I would like to be able to filter my data to show any data that is 8 or 12 business days out, however, I am unable to pull through the column name or have SQL recognize the BusinessDaysUntil column.

Would someone be able to help me out? Once I get this squared away, the rest of the project should go smoothly.

Answer

You can’t use a derived column in the WHERE clause.
Also you are using a rather useless CTE which returns only 1 column of the table SampleDB.
Instead create a CTE with the query and then select from it and filter:

WITH cte AS (
  SELECT
    EstimatedClosingDate,
    (DATEDIFF(dd, GETDATE(), EstimatedClosingDate) + 1)
    -(DATEDIFF(wk,GETDATE(), EstimatedClosingDate) * 2)
    -(CASE WHEN DATENAME(dw, GETDATE()) = 'Sunday' THEN 1 ELSE 0 END)
    -(CASE WHEN DATENAME(dw, EstimatedClosingDate) = 'Saturday' THEN 1 ELSE 0 END)
    -(CASE WHEN DATENAME(dw, EstimatedClosingDate) = 'Saturday' THEN 1 ELSE 0 END)
    -(Select Count(*)  FROM Holiday Where Date >= GETDATE() AND Date < EstimatedClosingDate) AS BusinessDaysUntil
  FROM SampleDB
  WHERE EstimatedClosingDate > GetDate() AND EstimatedClosingDate < (GetDate()+17)
)
SELECT * FROM cte
WHERE BusinessDaysUntil > ?

Replace BusinessDaysUntil > ? with the filter that you want to apply.