|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Dynamic WHERE clauseIs it possible to create something like this: SELECT ... FROM .... WHERE TransMonth (CASE WHEN @DateType = 'UserEntered' THEN = @UserEnteredMonth WHEN @DateType = 'YearToDate' THEN <= MONTH(GETDATE() ) END) Where @UserEnteredMonth and TransMonth are defined as INT. Kind Regards Ricky Is this fine?
SELECT ... FROM ... WHERE TransMonth <= (CASE WHEN @DateType = 'UserEntered' THEN @UserEnteredMonth + 1 WHEN @DateType = 'YearToDate' THEN MONTH(GETDATE() ) END) Show quote "ricky" wrote: > Hi > > Is it possible to create something like this: > > SELECT ... > FROM .... > > WHERE > > TransMonth > (CASE > WHEN @DateType = 'UserEntered' THEN > = @UserEnteredMonth > WHEN @DateType = 'YearToDate' THEN > <= MONTH(GETDATE() ) > END) > > Where @UserEnteredMonth and TransMonth are defined as INT. > > Kind Regards > > Ricky > > > > > > > I think this is what you want:
SELECT ... FROM ... WHERE (@DateType = 'UserEntered' AND TransMonth = @UserEnteredMonth) OR (@DateType='YearToDate' AND TransMonth <= MONTH(GETDATE() ) Show quote "ricky" wrote: > Hi > > Is it possible to create something like this: > > SELECT ... > FROM .... > > WHERE > > TransMonth > (CASE > WHEN @DateType = 'UserEntered' THEN > = @UserEnteredMonth > WHEN @DateType = 'YearToDate' THEN > <= MONTH(GETDATE() ) > END) > > Where @UserEnteredMonth and TransMonth are defined as INT. > > Kind Regards > > Ricky > > > > > > > Hi Kostas
Thanks for the reply, apologies for sounding doubtful, but will this cater for different parameter selection? Kind regards Ricky Show quote "Kostas" <Kos***@discussions.microsoft.com> wrote in message news:2C44A21A-27B0-41E4-B6C4-D240732578D8@microsoft.com... > I think this is what you want: > SELECT ... > FROM ... > WHERE (@DateType = 'UserEntered' AND TransMonth = @UserEnteredMonth) > OR (@DateType='YearToDate' AND TransMonth <= MONTH(GETDATE() ) > > "ricky" wrote: > > > Hi > > > > Is it possible to create something like this: > > > > SELECT ... > > FROM .... > > > > WHERE > > > > TransMonth > > (CASE > > WHEN @DateType = 'UserEntered' THEN > > = @UserEnteredMonth > > WHEN @DateType = 'YearToDate' THEN > > <= MONTH(GETDATE() ) > > END) > > > > Where @UserEnteredMonth and TransMonth are defined as INT. > > > > Kind Regards > > > > Ricky > > > > > > > > > > > > > > As I said, I think it does what you want. But why don't you test it? You
shouldn't take my word for it... Show quote "ricky" wrote: > Hi Kostas > > Thanks for the reply, apologies for sounding doubtful, but will this cater > for different parameter selection? > > Kind regards > > Ricky > > > > "Kostas" <Kos***@discussions.microsoft.com> wrote in message > news:2C44A21A-27B0-41E4-B6C4-D240732578D8@microsoft.com... > > I think this is what you want: > > SELECT ... > > FROM ... > > WHERE (@DateType = 'UserEntered' AND TransMonth = @UserEnteredMonth) > > OR (@DateType='YearToDate' AND TransMonth <= MONTH(GETDATE() ) > > > > "ricky" wrote: > > > > > Hi > > > > > > Is it possible to create something like this: > > > > > > SELECT ... > > > FROM .... > > > > > > WHERE > > > > > > TransMonth > > > (CASE > > > WHEN @DateType = 'UserEntered' THEN > > > = @UserEnteredMonth > > > WHEN @DateType = 'YearToDate' THEN > > > <= MONTH(GETDATE() ) > > > END) > > > > > > Where @UserEnteredMonth and TransMonth are defined as INT. > > > > > > Kind Regards > > > > > > Ricky > > > > > > > > > > > > > > > > > > > > > > > > Excellent Kostas, it works.
Thank you for your help. Kind Regards Ricky Show quote "Kostas" <Kos***@discussions.microsoft.com> wrote in message news:1AB1E760-B984-4A0A-9420-6F18E374634D@microsoft.com... > As I said, I think it does what you want. But why don't you test it? You > shouldn't take my word for it... > > "ricky" wrote: > > > Hi Kostas > > > > Thanks for the reply, apologies for sounding doubtful, but will this cater > > for different parameter selection? > > > > Kind regards > > > > Ricky > > > > > > > > "Kostas" <Kos***@discussions.microsoft.com> wrote in message > > news:2C44A21A-27B0-41E4-B6C4-D240732578D8@microsoft.com... > > > I think this is what you want: > > > SELECT ... > > > FROM ... > > > WHERE (@DateType = 'UserEntered' AND TransMonth = @UserEnteredMonth) > > > OR (@DateType='YearToDate' AND TransMonth <= MONTH(GETDATE() ) > > > > > > "ricky" wrote: > > > > > > > Hi > > > > > > > > Is it possible to create something like this: > > > > > > > > SELECT ... > > > > FROM .... > > > > > > > > WHERE > > > > > > > > TransMonth > > > > (CASE > > > > WHEN @DateType = 'UserEntered' THEN > > > > = @UserEnteredMonth > > > > WHEN @DateType = 'YearToDate' THEN > > > > <= MONTH(GETDATE() ) > > > > END) > > > > > > > > Where @UserEnteredMonth and TransMonth are defined as INT. > > > > > > > > Kind Regards > > > > > > > > Ricky > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ricky wrote:
Show quote > Hi CASE is an expression and no a control flow statement. It returns a> > Is it possible to create something like this: > > SELECT ... > FROM .... > > WHERE > > TransMonth > (CASE > WHEN @DateType = 'UserEntered' THEN > = @UserEnteredMonth > WHEN @DateType = 'YearToDate' THEN > <= MONTH(GETDATE() ) > END) > > Where @UserEnteredMonth and TransMonth are defined as INT. value. You are still doing procedural programming and and have not yet gotten the concept of declarative programming. You also need to start using CURRENT_TIMESTAMP. Since you did not post any DDL and I assume this is just an example, you might also consider learning how to do temporal models in SQL with durations, ranges and calendar tables instead of integer math. For example, you ask the user for a date range and if they fail to provide one or both of the range limits, then use a default value. This avoids the trouble of writing code with flags instead of predicates. WHERE trans_date BETWEEN COALESCE(@my_start_date, '2006-01-01') AND COALESCE( @my_end_date, CURRENT_TIMESTAMP) > WHERE trans_date BETWEEN COALESCE(@my_start_date, '2006-01-01') For god sake, when are you going to look up and understand how SQL Server > AND COALESCE( @my_end_date, CURRENT_TIMESTAMP) optimiser actually works? Its fine writing tidy code in a book or class room but this type of thing is just stupid in reality. The above will force a table scan (clustered index scan), for 'table scan' look up books online. Seriously, you need to get back to basics yourself. The best way to do this is using dynamic SQL, if the user does not specify a date then don't use it in the WHERE clause - simple! -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1154783918.932790.219010@m79g2000cwm.googlegroups.com... > > ricky wrote: >> Hi >> >> Is it possible to create something like this: >> >> SELECT ... >> FROM .... >> >> WHERE >> >> TransMonth >> (CASE >> WHEN @DateType = 'UserEntered' THEN >> = @UserEnteredMonth >> WHEN @DateType = 'YearToDate' THEN >> <= MONTH(GETDATE() ) >> END) >> >> Where @UserEnteredMonth and TransMonth are defined as INT. > > CASE is an expression and no a control flow statement. It returns a > value. > > You are still doing procedural programming and and have not yet gotten > the concept of declarative programming. You also need to start using > CURRENT_TIMESTAMP. Since you did not post any DDL and I assume this is > just an example, you might also consider learning how to do temporal > models in SQL with durations, ranges and calendar tables instead of > integer math. > > For example, you ask the user for a date range and if they fail to > provide one or both of the range limits, then use a default value. > This avoids the trouble of writing code with flags instead of > predicates. > > WHERE trans_date BETWEEN COALESCE(@my_start_date, '2006-01-01') > AND COALESCE( @my_end_date, CURRENT_TIMESTAMP) >  |
|||||||||||||||||||||||
Other interesting topics