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 |
|
robmburke
Starting Member
2 Posts |
 Posted - 2012-07-02 : 11:06:07
|
| I'm having trouble finding an answer to this online.I want to allow my user, in an SSRS style report to choose the field to compare against.So, there are two fields that have dates in them, and instead of making a whole separate report, I want to change the WHERE clause to reflect a different field to use as the comparison against the date range.I'm having trouble figuring out if there is a way to do this or not and how to go about it if there is.Thanks for any help and direction. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-02 : 13:00:36
|
| where (dte1 = @fld1 or @fld1 is null)and (dte2 = @fld2 or @fld2 is null)or whatever your default value is.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
robmburke
Starting Member
2 Posts |
Posted - 2012-07-02 : 13:05:24
|
| Maybe I should post an example of what I am trying to do.Your example isn't making sense to me.CASE @FieldName WHEN 'InvoiceDate' THEN WHERE a.InvoiceDate BETWEEN @StartDate AND @EndDate WHEN 'PaidDate' THEN WHERE a.PaidDate BETWEEN @StartDate AND @EndDateENDThis is the "psuedocode" of what I am trying to accomplish. I am not seeing how your code accomplishes the same thing.The only differences between the two clauses is the use of different fields to compare (a.InvoiceDate and a.PaidDate). |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-07-02 : 14:22:19
|
Maybe this?WHERE (@ColumnName = 'InvoiceDate' AND a.InvoiceDate BETWEEN @StartDate AND @EndDate) OR (@ColumnName = 'PaidDate' AND a.PaidDate BETWEEN @StartDate AND @EndDate) |
|
|
|
|
|
|
SQL WHERE clause and dynamic fields