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 2005 Forums
 Other SQL Server Topics (2005)
 Multiple 'Between' Range Test Queries		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rugvedmandrekar
Starting Member

United Kingdom
2 Posts
Posted - 12/02/2012 :  06:15:10  Show Profile  Reply with Quote
Hello everyone,

This is not my first experience with SQL but I have completely lost touch with SQL statements from the past 3 years. I needed to brush up my knowledge on SQL once again in order to get my basics right. Hence, this question may look silly.

This question is regarding the 'Between' range tests. Can I combine two simple range test queries into a single query? For example, I have a table, EmployeePayHistory(EmployeeId, RateChangeDate, Rate, Frequency). I want to get all the rows from from this table whose 'RateChangeDate' comes between two different ranges - 01/01/1996 & 31/12/1997 and 01/01/2000 & 31/12/2003 (Date format: dd/mm/yyyy) taking the help of the 'Between' range test.

If I were to achieve the above results I could fire the following two queries simultaneously

1) select *
from employeepayhistory
where ratechangedate between '01-jan-1996' and '31-dec-1997'

2) select *
from employeepayhistory
where (ratechangedate between '01-jan-2000' and '31-dec-2003')

However, could I combine the above two queries into a single query of something like this:-

select *
from employeepayhistory
where ratechangeddate between (select * from employeepayhistory where ratechangeddate between '01-jan-1996' and '31-dec-1997') and (select * from employeepayhistory where ratechangeddate between '01-jan-2000' and '31-dec-2003') /*** Incorrect syntax***/

? ? ? ? ?

Replies to this question will be highly appreciated. Thanking in advance.
Edited by - rugvedmandrekar on 12/04/2012 05:21:43

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 12/02/2012 :  07:06:26  Show Profile  Reply with Quote
you should be using OR

select *
from humanresources.employeepayhistory
where ratechangedate between '01-jan-1996' and '31-dec-1997'
OR ratechangedate between '01-jan-2000' and '31-dec-2003'


also always try to pass date values in iso format
see

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

rugvedmandrekar
Starting Member

United Kingdom
2 Posts
Posted - 12/04/2012 :  05:24:44  Show Profile  Reply with Quote
Thank you very much for your kind advice visakh16.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 12/06/2012 :  01:34:39  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.05 seconds. Powered By: Snitz Forums 2000