[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

exclude holidays in sql

I want to exclude holiday from my select query. I have a holiday table. How can I exclude it?

2014-01-01 00:00:00.000      Holiday         New Years Day
2014-05-26 00:00:00.000      Holiday         Memorial Day
2014-07-04 00:00:00.000      Holiday         Independence Day
2014-09-01 00:00:00.000      Holiday         Labor Day
2014-11-27 00:00:00.000      Holiday         Thanksgiving
2014-12-25 00:00:00.000      Holiday         Christmas
0
VBdotnet2005
Asked:
VBdotnet2005
6 Solutions
 
Brian CroweCommented:
It would be helpful if you provide the original query but you can do this with an EXCEPT or with a JOIN depending on what you are trying to do.
0
 
Brendt HessSenior DBACommented:
SELECT *
FROM WhateverTable as t
LEFT JOIN Holidays as h
    ON t.SomeDate >= h.Date
    AND t.SomeDate < Dateadd(day, 1, h.Date)
WHERE h.date is null
0
 
Scott PletcherSenior DBACommented:
I suggest NOT EXISTS, as that allows SQL to generate the best plan:


SELECT ...
FROM main_table mt
...
WHERE
    NOT EXISTS(SELECT 1 FROM holidays h WHERE h.date = mt.some_date)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
VBdotnet2005Author Commented:
What does Select 1 mean?
0
 
HuaMinChenBusiness AnalystCommented:
I suggest you have a holiday table to store the relevant and within your column, you can add such condition
... 
where date_col not in(select holiday from holidaytab)

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Looks like the above experts have the T-SQL covered given an already existing holiday table.  

If you're interested, SQL Server does not have an ISHOLIDAY() function to programmatically define a holiday, and even if that were possible it wouldn't account for country, state, and local differences.   I have an article out there called Build your own SQL calendar table to perform complex date expressions that walks you through creating your own date table where you can programmatically define what is a holiday and what isn't.
0
 
Anthony PerkinsCommented:
HuaMinChen,
I suggest you have a holiday table to store the relevant
You may want to read the question:
I want to exclude holiday from my select query. I have a holiday table.
0
 
Anthony PerkinsCommented:
What does Select 1 mean?
It means just what it states: Return the literal 1.  In much the same way you do SELECT GETDATE() to return the value of the current date time of the system.

But to answer your question (or where I think you were heading with it) "Why do you use SELECT 1?" and that would be because traditionally it is a considered to be far less expensive to do SELECT 1 compared to say SELECT ColumnName or God forbid SELECT * and the result is exactly the same.  Having said that I suspect the SQL Query Optimizer is smart enough to know that if you did use SELECT * in an EXISTS statement, it would probably resolve it to something closer to SELECT 1.  But Scott can probably elaborate better on that.
0
 
Scott PletcherSenior DBACommented:
>> What does Select 1 mean? <<

It's just the simplest SELECT list possible.  All we need to do is check whether or not a row exists, not return any column(s), so the value in the SELECT list is not meaningful, and "SELECT 1" shows that nicely and simply.
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi

Try this

SELECT * from table
WHERE
    transdate not in (SELECT date FROM holidays)

Open in new window

0
 
awking00Commented:
I agree with ScottPletcher that NOT EXISTS is the way to go. I will say that there is some debate as to using select 1 or select null as being the simplest. I think it may depend on the dbms and the difference in any case is likely to be negligible.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now