Solved

fetching data from last 5 days

Posted on 2013-05-22
5
162 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
  • 3
  • 2
5 Comments
 

Author Comment

by:royjayd
Comment Utility
any answers greatly appreciated
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 415 total points
Comment Utility
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 65

Expert Comment

by:Jim Horn
Comment Utility
'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
Comment Utility
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 65

Expert Comment

by:Jim Horn
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

10 Experts available now in Live!

Get 1:1 Help Now