CODEWITHSUNDEEP
sqlt-sql
Charles Naughton
Charles Naughton

Reputation: 148

Calculating number of holidays between two dates in table using SQL

I have a table that has two date fields (Date 1 and Date 3 in example below). I also have a Holiday table that lists all of the holidays. I would like to find an approach in a SQL query to be able to count the number of holidays between the two date values. Any ideas?

Example table:

Date 1          Date 2
5/1/2012        5/4/2012
8/31/2012       9/25/2012
12/23/2011      12/27/2011

Example of Date Dimension table:

Holiday Date

12/25/2011
1/1/2012
7/4/2012
9/3/2012

Ideal output:

Date 1          Date 2           Holidays
5/1/2012        5/4/2012         0
8/31/2012       9/25/2012        1
12/23/2011      1/10/2012        2

Upvotes: 1

Views: 4106

Answers (2)

slashmais
slashmais

Reputation: 7155

The following should do it:

select date1, date2, t.total
from datestable, (select count(*) as total 
                  from holidaystable 
                  inner join datestable 
                  on holidaydate between date1 and date2) t;

Upvotes: 1

wallyk
wallyk

Reputation: 57774

Something like this:

SELECT count (holidays.date) AS holiday_count
FROM holidays, table
WHERE holidays.date BETWEEN table.date_1 AND table.date_2

Upvotes: 1

Related Questions