<div>
&gt;This is my calendar table. 
Check out mine --&gt; &nbsp;<a href="https://www.experts-exchange.com/articles/12267/SQL-Server-Calendar-Table.html" rel="ugc">SQL Server Calendar Table</a> 
 
&gt;&nbsp;I want to return a previous day where it is not weekend or holiday. 
In mine that would be... 
 
SELECT MAX(PKDate) 
FROM calendar 
WHERE PKDate &lt;&nbsp;[Enter the date we're talking about here] 
&nbsp; &nbsp;AND is_weekend = 0 AND is_holiday = 0
</div>

<div>
The weekend you can pick off using DATEPART(Weekday, your_date) NOT IN (1, 7). 
 
Holidays are not something SQL Server does, as they're customized all over the world and dependant on what you personally care about, so the only way to pull that off is to write custom T-SQL to determine what's a holiday and what's not, update a column in your calendar table accordingly, and then query that column.
</div>

<div>
It's not clear how you're using your holiday table, but here is an example of how to test for whether a date exists in your holiday table or whether it's a weekend, and when true, return the day before: 
 

<pre><code class="code-10 nolinks" id="code-20-41387479-1">CREATE TABLE #tmp (
Holiday datetime
)
INSERT INTO #tmp VALUES('2015-01-01')
INSERT INTO #tmp VALUES('2015-05-25')
INSERT INTO #tmp VALUES('2015-07-04')
INSERT INTO #tmp VALUES('2015-09-07')
INSERT INTO #tmp VALUES('2015-11-26')
INSERT INTO #tmp VALUES('2015-12-25')

DECLARE @BaseDate DATETIME = DATEADD(DAY,-2,DATEDIFF(DAY,0,GETDATE()));
SELECT @BaseDate; --returns 2015-12-26 00:00:00.000
DECLARE @TestDate DATETIME;
SELECT @TestDate = CASE WHEN DATEPART(DW, @BaseDate) = 1 THEN DATEADD(DAY,-2,@BaseDate) WHEN DATEPART(DW, @BaseDate) = 7 THEN DATEADD(DAY,-1,@BaseDate) ELSE @BaseDate END;
SELECT @TestDate; --returns 2015-12-25 00:00:00.000
SELECT (CASE WHEN @TestDate IN (SELECT Holiday FROM #tmp) THEN DATEADD(DAY,-1,@TestDate) ELSE @TestDate END); --returns 2015-12-24 00:00:00.000
</code></pre>
</div>

<div>
currently, these are the dates I have on my Holiday table. 
 
01/01/2015 
05/25/2015 
07/04/2015 
09/07/2015 
11/26/2015 
12/25/2015 
and on Jan 1 2016, the table would be 
01/01/2016 
05/25/2016 
07/04/2016 
09/07/2016 
11/26/2016 
12/25/2016 
 
I want to check if the previous date is not falls on weekend and it does not exist on my Holiday table. 
Today is 12/28/2015 if should return 12/24/2015. I tried below, and it returned 12/24/2015. What if I replaced getdate() with 12/29/2015? It still returned 12/24/2015? 
 
 
DECLARE @BaseDate DATETIME = DATEADD(DAY,-2,DATEDIFF(DAY,0, &nbsp;getdate())); 
--SELECT @BaseDate; 
DECLARE @TestDate DATETIME; 
SELECT @TestDate = CASE WHEN DATEPART(DW, @BaseDate) = 1 THEN DATEADD(DAY,-2,@BaseDate) 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; WHEN DATEPART(DW, @BaseDate) = 7 THEN DATEADD(DAY,-1,@BaseDate) 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; ELSE @BaseDate END; 
--SELECT @TestDate; &nbsp; 
SELECT (CASE WHEN @TestDate IN (SELECT EventDate FROM tbl_holiday) THEN DATEADD(DAY,-1,@TestDate) 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; ELSE @TestDate END); return 12/24/2015
</div>

<div>
I think you misunderstand what you should change in order to test for 12/29/2015. &nbsp;Change this line: 
 

<pre><code class="code-1 nolinks" id="code-20-41387523-1">DECLARE @BaseDate DATETIME = DATEADD(DAY,-2,DATEDIFF(DAY,0,GETDATE()));
</code></pre>
 
 
To this: 

<pre><code class="code-1 nolinks" id="code-20-41387523-2">DECLARE @BaseDate DATETIME = DATEADD(DAY,1,DATEDIFF(DAY,0,GETDATE()));
</code></pre>
 
@BaseDate is a simulation of whatever date you'd like to start with. &nbsp;In the case of 12/29/2015, my query returns 12/29/2015. 
 
It's not clear how you want your query to work, so all I can provide is a simulation of how we get the starting date. 
 
<a href="https://filedb.experts-exchange.com/incoming/2015/12_w01/1061481/2015-12-28_1506.png" target="_blank" title="results (56 KB)"><img alt="results" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2015/12_w01/800_1061481/2015-12-28_1506.png" /></a>
</div>

2015-12-28_1506.png

<div>
DECLARE @BaseDate DATETIME = DATEADD(DAY,-1,DATEDIFF(DAY,0, getdate())); 
--SELECT @BaseDate; &nbsp; 
DECLARE @TestDate DATETIME; 
SELECT @TestDate = CASE WHEN DATEPART(DW, @BaseDate) = 1 THEN DATEADD(DAY,-2,@BaseDate) 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; WHEN DATEPART(DW, @BaseDate) = 7 THEN DATEADD(DAY,-1,@BaseDate) 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; ELSE @BaseDate END; 
--SELECT @TestDate; 
SELECT (CASE WHEN @TestDate IN (SELECT EventDate FROM tbl_holiday) THEN DATEADD(DAY,-1,@TestDate) 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; ELSE @TestDate END); &nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 
This does return &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2015-12-24 00:00:00.000 &nbsp; 
If I were to run the same query above tomorrow (12/29/2015), it should return 2015-12-28 00:00:00.000?
</div>

<div>
<div class="content wysiwyg-content">
This is my calendar table. I want to return a previous day where it is not weekend or holiday. 
Sample today is 12/28/2015 and last weekend 12/26/2015 and 12/27/2015. However, Friday (12/25/2015) was a holiday.I want it to return 12/24/2015 instead. 
 
my Holiday table 
 
01/01/2015 
05/25/2015 
07/04/2015 
09/07/2015 
11/26/2015 
12/25/2015
</div>
</div>

This is my calendar table. I want to return a previous day where it is not weekend or holiday. 
Sample today is 12/28/2015 and last weekend 12/26/2015 and 12/27/2015. However, Friday (12/25/2015) was a holiday.I want it to return 12/24/2015 instead. 

my Holiday table

01/01/2015
05/25/2015
07/04/2015
09/07/2015
11/26/2015
12/25/2015

Exclude weekend and holiday from a holiday table in sql

The successor to Active Server Pages, ASP.NET websites utilize the .NET framework to produce dynamic, data and content-driven web applications and services. ASP.NET code can be written using any .NET supported language. As of 2009, ASP.NET can also apply the Model-View-Controller (MVC) pattern to web applications

ASP.NET

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.