where clause - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

where clause

Author

Topic

JARA
Starting Member

6 Posts

Posted - 2006-01-13 : 14:42:35

I am new to SQL and I need to replace the X and the Y in this where clause:

where received between X and Y

Thanks in advanced for any help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 15:00:36

Hi Jara,

I'm not clear on what you mean. Do you mean you want to use parameters (variables) so that the statement will work with whatever the user passes? Or do you mean you want to perform a string replace function where you replace those to characters with other characters?

Be One with the Optimizer
TG

JARA
Starting Member

6 Posts

Posted - 2006-01-13 : 15:37:40

yes I want to use parameters to obtain information.

Thanks lots!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 15:47:02

Here is a quick example of some sql code. You should spend a lot of time reading through sql server's Books Online. You can start by looking up the key words and functions I used below.


--this assumes the column [received] is of datatype datetime
declare @x datetime
        ,@y datetime

select @x = dateadd(month, -1, getdate())
       ,@y = getdate()

select <columnList>
from   <table> t
where t.[received] between @x and @y

Be One with the Optimizer
TG

JARA
Starting Member

6 Posts

Posted - 2006-01-13 : 15:54:19

This is how I have right now, but I am trying to replace the x and the y with the right format.

SELECT MarketID, count(distinct Version.quoteid) Quotes, sum(CASE WHEN Version is null then 1 else 0 end) Binders FROM Quote (NOLOCK), Version (NOLOCK) where Quote.QuoteID=Version.QuoteID
where received between and y
group by marketid

thank you again!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 16:21:27

what values do you want to replace X and Y with?

if you mean you want to know how to format a date string, try '2006-1-13'

Be One with the Optimizer
TG

JARA
Starting Member

6 Posts

Posted - 2006-01-13 : 16:34:54

Yeah I've tried that with no luck.
Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 16:37:52

What does "no luck" mean? no rows? errors? wrong rows?

Post the actual statement you are trying and the sql error it generates.

Be One with the Optimizer
TG

JARA
Starting Member

6 Posts

Posted - 2006-01-13 : 17:07:52

This is the error:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'where'.

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 17:38:54

>>Post the actual statement you are trying and the sql error it generates.

In the statement above you have WHERE twice:


SELECT	MarketID
	,count(distinct Version.quoteid) Quotes
	,sum(CASE WHEN Version is null then 1 else 0 end) Binders 
FROM	Quote (NOLOCK)
	,Version (NOLOCK) 
where Quote.QuoteID=Version.QuoteID
where and received between '2006-1-1' and '2006-12-31'
group by marketid

Be One with the Optimizer
TG

JARA
Starting Member

6 Posts

Posted - 2006-01-17 : 08:31:50

Thanks a lot, it worked!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources