CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
JetJack
JetJack

Reputation: 988

How to find all fridays and holidays between two dates

The table:

hDate      Holiday

17/12/2011 National Day
01/01/2012 New Year
....

From the table, i want to find the total number of holidays between two dates:

A query like:

select count(hdate)
from table1
where hdate between '" & start_date & "' and '" & end_date & "'

User input:

start_date = '16/12/2011' 
end_date = '15/01/2012'

and also I want to find the friday between 2 dates.

For finding fridays, how to create a query?

Expected output:

Holiday Friday

2       5

[2] - 2 days holiday from table1, [5] - 5 days friday

How to do this?

Upvotes: 0

Views: 7835

Answers (7)

onedaywhen
onedaywhen

Reputation: 57093

See:

Why should I consider using an auxiliary calendar table?

A calendar table can make it much easier to develop solutions around any business model which involves dates. Last I checked, this encompasses pretty much any business model you can think of, to some degree. Constant problems that end up requiring verbose, complicated and inefficient methods include the following questions:

  • How many business days between x and y?
  • What are all of the dates between the second Tuesday of March and the first Friday in April? 
  • On what date should I expect this shipment to arrive? 
  • What were the dates of all the Fridays in this quarter? 
  • ...  

Upvotes: 2

devers
devers

Reputation: 31

That select help you:

DECLARE @FROMDATE DATE = '2009-01-07'
DECLARE @TODATE DATE = '2012-01-26'

SELECT COUNT(*) holidays,(select COUNT(*) from table1 where DATEPART(DW, hdate) = 5
AND DT BETWEEN @FROMDATE AND @TODATE ) fridays FROM table1 
WHERE hdate BETWEEN @FROMDATE AND @TODATE

Upvotes: 2

Andreas Rohde
Andreas Rohde

Reputation: 609

We solving the problem with an extra time table. This looks like this

ID   | Date        | Holiday   | Year    | CalendarWeek | DayName
1    | 17/12/2011  | 1         | 2011    | 50           | Monday 
2    | 18/12/2011  | 0         | 2011    | 50           | Thursday
3    | 19/12/2011  | 0         | 2011    | 50           | Wendsday

With this table you could resolve your question like this

select 
(select count(d.DayName) from date_table as d 
     where d.DayName = 'Friday' and date >= start_date and date <= end_date ),
(select sum(d.Holiday) from date_table as d 
     where date >= start_date and date <= end_date )

This should also be SQL Server 2000 compatible. And this for SQL Server 2005 and above:

with tmp(id) as
(
 select id from  from date_table where date >= start_date and date <= end_date
)
select
 (select count(d.DayName) from date_table inner join tmp on tmp.id = id 
     where DayName = 'Friday' ),
 (select sum(d.Holiday) from date_table inner join tmp on tmp.id = id )

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44336

This counts the fridays between 2 dates:

declare @from datetime= '2012-01-26'  
declare @to datetime  = '2012-01-28'

select datediff(day, -3, @to)/7-datediff(day, -2, @from)/7

The holidays are easy to find, it seems like you have that part covered already.

I sort of answered this earlier. But didn't get any credit:

How to calculate the number of "Tuesdays" between two dates in TSQL?

Upvotes: 8

Somnath Muluk
Somnath Muluk

Reputation: 57816

select count(Holiday) as holiday
from Table1
where date between start_date AND end_date

Upvotes: 1

Vijin Paulraj
Vijin Paulraj

Reputation: 4648

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='16/12/2011' 
SET @EndDate = '15/01/2012' 


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Friday' THEN 1 ELSE 0 END)

Upvotes: 1

Heinzi
Heinzi

Reputation: 172468

  1. For the holiday, your SQL looks fine, you just seem to have trouble plugging the parameters into the SQL. If you specify which programming language you are using, we might be able to help here. If you use .NET, you should use Parameterized Queries instead of string substitution.

  2. For the fridays, see this question:

Upvotes: 1

Related Questions