[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL query to retervie data over last 6 days

Posted on 2008-06-23
7
Medium Priority
?
887 Views
Last Modified: 2011-10-19
Hi there,
I have been requested to run an automated query that runs once a week, for example on a Monday.  The query needs to extract the data for the last 7 days.  For example the date range for the first monday would of been 1 jan - 7 jan, the next time it runs the date range needs to be from 8 Jan - 14 Jan etc.  The date select needs to be done without using the parameter query.  The info is then displayed on a TV screen for the sales people to view.
Thanks you for your assistance
Heather
0
Comment
Question by:habbotha
[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
7 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 21845498
Hello habbotha,

Select * From MyTable Where MyDateField >= DateAdd(day,-7,convert(varchar(10),getdate(),120)) And MyDateField < convert(varchar(10),getdate(),120)

This will give you the 7 preceeding days, not including any data from today.

Regards,

TimCottee
0
 
LVL 7

Expert Comment

by:Chandan_Gowda
ID: 21845499
try the below attached code
DateTime.txt
0
 
LVL 5

Expert Comment

by:kumar_jac
ID: 21845510
Hi,

This to return last 7 days
SELECT * FROM Table1 WHERE date_added >=dateadd(day,datediff(day,0,GetDate())- 7,0)
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 18

Expert Comment

by:lludden
ID: 21845528
SELECT * FROM mytable WHERE RecordDate BETWEEN DATEADD(d,-7,getdate()) AND getdate()

This will return all records from exactly 7 days prior to the current date/time.  If the date is just a date value and you want the current day and the 6 preceding days then use.

SELECT * FROM mytable WHERE RecordDate BETWEEN CONVERT(varchar(10),dateadd(d,-6,getdate()),110) AND CONVERT(varchar(10),getdate(),110)
0
 

Author Comment

by:habbotha
ID: 21847358
Hi All:
Thanks for the info.  I wonder if you could assist me a bit futher with this same query.  Once i have run the query i need to count the number of entries per day.  the reason for this is that the info is going to be displayed in a graph.  I have tried using the group by function and i am nt having any luck there.
Thanks Heather
0
 
LVL 18

Accepted Solution

by:
lludden earned 2000 total points
ID: 21847989
Assuming all your dates are just dates and no time values.

SELECT RecordDate, COUNT(*) FROM mytable WHERE RecordDate BETWEEN DATEADD(d,-7,getdate()) AND getdate() GROUP BY RecordDate
0
 

Author Comment

by:habbotha
ID: 21882010
thanks guys for the help - all is working
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

650 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