Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

fetching data from last 5 days

Posted on 2013-05-22
5
Medium Priority
?
196 Views
Last Modified: 2013-05-22
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
Comment
Question by:royjayd
[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
  • 3
  • 2
5 Comments
 

Author Comment

by:royjayd
ID: 39187874
any answers greatly appreciated
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1660 total points
ID: 39187918
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39187928
'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
 

Author Comment

by:royjayd
ID: 39188014
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39188055
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

604 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