• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1698
  • Last Modified:

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

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
johnny_32
Asked:
johnny_32
1 Solution
 
rockiroadsCommented:
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
 
SharathData EngineerCommented:

--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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now