• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

SQL query to retervie data over last 6 days

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
habbotha
Asked:
habbotha
1 Solution
 
TimCotteeHead of Software ServicesCommented:
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
 
Chandan_GowdaCommented:
try the below attached code
DateTime.txt
0
 
kumar_jacCommented:
Hi,

This to return last 7 days
SELECT * FROM Table1 WHERE date_added >=dateadd(day,datediff(day,0,GetDate())- 7,0)
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
lluddenCommented:
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
 
habbothaAuthor Commented:
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
 
lluddenCommented:
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
 
habbothaAuthor Commented:
thanks guys for the help - all is working
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

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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