SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Where clause with 4 'ands' ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jun0
Starting Member

United Kingdom
32 Posts

Posted - 12/14/2012 :  09:00:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/14/2012 :  09:44:04  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/14/2012 :  09:47:29  Show Profile  Reply with Quote
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
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 12/14/2012 :  10:56:12  Show Profile  Reply with Quote
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

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/14/2012 :  11:13:23  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/14/2012 :  11:13:59  Show Profile  Reply with Quote
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
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 12/15/2012 :  11:37:15  Show Profile  Reply with Quote
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

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/15/2012 :  11:51:00  Show Profile  Reply with Quote
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'
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 12/16/2012 :  05:05:08  Show Profile  Reply with Quote
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

Edited by - jun0 on 12/16/2012 05:06:32
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/16/2012 :  07:37:08  Show Profile  Reply with Quote
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.
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 12/16/2012 :  08:21:26  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/16/2012 :  10:18:13  Show Profile  Reply with Quote
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.
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 12/16/2012 :  13:11:34  Show Profile  Reply with Quote
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>
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000