Solved

Get past 90 days and business days only

Posted on 2014-09-29
5
229 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 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:Scott Pletcher
Scott Pletcher 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 49

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: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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 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