Multiple 'Between' Range Test Queries

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 2005 Forums

Other SQL Server Topics (2005)

Multiple 'Between' Range Test Queries

Author

Topic

rugvedmandrekar
Starting Member

2 Posts

Posted - 2012-12-02 : 06:15:10

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-02 : 07:06:26

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/

rugvedmandrekar
Starting Member

2 Posts

Posted - 2012-12-04 : 05:24:44

Thank you very much for your kind advice visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-06 : 01:34:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources