February 19, 2009 at 5:22 am
Folks,
I've been racking my brains to find a solution to this. Here, Saturdays are counted as working days so maybe you can help me construct some SQL to calculate the number of days between two dates EXCLUDING Sundays. The following *almost* works but not quite...
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'
SELECT
(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)
Any help gratefully accepted.
Thanks,
February 19, 2009 at 6:20 am
What number do you want back for the dates given?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2009 at 6:32 am
The query is reducing 2 days for a week from total days. Since, Saturday is a business day for you, it should be reduced by just 1 day. i.e.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 1)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
--Ramesh
February 19, 2009 at 6:47 am
What about national holidays?
If you need to take account of these then a Calendar table where working days can be identified would be the way to go.
February 19, 2009 at 6:54 am
Thanks for all your help folks. I'm going to do what I should have done a long time ago and ask for a working days calendar to be added with a 'working_days_num' field.
Kind regards,
February 19, 2009 at 7:41 am
For future reference, a very thorough discussion of this topic can be found in the discussion of the following article. A variety of solutions, performance considerations, and other issues were covered in the melee that ensued.
http://www.sqlservercentral.com/articles/T-SQL/65423/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 24, 2009 at 6:58 pm
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2009/02/08'
SET @EndDate = '2009/02/24'
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) )
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'and DATENAME(dw, @EndDate) != 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @StartDate) != 'Sunday' and DATENAME(dw, @EndDate) = 'Sunday' THEN 0 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' and DATENAME(dw, @EndDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @StartDate) != 'Sunday' and DATENAME(dw, @EndDate) != 'Sunday' THEN 0 ELSE 0 END)
hey , try this out..
February 25, 2009 at 7:28 am
I don't see the pont in these two in the query above since they will both always evaluate to 0
CASE WHEN DATENAME(dw, @StartDate) != 'Sunday' and DATENAME(dw, @EndDate) = 'Sunday' THEN 0 ELSE 0 END
CASE WHEN DATENAME(dw, @StartDate) != 'Sunday' and DATENAME(dw, @EndDate) != 'Sunday' THEN 0 ELSE 0 END
February 25, 2009 at 8:03 am
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/03/01'
SET @EndDate = '2008/03/31'
set DATEFIRST 7
select DATEPART(dw, @StartDate) as firstday, (datediff(dd, @StartDate, @EndDate)/7)
select
case when DATEPART(dw, @StartDate) > 6 then
datediff(dd, @StartDate, @EndDate) - ((datediff(dd, @StartDate, @EndDate)/7) + 1 )
else
datediff(dd, @StartDate, @EndDate) - (datediff(dd, @StartDate, @EndDate)/7)
end
February 26, 2009 at 1:09 am
Thanks for all the posts folks. I think the definitive answer is as follows:-
select datediff(dd,start_date, getdate())-(( floor((datepart(dw,start_date)+datediff(dd,start_date ,getdate())) /7 )*1))+ case datepart(dw,getdate()) when 7 then 1 else 0 end
Seems to work fine, but of course no bank hols are included, thus strengthening the argument for a function based on a working day calendar.
Thanks again.
February 26, 2009 at 7:06 am
My one comment about your definitive answer is that it is limited in its application. You're definitely smart to look into implementing a calendar table. Calendar tables don't have to list working days. Most contain only weekend days and holidays. They can be prepopulated easily and the queries against them are fairly fast and straighforward. They are also flexible in that the code to just omit Sundays will be similar to the code to omit Saturdays, Sundays, and Holidays etc.
I posted the link to an article that started an excellent discussion of these kinds of problems earlier. The link below is to the start of the discussion, which is worth walking through for the code examples and variety of issues covered. It was definitely an education for me, anyway. Best of luck 🙂
http://www.sqlservercentral.com/Forums/Topic649517-1472-10.aspx
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 12, 2009 at 7:03 am
CREATE FUNCTION [dbo].[GetWorkingDays]
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @varDate datetime --placeholder for c
DECLARE @wdCount int
SELECT @wdCount = 0
SELECT @varDate = @StartDate
WHILE (@varDate Between @StartDate AND @EndDate)
BEGIN
IF (DATEPART(dw, @varDate) 6
May 12, 2009 at 3:13 pm
Nawar, although your function may get the desired result, using a while loop is going to run much slower than a set based solution. If you will read up a bit on calendar tables, you will find that they will run much more efficiently over the long term. Thank you for contributing! 🙂
Regards,
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 13, 2009 at 6:34 am
Here's a link I found on Jeremiah Peschka's blog[/url] that creates and populates a calendar table
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply