Solved

get weekly report

Posted on 2012-03-30
3
152 Views
Last Modified: 2012-04-15
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
Comment
Question by:VBdotnet2005
3 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 37789614
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
 
LVL 6

Expert Comment

by:Ashok Kumar
ID: 37789978
- Try this.

WHERE (MyOrderDate >= DATEADD(day, -7, CURRENT_TIMESTAMP) and MyOrderDate <= now () )
0
 
LVL 9

Accepted Solution

by:
OCDan earned 500 total points
ID: 37790814
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

821 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