I need to filter data based on two date ranges as follows. I need to know where to put the parenthesis.

  
WHERE OPENDATE GE STARTDATE AND OPENDATE LE ENDDATE OR
CLDDATE GE STARTDATE AND CLDDATE LE ENDDATE 

I also need to do the same thing in a define

DEFINE FILE
QTR/I1   = IF OPENDATE GE Q1_BEG AND OPENDATE LE Q1_END OR CLDDATE GE Q1_BEG AND CLDDATE LE Q1_END THEN 1 ELSE
           IF OPENDATE GE Q2_BEG AND OPENDATE LE Q2_END OR CLDDATE GE Q2_BEG AND CLDDATE LE Q2_END THEN 2 ELSE
           IF OPENDATE GE Q3_BEG AND OPENDATE LE Q3_END OR CLDDATE GE Q3_BEG AND CLDDATE LE Q3_END THEN 3 ELSE
           IF OPENDATE GE Q4_BEG AND OPENDATE LE Q4_END OR CLDDATE GE Q4_BEG AND CLDDATE LE Q4_END THEN 4 ELSE 0; 

Thanks.

This message has been edited. Last edited by: Michele Brooks,

WHERE (OPENDATE GE STARTDATE AND OPENDATE LE ENDDATE) OR
      (CLDDATE GE STARTDATE AND CLDDATE LE ENDDATE)

QTR/I1 =
     IF (OPENDATE GE Q1_BEG AND OPENDATE LE Q1_END) OR (CLDDATE GE Q1_BEG AND CLDDATE LE Q1_END) THEN 1
ELSE IF (OPENDATE GE Q2_BEG AND OPENDATE LE Q2_END) OR (CLDDATE GE Q2_BEG AND CLDDATE LE Q2_END) THEN 2
ELSE IF (OPENDATE GE Q3_BEG AND OPENDATE LE Q3_END) OR (CLDDATE GE Q3_BEG AND CLDDATE LE Q3_END) THEN 3
ELSE IF (OPENDATE GE Q4_BEG AND OPENDATE LE Q4_END) OR (CLDDATE GE Q4_BEG AND CLDDATE LE Q4_END) THEN 4
ELSE 0; 

Thanks again. I thought that's what I tried; perhaps not. I'll this.

In my experience as a programmer, AND logic usually is evaluated first, then OR logic. So I'm curious as to whether it would still work without parentheses.

That being said, it's much easier to read and understand with parentheses, as the example Francis gave demonstrates.

I would always suggest using parentheses, for readability and making sure that the order of evaluation is what you specify.

Thanks all. Francis' example worked.

It's closed, but I thought to add a comment:

I agree with Waz - always use brackets, but once in a while the ORs and ANDs get more complicated than my tiny brain can accommodate, specially when several variables have to be in place to get the correct result.

In that case I split up the logic and assign a numeric flag to each individual result. Checking the value of the sum of the flags allows me to produce the result desired.

George, I think what you are suggesting is setting flags in a DEFINE section and testing the database fields there. Then in the WHERE section test just those flags. That's a great idea.

Correct!