Solved

Get past 90 days and business days only

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

21 Experts available now in Live!

Get 1:1 Help Now