Calculating number of holidays between two dates in table using SQL

Question

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

wallyk · Accepted Answer · 2012-10-09 15:55:17Z

1

Something like this:

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

answered Oct 9, 2012 at 15:55

wallyk

58.1k17 gold badges92 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

slashmais · Accepted Answer · 2012-10-09 16:17:52Z

1

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;

answered Oct 9, 2012 at 16:17

slashmais

7,1859 gold badges57 silver badges81 bronze badges

Collectives™ on Stack Overflow

Calculating number of holidays between two dates in table using SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related