Where clause with 4 'ands' ? - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Where clause with 4 'ands' ?

Author

Topic

jun0
Starting Member

32 Posts

Posted - 2012-12-14 : 09:00:43

Hi,

I am which to write a query that extracts data based on the firstentry and lastentry times of data into a database, at the moment it doesn't work, it looks like this:

select * from <databasename>
where firstentry >= '2012-11-19' and first entry < '2012-11-20'
and lastentry >= '2012-11-19' and lastentry < '2012-11-20'

the query doesnt work at the moment, what would be the best way to write this statement, that works?

Thanks, I am new but learning :)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-14 : 09:44:04

It depends on what you want the query to do.

==========================================
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.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-14 : 09:47:29

Your query will return only records where the first and last entries occurred on 2012-11-19. You wamy just want to use an order statement

select * from <databasename>
where (firstentry >= '2012-11-19' and first entry < '2012-11-20')
OR ( lastentry >= '2012-11-19' and lastentry < '2012-11-20')

This will give you all records with an entry date of 2012-11-20 regardless of its last entry date and all records whose last entry was 2012-11-20 regardless of its entry date

Jim

Everyday I learn something that somebody else already knew

jun0
Starting Member

32 Posts

Posted - 2012-12-14 : 10:56:12

Thanks guys,

However, jimf, in the code you posted:


select * from <databasename>
where (firstentry >= '2012-11-19' and first entry < '2012-11-20')
OR ( lastentry >= '2012-11-19' and lastentry < '2012-11-20')

I would actually need the OR to be an AND, this is because I actually do need to return rows where the 'firstentry' and 'lastentry' are within the date range specified, not just one of the two entries.

However, I had already tried the code you posted above except with an AND in place of your OR and got an error. So I would like to know how what I am trying to do would be written correctly in SQL...?

Thanks again

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-14 : 11:13:23

Are the firstentry and lastentry on the same row in the table? i.e. you want single rows where all these conditions are met?

Then your query looks correct - try fionding a row you expect to be returned and selecting for just that row (include the pk in the query).

==========================================
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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-14 : 11:13:59

What is the data type of firstentry and lastentry columns? Assuming they are datetime, In the example below, can you indicate the rows should be returned in your query in the sample data below?

firstentry                  lastentry 
2012-11-19 11:10:01.780     2012-11-19 17:10:01.780
2012-11-19 11:10:01.780     2012-11-20 17:10:01.780
2012-11-18 11:10:01.780     2012-11-19 17:10:01.780

jun0
Starting Member

32 Posts

Posted - 2012-12-15 : 11:37:15

Hi guys, suitabeck, the row below is the only one that should be returned, as this is the only row in your example data where both firstoccurrence AND lastoccurrence are >= 19th and < than 20th:

firstentry lastentry
2012-11-19 11:10:01.780 2012-11-19 17:10:01.780

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-15 : 11:51:00

That means the query that you posted in your original post should do exactly that. Is the data type of the columns datetime? You can check using the following query:

SELECT COLUMN_NAME,
       DATA_TYPE
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  COLUMN_NAME IN ('firstentry', 'lastentry')
       AND TABLE_NAME = 'YourTableNameHere'

jun0
Starting Member

32 Posts

Posted - 2012-12-16 : 05:05:08

Yes the datatype is datetime, so you're saying that the query below should work:

select * from <tablename>
where firstentry >= '2012-11-19' and first entry < '2012-11-20'
and lastentry >= '2012-11-19' and lastentry < '2012-11-20'

This didn't seem to work when I tried it, i was thinking that there were too many 'and's there. What would happen if I needed to add another condition in the whereclause, such as:

select * from <tablename>
where customername = 'smith' and
firstentry >= '2012-11-19' and first entry < '2012-11-20'
and lastentry >= '2012-11-19' and lastentry < '2012-11-20'

I do apologise, but I have a feeling now that maybe this is the query I should have posted originally, as this really does have 4 'and's

How would this be done?

thankyou

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-16 : 07:37:08

You can have as many AND conditions as you need in the where clause. As you add each condition, it narrows the filter down to a smaller and smaller subset. In the latest example you posted, it will get you only those rows where customername is smith and the firstentry and lastentry are on 2012-11-19. If you are not getting the results you want, check if there any rows in your table that satisfy ALL of the five conditions.

jun0
Starting Member

32 Posts

Posted - 2012-12-16 : 08:21:26

ok sunitabeck, so you are saying that I can go with this:

select * from <databasename>
where firstentry >= '2012-11-19' and first entry < '2012-11-20'
and lastentry >= '2012-11-19' and lastentry < '2012-11-20'

and I don't need to do it like this:

select * from <databasename>
where (firstentry >= '2012-11-19' and first entry < '2012-11-20')
OR ( lastentry >= '2012-11-19' and lastentry < '2012-11-20')

these should both work? I can't test it just at the minute unfortunately, but I'm sure I got errors, however I'm not doubting that you are correct, there must have been something else in my query causing the problem.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-16 : 10:18:13

The two queries you posted are logically different. The code itself is pretty descriptive, you can read it like you would read an English langauge sentence.

In the first case, you are asking for only those rows that satisfy ALL four conditions. That would mean rows for which first entry is on the 19th AND last entry is on the 19th. In the sample data that I posted on 12/14/2012 : 11:13:59, only the first row would meet that condition.

In the second case, you are asking for rows that satisfy the first condition and the second condition, OR, rows that satisfy the third condition and fourth condition. That would mean rows for which the first entry is on the 19th OR last entry is on the 19th. In the sample data that I posted on 12/14/2012 : 11:13:59, all three rows would meet that condition.

Also, in both the queries you posted, you have used SELECT * FROM <databasename>. You should be inserting a TABLE NAME, not the DATABASE NAME.

jun0
Starting Member

32 Posts

Posted - 2012-12-16 : 13:11:34

Thanks, well then it is definately the first of the two queries that I need to use, however I'm sure an error, sorry I know that doesn't help much.

I will try again and post the error if I do get one.

Yes I know sorry, <databasename> should be <tablename>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources