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 |
|
poolek3888
Starting Member
1 Post |
 Posted - 2007-07-17 : 16:50:13
|
| Hello all. Thanks in advance for the help. My problem seemed like a simple one, but has turned into a big head ache.I am basically selecting data in a certain time period that I allow the user to specify with the use of parameters. Now, in order to select that data, I ask for the user to input an ending date and ask how far back they would like to go. In my query, after SELECT, FROM, and WHERE, my query reads:AND b.audit_timestamp between to_date(:my_date,'MM-DD-YYYY') - INTERVAL '7' DAY and to_date(:my_date,'MM-DD-YYYY')You can see here that this will only go back 7 days. So I need a way to change "'7' DAY" into "'1' DAY" and "'1' MONTH." I figured I could make a new parameter for this and sub in the code I need, but it seems that wont work. I also checked out case and if statements, but they seem to only apply after SELECT and before FROM.Do you have any advise for how I can make a WHERE statement be based on a variable?Thanks all!-Kyle |
|
|
tjwent69
Starting Member
30 Posts |
Posted - 2007-07-23 : 16:31:42
|
| What are your report parameters? What exactly are the users entering? I have Accounts receivable reports that do things automatically and some that a user enters days and others that have start and end dates. I don't really know what you are trying to do. But I hope this will help a little.I don't use the between for my dates because the hh:mm:ss will cause unpredictable results. I do dates like this:where order.date >= @StartDate and order.date < dateadd(dd,1,@EndDate)in this example the user is prompted to enter a start and an end date. If I wanted the user to enter an ending date and then go back 14 days I would then have @StartDate, @EndDate, @Howmanydays.I would prompt the user for @EndDate and @Howmanydays then calculate the @Startdate.like this... set @StartDate = dateadd(dd,-@Howmanydays,@EndDate)my dateadd function subtracts the howmanydays from the user entered end date. The where clause is the same as it was in the first example. you can also skip the whole @StartDate variable by doing the Where clause like this... where order.date >= dateadd(dd, -@Howmanydays,@EndDate and order.date < dateadd(dd,1,@EndDate)dateadd and datediff functions are extremely useful for manupulating dates in query's. mastering those will make retrieving records based upon dates much easier. |
 |
|
|
|
|
|
|
|
Needing help with WHERE in reporting