how to do an AND-OR date range? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2005)

how to do an AND-OR date range?

Author

Topic

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2009-03-03 : 14:02:24

Hello, I would like to have the user enter input when:

1- the user enters a data range
2- the user enters a cardcode
3- the user enter both

This is my where clause and what I thought should work, but it doesn't.

WHERE
(T0.CardCode LIKE (CASE WHEN '[%1]' = '' THEN '%' ELSE '[%1]' END)
AND
(T1.FactoryDate BETWEEN (CASE WHEN '[%2]' = '' THEN '%' ELSE '[%2]' END) AND (CASE WHEN '[%3]' = '' THEN '%' ELSE '[%3]' END)))

OR

(T0.CardCode LIKE (CASE WHEN '[%1]' = '' THEN '%' ELSE '[%1]' END)
OR
(T1.FactoryDate BETWEEN (CASE WHEN '[%2]' = '' THEN '%' ELSE '[%2]' END) AND (CASE WHEN '[%3]' = '' THEN '%' ELSE '[%3]' END)))

Any help is appreciated, thanks.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-03-03 : 16:08:15

Are you using dynamic -sql?

Jack Vamvas
--------------------
http://www.ITjobfeed.com

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2009-03-04 : 10:18:09

it's T-SQL in SAP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 10:20:57

are you looking for wildcard matches? if not why are you using LIKE?

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2009-03-04 : 10:42:43

good point visakh. I substituted the LIKE for an = and got the same results.

The OR works, but not the AND.

I need both to work..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 10:46:31

you need to wrap the AND condition sperately in a pair of braces ()

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2009-03-04 : 11:01:26

^it is already like that, is there something you see that I did wrong?

** I noticed that this query only doesn't work when I have a DATE RANGE / BETWEEN CONDITION **

(when i use inputs with LIKE or = .... the AND/OR function of this query works fine, but i need a date range)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 11:09:29

use >= and < instead of between and try

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2009-03-04 : 12:59:03

Thanks, but I tried this and still doesn't do both the AND & the OR..

:(

WHERE
((T0.CardCode = (CASE WHEN '[%1]' = '' THEN '%' ELSE '[%1]' END)
AND
(T1.U_XFactoryDate >= (CASE WHEN '[%2]' = '' THEN '%' ELSE '[%2]' END) and T1.U_XFactoryDate <= (CASE WHEN '[%3]' = '' THEN '%' ELSE '[%3]' END)))

OR

(T0.CardCode = (CASE WHEN '[%1]' = '' THEN '%' ELSE '[%1]' END)
OR
(T1.U_XFactoryDate >= (CASE WHEN '[%2]' = '' THEN '%' ELSE '[%2]' END) and T1.U_XFactoryDate <= (CASE WHEN '[%3]' = '' THEN '%' ELSE '[%3]' END))))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 13:08:19

i told to use < not <=

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2009-03-04 : 13:19:47

^ It's doing the same thing.

WHERE
((T0.CardCode = (CASE WHEN '[%1]' = '' THEN '%' ELSE '[%1]' END)
AND
(T1.U_XFactoryDate >= (CASE WHEN '[%2]' = '' THEN '%' ELSE '[%2]' END) and T1.U_XFactoryDate < (CASE WHEN '[%3]' = '' THEN '%' ELSE '[%3]' END)))

OR

(T0.CardCode = (CASE WHEN '[%1]' = '' THEN '%' ELSE '[%1]' END)
OR
(T1.U_XFactoryDate >= (CASE WHEN '[%2]' = '' THEN '%' ELSE '[%2]' END) and T1.U_XFactoryDate < (CASE WHEN '[%3]' = '' THEN '%' ELSE '[%3]' END))))

^When I have OR(in bold), then It's not performing the AND

when I change OR(in bold) into AND, then It's not perfoming the OR

I need both to work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 13:23:23

i think you need only this

WHERE
T0.CardCode = (CASE WHEN '[%1]' = '' THEN '%' ELSE '[%1]' END)
OR
(T1.U_XFactoryDate >= (CASE WHEN '[%2]' = '' THEN '%' ELSE '[%2]' END)
and T1.U_XFactoryDate < (CASE WHEN '[%3]' = '' THEN '%' ELSE '[%3]' END))

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2009-03-04 : 13:32:26

thanks, but that is only performing the OR

it will execute and work when I enter 1 input, but when I enter all 3 inputs to the query the data is returned scattered

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources