Solved

Get past 90 days and business days only

Posted on 2014-09-29
5
204 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: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 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: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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

832 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