Exclude Weekends

Hi,
I have the Following Query.

Set @SQL = @SQL + ' where (DateDiff("d", ORDR_DATE, ORDR_ShipDate) <= 3)'

What is does is returns the difference b/w two dates, if LESS then or equall to 3 then its ok. What I want to do is, if its saturday or Sunday then DONT Count those days, I want to return different depending upon PURE Weekdays. M - F.

Please Advise.

Thanks
LVL 23
Saqib KhanSenior DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duane LawrenceCommented:
I found a rather lively discussion on this topic on:

http://dbforums.com/t802566.html

Duane
0
Duane LawrenceCommented:
I did not write this, I found it on:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3b3c7d26.157897584%40news.microsoft.com&rnum=3&prev=/groups%3Fnum%3D50%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DWEEKDAYS%2B%2BVBSCRIPT%2BBETWEEN%26btnG%3DGoogle%2BSearch

I know it is in VB script, but it should be easy to follow the code and convert it to a stored proc.

Duane

Here's a routine I wrote in VB and converted to VBScript that gives
the number of business days between two dates:

   'VBScript does not support the VBA function IIF
   'so we can mimic it with this function
   Function IIf(i,j,k)
      If i Then IIf = j Else IIf = k
   End Function

   Date1 = CDate("4/1/01")
   Date2 = CDate("5/16/01")

    Dim days
    Dim dowdiff
    Dim weekd
    days = DateDiff("d", Date1, Date2)
    dowdiff = IIf(Weekday(Date1) < 7, Weekday(Date2), 1) _
 - Weekday(Date1)
    weekd = IIf(days = 0, 0, Int(days / 7) * 5 + _
            IIf(dowdiff >= 0 And dowdiff < 6, dowdiff, dowdiff + 5))

Response.Write weekd

Jeremy
0
Saqib KhanSenior DeveloperAuthor Commented:
Pure T-SQL Solution would be great.

Thanks
0
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.

Brendt HessSenior DBACommented:
A couple of theory questions (under the useful assumption that Murphy's law is the supreme law of computing):

What should happen if the first date, or the last date, fall on a weekend? (Don't say that it cannot happen, because people can manually edit the tables, unless you have constraints limiting what can be entered in those fields to day of week 2 to 6).

Do you want a general purpose solution (Given two dates, return # of weekdays between them) or a special case solution (given two dates, tell if at least three working days [M-F] fall between them)?
0
Saqib KhanSenior DeveloperAuthor Commented:
ok let me explain..

Lets look at this Query

DateDiff("d", ORDR_DATE, ORDR_ShipDate) <= 3)


ORDR_DATE = Friday
Ship Date   = Tuesday

Now Basicly DateDiff will return the overall Difference b/w these two dates(FRI, SAT, Sun, Mon, Tues), But I dont want this, I want "NOT" to include Sat and Sun in my Query.


in my Application I am Looking for all the orders Shipped within 3 days. 3 Business Days I want to Count. if order was placed on Thursday and was shipped on Monday then its ok for the Query if we take out weekends, but NOT ok if we just run the generic DateDiff Function.

Hope it makes sense.

Thanks
0
Brendt HessSenior DBACommented:
You can always try the query:

Set @SQL = @SQL + ' where ((Datediff(dd, ORDR_DATE, ORDR_ShipDate) - (Datediff(ww, ORDR_DATE, ORDR_Shipdate) * 2)) <= 3)'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lengreenCommented:
Hi

Declare @StartDate datetime
Declare @EndDate datetime

set @StartDate = '5-NOV-2004'
set @EndDate = '11-NOV-2004'

SELECT  1 where (DateDiff(dd, @StartDate, @EndDate)<= 3) or  (DateDiff(dd, @StartDate, @EndDate)<= 5 and datepart(weekday,@StartDate) not between 2 and 5 )

try this i'll explain it in a minute

cheers

Len
0
Duane LawrenceCommented:
bhess1 gave me an idea

select datediff( ww, getdate(), dateadd(d,20,getdate()))*5

Duane
0
lengreenCommented:
This bit catch all dosent really matter

(DateDiff(dd, @StartDate, @EndDate)<= 3)

or  (DateDiff(dd, @StartDate, @EndDate)<= 5 and datepart(weekday,@StartDate) not between 2 and 5 )

Because its 3 days we can only cross over 1 weekend

(weekday,@StartDate) will return 1 = sunday to   6 = saturday,

so providing i wasn't ordered on a monday or tueday the the difference can be <= 5

cheers

Len

0
lengreenCommented:
Declare @StartDate datetime
Declare @EndDate datetime

set @StartDate = '4-NOV-2004'
set @EndDate = '10-NOV-2004'

SELECT  1 where (DateDiff(dd, @StartDate, @EndDate)<= 3) or  (DateDiff(dd, @StartDate, @EndDate)<= 5 and (datepart(weekday,@StartDate) < 2 or datepart(weekday,@StartDate) > 3) )



this is it, i made an error

cheers

Len
0
Saqib KhanSenior DeveloperAuthor Commented:
seems like bhess solution works

Set @SQL = @SQL + ' where ((Datediff(dd, ORDR_DATE, ORDR_ShipDate) - (Datediff(ww, ORDR_DATE, ORDR_Shipdate) * 2)) <= 3)'

I did some testing and seems like its Taking out Saturday and Sunday from the Range.

Bhess can you please give me little bit of explaination about this Script, this way i will know what i am doing

Thanks
0
Brendt HessSenior DBACommented:
Datediff(dd...) of course, returns the number of days difference between two dates.

Datediff(ww...) returns the number of Weeks difference between two dates.

If two dates are in the same week, the ww compare returns 0.

Given this, you can see that:
Friday week 1 to monday week 2 is a 1 week difference.

Now, the separation for each week is two weekend days, so (2 * Week difference) gives the number of Saturdays and Sundays between date1 and date2.  
Subtracting the number of weekend days from the total number of days between date1 and date2 returns the number of weekdays between the two of them.

Friday to next monday returns
  (4 days) - (2 weekend days) = 2 days.
0
Saqib KhanSenior DeveloperAuthor Commented:
one More Question, maybe i am going Crazy.

by Default T-SQL Takes Monday as a New Week or Sunday?
0
lengreenCommented:
Sunday is the first day of the week

cheers

Len
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.