datedif and exclude weekends and holidays from datediff 1

[wvdba]

hi.
i have a situation where a user wants to calculate the difference between two dates in a table in a database. example: september 1 to september 19. (18 days).
but she wants to exclude the weekends and holidays from those 18 days. i understand that holidays (14 of them) can be put in a table of some sort. i have not been able to come up with a solution to this. any ideas?
thanks.

 

[gmmastros]

Do you have a numbers table in your database? A numbers table can help a lot here. If you don't have a numbers table, would you be willing to use a couple megabytes to include one?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wvdba]

thanks,
gmmastros for the reply.
space is not and issue.
what's a numbers table, please?

 

[gmmastros]

A numbers table is simply a table with one column and lots of rows, one for each number. Ex:

[tt]
Numbers
Num
---
1
2
3
etc...
[/tt]

We can do ourselves one better and add another column for Date, and even another column for IsHoliday.

To build your numbers table... (expect this to take a couple minutes)

Create Table dbo.Numbers(Num int, Date DateTime, IsHoliday Bit, IsWeekend Bit, Constraint PK_Numbers Primary Key (Num))
-- For SQL2005 and above
Create Index ix_Numbers_Date ON dbo.Numbers(Date) Include (IsHoliday, IsWeekend)
--For SQL2000
--Create Index ix_Numbers_Date ON dbo.Numbers(Date, IsHoliday)

go

;With Digits as
(
	Select 0 As Num
	Union All Select 1
	Union All Select 2
	Union All Select 3
	Union All Select 4
	Union All Select 5
	Union All Select 6
	Union All Select 7
	Union All Select 8
	Union All Select 9
)
Insert
Into	Numbers(Num)
Select	A.Num * 10000 + B.Num * 1000 + C.Num * 100 + D.Num * 10 + E.Num As Num
From	Digits A
        Cross Join Digits B
        Cross Join Digits C
        Cross Join Digits D
        Cross Join Digits E

Update  Numbers 
Set     Date = DateAdd(Day, Num, 0),
        IsWeekend = Case When Num % 7 = 5 or Num % 7 = 6 Then 1 Else 0 End,
        IsHoliday = 0

After you build the table, you'll want to set your holidays. You can use the functions in this FAQ's to help populate the IsHoliday value. faq183-5075

Once you have this table built, querying it is relatively simple. Ex:

Just to get the number of days.

Select Count(*) 
From   dbo.Numbers 
Where  Date Between '2010-09-01' and '2010-09-19'

Get the number of weekdays:

Select Count(*) 
From   dbo.Numbers 
Where  Date Between '2010-09-01' and '2010-09-19'
       And IsWeekend = 0

Get the number of weekdays excluding holidays:

Select Count(*) 
From   dbo.Numbers 
Where  Date Between '2010-09-01' and '2010-09-19'
       And IsWeekend = 0
       And IsHoliday =0

Truth is, there are neat tricks to calculate the difference between dates (DateDiff), and also tricks to remove weekends from the calculations. But, once you want to consider removing holidays, this is probably the best (and easiest to understand). Also notice the indexes I created for the table, this table is relatively small and well indexed so the performance should be pretty good.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wvdba]

thanks much.
could this be done in access as well?
i'm thinking that a table (tbl0) like this has to be built using some kind of a script:
cal_date weekend holiday
======== ======= =======
01/01/10 n y
01/02/10 y n
01/03/10 y n
something like these.
then select count(*) from tbl0 where weekend = "n" and holiday = "n";
is this correct?

 

[simian336]

You may also want to consider a calendar table.

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Simi

 

[gmmastros]

wvdba,

I see no reason why this couldn't be done in Access. Loading the table with initial values will be different, and the query may be slightly different, too. The concept is the important part and should work well with whatever database you choose to use.

simian336,
I've seen that page before. Mostly I like it, but there are things in there that I certainly don't like either. The biggest problem I have is with the varchar data in the table. The varchar data more than doubles the size of each row, which means you can only fit 1/2 the number of rows in each data page. This will make the table a little less efficient. Additionally, the varchar data is for monthname and weekday name. There are functions built in to SQL Server to get this information based on the date. If you allow SQL Server to do this, you can actually get the month name and weekday name with alternative languages. If you store this data in the table, you are restricted to the language you used to build the table.

Ex:

set language US_English
Select DateName(Month, GetDate()), DateName(Weekday, GetDate())
Set Language Italian
Select DateName(Month, GetDate()), DateName(Weekday, GetDate())
Set Language Spanish
Select DateName(Month, GetDate()), DateName(Weekday, GetDate())

So, you are using more space and also limiting yourself to a single language. The only possible advantage to storing this data is for filtering purposes. Ex: Select * From Calendar Where WeekdayName = 'Saturday'. If you had an index on this column, with only 7 different values, you will probably get an index scan (instead of a seek) anyway (based on the selectivity of data).

I have similar, but smaller issues with the other columns like Year, Quarter, Month & Day. All of this information can be calculated from the data (using the DatePart function).

Also consider that the table I am suggesting is very similar to a calendar table, except is also has a sequential number so it can also be used for other queries that would benefit by having a simple numbers table.

I'm rambling, forgive me. Calendar tables are nice to have. Numbers tables are nice to have. Once you have one, you'll begin to see other uses for them. I know I have.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wvdba]

thanks so much everyone.
i think i'm on the right track having read all this info.