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

get weekly report

I want to create a process (weekly at 6pm)  which retreive data from last Friday at 6pm to Friday 6 pm.

select *from mytable where  myorderdate = getdate()-7 ?
0
VBdotnet2005
Asked:
VBdotnet2005
1 Solution
 
Brendt HessSenior DBACommented:
Too hazardous.  You should not depend on something starting at exactly a specified instant.  Instead, use a range derived from the current date, e.g.:

SELECT *
FROM MyTable
WHERE MyOrderDate > CONVERT(varchar(8), DATEADD(DAY, -7, CURRENT_TIMESTAMP), 112) + ' 18:00' 
	AND MyOrderDate <= CONVERT(varchar(8), CURRENT_TIMESTAMP, 112) + ' 18:00'

Open in new window


Note that this is not the equivalent of a BETWEEN statement.  Using a BETWEEN clause, if an order fell at exactly 18:00 on Friday, then with a BETWEEN statement, it would be included in two weeks.  With this statement, it will only be included in one week.
0
 
Ashok KumarCommented:
- Try this.

WHERE (MyOrderDate >= DATEADD(day, -7, CURRENT_TIMESTAMP) and MyOrderDate <= now () )
0
 
OCDanCommented:
If run now it would select all orders between 24/03/2012 00:00:00 and 1/04/2012 00:00:00
Using the dateadd and datediff functions allows the query to still be SARG-able helping performance

SELECT *
FROM    mytable
WHERE  myorderdate BETWEEN DateAdd(day, datediff(day,0,getdate()-7), 0) and DateAdd(day, datediff(day,0,getdate()+1), 0)

If you wanted this to be sent out weekly automatically you could enable sp_send_dbmail

And setup a scheduled job in SQL Server Agent using the below code:
EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'ProfileName',
 @recipients = 'whomever@wherever.com',
 @query = 'SELECT * FROM    mytable  WHERE  myorderdate BETWEEN DateAdd(day, datediff(day,0,getdate()-7), 0) and DateAdd(day, datediff(day,0,getdate()+1), 0)' ,
 @subject = 'Query Output',
@attach_query_result_as_file = 1

Hope that is some help
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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