Solved

SQL calculation should add working days only (i.e. exclude weekend and public holidays)

Posted on 2009-03-30
2
1,663 Views
Last Modified: 2012-05-06
I have a select statement that currently works like below:

select inwp.cpp_quotation_days + cp.cp_date_created as [Date Quote Due]

cpp_quotation_days  come from a table for this example lets say quotation days = 20
If i add 20 to the cp_date_created field I get the Date Quote Due.

A new requirement is for the calculation to only add working days to get the Date Quote Due (exluding weeknd and public holidays)

Any ideas?
0
Comment
Question by:johnny_32
2 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24021153
Only way I know how is to create a holidays type table. This is the approach taken for msaccess as well.

Have a look here at some udf's to work this way
http://sql-server-performance.com/Community/forums/t/20578.aspx
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24022030

--First create a Holiday table which holds the holidays.
 

create table Holidays(datefield datetime,holiday nvarchar(100),country nvarchar(100))

insert into Holidays

select '12/25/2009', 'christmas', 'usa'

 union all

select '12/26/2009', 'Day After Christmas', 'USA'

 union all

select '05/25/2009', 'Memorial Day', 'USA'

--------------------------------------------------------------------------------------------------------

--Then create a function like this.

create function dbo.fn_GetWorkingDays 

(@Date datetime,

 @Days int) returns int as

begin

declare @table table(datefield datetime)

declare @StartDate datetime, @EndDate datetime

select @StartDate = @date,@EndDate = DATEADD(DD,@days,@date)

while @StartDate < @EndDate

begin

insert into @Table values(@StartDate)

set @StartDate = @StartDate + 1

end
 

select COUNT(*)

  from @Table

 where DATEPART(dw,datefield) not in (1,7)

   and dateadd(dd,0,datediff(dd,0,datefield)) not in (select dateadd(dd,0,datediff(dd,0,datefield)) from Holidays)

drop table Holidays

end

--------------------------------------------------------------------------------------------------------

-- And finally call this function in your SELECT query.

select dbo.fn_GetWorkingDays(cp.cp_date_created,inwp.cpp_quotation_days) as [Date Quote Due]

Open in new window

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now