How Do I Count Business or Work Days Between Two D

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How Do I Count Business or Work Days Between Two D

Author

Topic

musicalstone
Starting Member

4 Posts

Posted - 2007-07-14 : 12:13:38

How Do I Count Business or Work Days Between Two Dates? Any suggests are useful. Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-14 : 12:17:49

1) Use a calendar table and use the F_TABLE_DATE function to create it.
2) Add a column named WorkDay as bit. Update the column accordingly.

Now select count(*) from yourtablenamehere where DATE between @a and @b and workday = 1

Peter Larsson
Helsingborg, Sweden

musicalstone
Starting Member

4 Posts

Posted - 2007-07-14 : 12:29:05

I have a table like this:

Create Table BusinessCalendar
(BDate smalldatetime Primary Key, BType tinyint)
Go

Declare @startdate smalldatetime, @enddate smalldatetime
Set @startdate='dec 12 2002'
Set @enddate='jan 17 2003'

Select BDays=(@enddate - @startdate)
From BusinessCalendar
Where BType=1
And BDate Between @startdate And @enddate

--> but its return 0
I dont understand .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-14 : 14:39:03

Think about it! What are you selecting?
You are selecting the difference in days between the two parameters. How many times are you returning this number? Yes, as many records as there are between the two parameters!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-14 : 14:40:18

Select count(*) AS BDays
From BusinessCalendar
Where BType = 1 And BDate Between @startdate And @enddate

Peter Larsson
Helsingborg, Sweden

musicalstone
Starting Member

4 Posts

Posted - 2007-07-19 : 10:05:31

Thanks Peso

musicalstone
Starting Member

4 Posts

Posted - 2007-07-19 : 10:36:06

and another way
CREATE TABLE Test
(
ID int primary key,
FromDate datetime,
ToDate datetime
)
SELECT datediff(d, FromDate, ToDate) FROM Test WHERE ID =1

so easier

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources