quote:

---

Originally posted by siumui

Hello all.

I've been searching for a quite a while but still having a hard time to digest all the codes that calculate the number business days between two dates.

I have two dates column in a table which I need to find out the # of business days (Monday to Friday) between the two dates, excluding holidays. From searching on the internet, I know that I have to create table that has all the holidays.

Please help. Thank you.

---

SELECT (DATEDIFF(dd,t.Start,t.End) - h.Cnt - (2* DATEDIFF(wk,Start,End)))+1 AS BusinessDayCount
FROM table t
CROSS APPLY (SELECT COUNT(*) AS Cnt
             FROM Holiday
             WHERE [Date] >= Start
             AND [date]< End +1
            )h

CREATE TABLE [dbo].[dim_Date](
	[DateID] [int] NOT NULL,
	[DatetimeStamp] [datetime] NULL,
	[DateStamp] [date] NULL,
	[YearNr] [smallint] NULL,
	[YearMonth] [int] NULL,
	[YearQuarter] [varchar](50) NULL,
	[QuarterNr] [smallint] NULL,
	[MonthNr] [smallint] NULL,
	[WeekNr] [smallint] NULL,
	[DayNr] [smallint] NULL,
	[DayLongName] [varchar](50) NULL,
	[MonthLongName] [varchar](50) NULL,
	[DayShortName] [varchar](3) NULL,
	[MonthShortName] [varchar](3) NULL,
	[DayNrInYear] [smallint] NULL,
	[DayNrInWeek] [smallint] NULL,
	[DayDescription] [varchar](500) NULL,
	[WorkDayFactor] [decimal](2, 1) NULL,
 CONSTRAINT [PK_dim_date] PRIMARY KEY CLUSTERED 
(
	[DateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ_dim_date] UNIQUE NONCLUSTERED 
(
	[DatetimeStamp] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


INSERT INTO dim_Date SELECT DateID=-1, DatetimeStamp='1900-01-01 00:00:00.000', DateStamp='1900-01-01', YearNr=-1, YearMonth=-1, YearQuarter='-1', QuarterNr=-1, MonthNr=-1, WeekNr=-1, DayNr=-1, DayLongName='Unknown', MonthLongName='Unknown', DayShortName='Unk', MonthShortName='Unk', DayNrInYear=-1, DayNrInWeek=-1, DayDescription='Date is missing / unknown', IsBusinessDay=0
INSERT INTO dim_Date SELECT DateID=19950101, DatetimeStamp='1995-01-01 00:00:00.000', DateStamp='1995-01-01', YearNr=1995, YearMonth=199501, YearQuarter='1995 Q1', QuarterNr=1, MonthNr=1, WeekNr=0, DayNr=1, DayLongName='Sunday', MonthLongName='January', DayShortName='Sun', MonthShortName='Jan', DayNrInYear=1, DayNrInWeek=7, DayDescription=null, IsBusinessDay=0
INSERT INTO dim_Date SELECT DateID=19950102, DatetimeStamp='1995-01-02 00:00:00.000', DateStamp='1995-01-02', YearNr=1995, YearMonth=199501, YearQuarter='1995 Q1', QuarterNr=1, MonthNr=1, WeekNr=1, DayNr=2, DayLongName='Monday', MonthLongName='January', DayShortName='Mon', MonthShortName='Jan', DayNrInYear=2, DayNrInWeek=1, DayDescription=null, IsBusinessDay=1
INSERT INTO dim_Date SELECT DateID=19950103, DatetimeStamp='1995-01-03 00:00:00.000', DateStamp='1995-01-03', YearNr=1995, YearMonth=199501, YearQuarter='1995 Q1', QuarterNr=1, MonthNr=1, WeekNr=1, DayNr=3, DayLongName='Tuesday', MonthLongName='January', DayShortName='Tue', MonthShortName='Jan', DayNrInYear=3, DayNrInWeek=2, DayDescription=null, IsBusinessDay=1
INSERT INTO dim_Date SELECT DateID=19950104, DatetimeStamp='1995-01-04 00:00:00.000', DateStamp='1995-01-04', YearNr=1995, YearMonth=199501, YearQuarter='1995 Q1', QuarterNr=1, MonthNr=1, WeekNr=1, DayNr=4, DayLongName='Wednesday', MonthLongName='January', DayShortName='Wed', MonthShortName='Jan', DayNrInYear=4, DayNrInWeek=3, DayDescription=null, IsBusinessDay=1
INSERT INTO dim_Date SELECT DateID=19950105, DatetimeStamp='1995-01-05 00:00:00.000', DateStamp='1995-01-05', YearNr=1995, YearMonth=199501, YearQuarter='1995 Q1', QuarterNr=1, MonthNr=1, WeekNr=1, DayNr=5, DayLongName='Thursday', MonthLongName='January', DayShortName='Thu', MonthShortName='Jan', DayNrInYear=5, DayNrInWeek=4, DayDescription=null, IsBusinessDay=1
INSERT INTO dim_Date SELECT DateID=19950106, DatetimeStamp='1995-01-06 00:00:00.000', DateStamp='1995-01-06', YearNr=1995, YearMonth=199501, YearQuarter='1995 Q1', QuarterNr=1, MonthNr=1, WeekNr=1, DayNr=6, DayLongName='Friday', MonthLongName='January', DayShortName='Fri', MonthShortName='Jan', DayNrInYear=6, DayNrInWeek=5, DayDescription=null, IsBusinessDay=1
INSERT INTO dim_Date SELECT DateID=19950107, DatetimeStamp='1995-01-07 00:00:00.000', DateStamp='1995-01-07', YearNr=1995, YearMonth=199501, YearQuarter='1995 Q1', QuarterNr=1, MonthNr=1, WeekNr=1, DayNr=7, DayLongName='Saturday', MonthLongName='January', DayShortName='Sat', MonthShortName='Jan', DayNrInYear=7, DayNrInWeek=6, DayDescription=null, IsBusinessDay=1
INSERT INTO dim_Date SELECT DateID=19950108, DatetimeStamp='1995-01-08 00:00:00.000', DateStamp='1995-01-08', YearNr=1995, YearMonth=199501, YearQuarter='1995 Q1', QuarterNr=1, MonthNr=1, WeekNr=1, DayNr=8, DayLongName='Sunday', MonthLongName='January', DayShortName='Sun', MonthShortName='Jan', DayNrInYear=8, DayNrInWeek=7, DayDescription=null, IsBusinessDay=1
INSERT INTO dim_Date SELECT DateID=19950109, DatetimeStamp='1995-01-09 00:00:00.000', DateStamp='1995-01-09', YearNr=1995, YearMonth=199501, YearQuarter='1995 Q1', QuarterNr=1, MonthNr=1, WeekNr=2, DayNr=9, DayLongName='Monday', MonthLongName='January', DayShortName='Mon', MonthShortName='Jan', DayNrInYear=9, DayNrInWeek=1, DayDescription=null, IsBusinessDay=1
INSERT INTO dim_Date SELECT DateID=19950110, DatetimeStamp='1995-01-10 00:00:00.000', DateStamp='1995-01-10', YearNr=1995, YearMonth=199501, YearQuarter='1995 Q1', QuarterNr=1, MonthNr=1, WeekNr=2, DayNr=10, DayLongName='Tuesday', MonthLongName='January', DayShortName='Tue', MonthShortName='Jan', DayNrInYear=10, DayNrInWeek=2, DayDescription=null, IsBusinessDay=1