?
Solved

Get past 90 days and business days only

Posted on 2014-09-29
5
Medium Priority
?
241 Views
Last Modified: 2014-09-30
How can I get the past 90 days that includes business days only?
select *from mytable where shippeddate >= DATEADD(DD,-90,getdate()) and shippeddate < getdate()
0
Comment
Question by:VBdotnet2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40350312
What do you count as a business day? It varies all over the world.

Maybe you have a table with all of the exceptions, and you omit those, plus standard non-business days (which in your part of the world may be Saturday and Sunday, or may be Friday and Saturday).

XLeratorDB have got a set of CLR functions which may be useful - see this as a review: http://blog.sqlauthority.com/2013/12/10/sql-server-working-with-business-days-in-sql-server-a-different-approach/
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 668 total points
ID: 40350369
Assuming you only want Monday thru Friday you could try...

SELECT *
FROM mytable 
WHERE shippeddate >= DATEADD(DD,-90,getdate())
   AND shippeddate < getdate()
   AND DATEPART(WEEKDAY, shippeddate) IN (2, 3, 4 5, 6)

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 668 total points
ID: 40350392
If you count only weekends, then you can simply go back an additional 26 days (90 days = 13 weeks = 26 weekend days), and exclude week-end days from the result.

where shippeddate >= DATEADD(DD,-90-26,getdate()) and shippeddate < getdate() and datediff(day, 0, shippeddate) % 7 in (5, 6)
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 664 total points
ID: 40351594
or, you can create and maintain a calendar table which permits not only tracking business days in the cyclical sense but records public holidays as well. There are many methods for doing this. Even if you record even a decade of dates in a table you are only dealing with ~3653  records (depending on the leaps years).

Here's an article on the subject

& the calendar table can be used in all sorts of ways when reporting/analysing data, not just helpful for calculating working days.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40353377
Be aware that the return values from "DATEPART(WEEKDAY, " can vary based on SQL settings, whereas "datediff(day, 0, shippeddate) % 7" returns the same value for any/all SQL settings.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 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