Working Days - SQL Server Forums

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)

Working Days

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-03 : 02:27:26

I want to get working days between 2dates excluding weekend and holidays. I have holiday table . I made gooling and main issue is if holiday relys on weekend....

pls help me. thanks.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-03 : 03:20:20

[code]
is this u want
DECLARE @fromdate DATETIME, @todate DATETIME

SELECT @fromdate = '2/1/2010',@todate = '2/28/2010'

SELECT COUNT(DATEADD(D,number,@fromdate)) AS WorkingDays
FROM master..spt_Values AS m
WHERE TYPE = 'p' AND DATEADD(D,number,@fromdate) <= @todate
AND DATENAME(dw,DATEADD(D,number,@fromdate)) NOT IN ('Saturday','Sunday')
AND DATEADD(D,number,@fromdate) NOT IN(SELECT holidaydate FROM holiday )
[/code]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-03 : 04:51:21

Solved. thanks.

function [dbo].[DayDiff](@StartDate as datetime,@EndDate as datetime)
returns int
AS
BEGIN

Declare @Workdays int;
SELECT @Workdays =
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw,@EndDate)= 'Saturday' THEN 1 ELSE 0 END)

return @Workdays -(select count(*) from Holiday where DATENAME(dw,DATE) <> 'Saturday' and DATENAME(dw,DATE) <> 'Sunday' and (DATE >= @StartDate and DATE <= @EndDate))

END

quote:

Originally posted by bklr


is this u want 
DECLARE @fromdate DATETIME, @todate DATETIME

SELECT @fromdate = '2/1/2010',@todate = '2/28/2010'

SELECT COUNT(DATEADD(D,number,@fromdate)) AS WorkingDays
FROM master..spt_Values AS m
WHERE TYPE = 'p' AND DATEADD(D,number,@fromdate) <= @todate
AND DATENAME(dw,DATEADD(D,number,@fromdate)) NOT IN ('Saturday','Sunday')
AND DATEADD(D,number,@fromdate) NOT IN(SELECT holidaydate FROM holiday )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:46:18

quote:

Originally posted by bklr


is this u want 
DECLARE @fromdate DATETIME, @todate DATETIME

SELECT @fromdate = '2/1/2010',@todate = '2/28/2010'

SELECT COUNT(DATEADD(D,number,@fromdate)) AS WorkingDays
FROM master..spt_Values AS m
WHERE TYPE = 'p' AND DATEADD(D,number,@fromdate) <= @todate
AND DATENAME(dw,DATEADD(D,number,@fromdate)) NOT IN ('Saturday','Sunday')
AND DATEADD(D,number,@fromdate) NOT IN(SELECT holidaydate FROM holiday )

will cause a problem if dates are more than 5 years apart

try

SELECT @fromdate = '2/1/2000',@todate = '2/28/2010'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:52:54

you need this probably


SELECT f.Date
FROM dbo.CalendarTable(@startdate,@enddate,1)f
LEFT JOIN Holiday h
ON h.datefield = f.[date]
WHERE h.datefield IS NULL

dbo.CalendarTable can be found in below link

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources