Count working days (not including weekends)

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 2008 Forums

Transact-SQL (2008)

Count working days (not including weekends)

Author

Topic

n3w2sql
Starting Member

4 Posts

Posted - 2012-08-17 : 10:08:29

I am trying to count the number of days between 2 fields but I dont want the weekends to be included is this possible if so can someone please explain? Thanks in advance.
This is my code:

SELECT
POLICYNUM
,cast(CREATEDATE as DATE) CREATEDATE
,substring(cast(b.incpdt as char),7,6) Incept_year
,cast (B.DateMadeLive as DATE) DateMadeLive
,cast (B.IncpDt as DATE) IncpDt
,CASE WHEN b.DateMadeLive > b.IncpDt THEN DATEDIFF (WEEKDAY, b.DateMadeLive,CREATEDATE)
ELSE DATEDIFF (WEEKDAY, b.IncpDt ,CREATEDATE) END Number_of_days,
CASE WHEN(CASE WHEN b.DateMadeLive > b.IncpDt THEN DATEDIFF (WEEKDAY, b.DateMadeLive,CREATEDATE)
ELSE DATEDIFF (WEEKDAY, b.IncpDt ,CREATEDATE) END ) > 10 THEN 1 ELSE 0 END Late

FROM [LEE_em].[dbo].[ALLPOLICYNAMES] a
inner join EM_RefData.dbo.EDW_DimReferenceAll b
on a.POLICYNUM = b.PolicyReference
where substring(cast(b.incpdt as char),7,6) >=2012

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 10:22:06

you can use logic like below


declare @start date='20120805',@end date='20120820'

select datediff(dd,@start,@end) - (datediff(wk,@start,@end)*2 +case when datename(dw,@start) in ('Saturday','Sunday') then 1 else 0 end+case when datename(dw,@end) in ('Saturday','Sunday') then 1 else 0 end)+1

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-08-17 : 12:12:55

It is often convenient to have a Calendar table in your system. One application of its use would be have a IsWeekday column in that table that you could SUM() over a date range.[CODE]create table Calendar (
DayDate date not null,
IsWeekday int not null,
<Other columns>
)

select sum(IsWeekday) NumberOfWeekdays
from Calendar
where DayDate between <StartDate> and <EndDate>[/CODE]Its a simple example here but once you have a Calendar table, you'll be surprised at how useful and efficient it is.

=================================================
Show me a sane man and I will cure him for you. -Carl Jung, psychiatrist (1875-1961)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources