Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

fetching data from last 5 days

hi guys

i have a table Sale_Region like


sale_Date   region    customer_name

2013-05-19     UK                  Jay
2013-03-1      US                  Jack
2013-05-22     AUS                 Jill
2013-05-21     UK                  Anthony



I want to return three columns max(sale_Date) , sale_Date which is 5 working days before  max(sale_Date),
region from the table

any idea how i can do that?



thanks
0
royjayd
Asked:
royjayd
  • 3
  • 2
1 Solution
 
royjaydAuthor Commented:
any answers greatly appreciated
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..

SELECT region, customer_name, max(sale_date), DATEADD(dd, -5,  max(sale_date)) as sale_date_minus_five_days
FROM YourTable
GROUP BY region, customer_name
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
'working days'

How does your company define this?  Most companies will have a custom table where each day is a row, and columns for fiscal year/month/week/whatever, working days, etc.

Problem is, everyone has different holdiays, so what's a working day will vary wildly between companies and countries, such that a single SQL function won't cover all situations.
0
 
royjaydAuthor Commented:
jim

I think that sql is pretty good and i agree with your point about working days , our users are in US and UK.

thanks.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim

btw a TIME table is invaluable imho, for dealing with not only working days, but if your company has a really screwed up fiscal year such as crop year / federal year, and if fiscal months are 4-5-4 instead of straight calendar months.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now