Solved

SQL query to retervie data over last 6 days

Posted on 2008-06-23
7
884 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

717 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