Solved

SQL query to retervie data over last 6 days

Posted on 2008-06-23
7
878 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
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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 500 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

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

22 Experts available now in Live!

Get 1:1 Help Now