Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stylishjm
Starting Member
17 Posts |
 Posted - 2013-06-05 : 07:55:27
|
Hello,I'm attempting to edit a Crystal Report so that it is automated rather than requiring a user to enter parameters.The report generates data between a set of dates defined by the user, however I want this changed to just display data from the past 7 days.I'm attempting to edit the sql query and I seem to be stuck...from qsruser.speedofservice soswhere sos.stationtype = 'Expediter'and sos.timestamp >= {?startdate}and sos.timestamp <= {?enddate}{?startdate} and {?enddate} are the parameters which are defined by the users which is then converted to dates such as "2013-05-06"Rather than static dates/parameters, I want to replace {?startdate} with the date 7 days agoWith {?enddate} I want to replace with the current date.Is anyone able to assist |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-05 : 08:18:59
|
| [code]from qsruser.speedofservice soswhere sos.stationtype = 'Expediter'and sos.timestamp >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-7,0)and sos.timestamp <= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0);[/code]This should be close to what you are looking for, but there is something you should be aware of and that is the time portion of the timestamp column. If timestamp column had a time portion as well, for example, something like '2013-06-05 08:17:27.350', then that would not pick up rows with timestamp after midnight tonight. If you want to include that, change DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) to GETDATE() (If you want upto now). Similar comments apply for the >= part as well. |
 |
|
|
stylishjm
Starting Member
17 Posts |
Posted - 2013-06-05 : 09:25:08
|
| Excellent, thank you very much! I'll give that a go |
|
|
|
stylishjm
Starting Member
17 Posts |
Posted - 2013-06-05 : 11:02:20
|
| Works perfectly, again, thank you very much! |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-05 : 11:33:13
|
| You are very welcome - glad to help. Repeating myself, but check the boundary conditions to make sure you are getting what you need. |
|
|
|
|
|
|
|
|
How to select current date, and a date 7 days ago?