Solved

Get past 90 days and business days only

Posted on 2014-09-29
5
201 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
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 167 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:ScottPletcher
ScottPletcher earned 167 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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 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:ScottPletcher
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

947 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

20 Experts available now in Live!

Get 1:1 Help Now