Nested AND/OR in SQL WHERE

Question

I am trying to use use a few conditions to select records based on start and end dates. Basically I want anything without an end date or that starts in the given period or ends in the same given period. Any help or direction on how this might be accomplished would be very appreciated. I'm including the WHERE part of the query below and the parameter binding but can include more if needed.

$periodsCxn->bindParam(':PositionID',$PositionID);
$periodsCxn->bindParam(':Start',$firstPayPeriod);
$periodsCxn->bindParam(':End',$lastPayPeriod);
$periodsCxn->bindParam(':Start2',$firstPayPeriod);
$periodsCxn->bindParam(':End2',$lastPayPeriod);

WHERE
        POSITION_PERIOD.PositionID = :PositionID AND
        (
            (
                POSITION_PERIOD.EndDate = '1900-01-01'
                OR
                POSITION_PERIOD.EndDate IS NULL
            )
            OR
            (
                POSITION_PERIOD.StartDate BETWEEN :Start AND :End
                OR
                POSITION_PERIOD.EndDate BETWEEN :Start2 AND :End2
            )
        )

What do you exactly want to achieve ? Why 1900 for end date ? P.S: no need to use different variables like Start2 and End2, just use the original ones every time you need them. — Andrea Ligios, Oct 15 '12 at 13:14
looks like it's just 1 or 2 or 3 or 4 from here (no extra mucking around with brackets, apart from separating the and clause). What's not working? — mcalex, Oct 15 '12 at 13:16
@Swift Yes you do have to use different placeholder names. The fact that you think that you don't tells me that you are not [disabling emulated prepares](http://stackoverflow.com/a/12202218/889949) - PDO emulated prepares will allow you to do this, true prepares do not. — DaveRandom, Oct 15 '12 at 13:22
It seemed that when the web form had an empty EndDate the DB is given the 1900-01-01 value. I had used the same variables before but I got an error and found that others had the same error and using different parameters resolved that. You're probably right mcalex, I've simplified it and it worked. — Noel, Oct 15 '12 at 13:36
Thank you for the quick reality check. I'm not sure what I had been thinking! — Noel, Oct 15 '12 at 13:37

score 0 · Accepted Answer · answered Oct 15 '12 at 13:46

0

It surprises me that you would want any record without an end date, but be comparing start dates in other cases.

Typically, with such data, you want to know if a given period overlaps another period, even when that period might have no end date. The logic for the overlap is:

where POSITION_PERIOD.PositionID = :PositionID AND
      POSITION_PERIOD.StartDate <= :End and
      (POSITION_PERIOD.EndDate >= :Start or
       POSITION_PERIOD.EndDate = '1900-01-01' or
       POSITION_PERIOD.EndDate IS NULL
      )

This assumes that the variables have valid dates.

answered Oct 15 '12 at 13:46

Gordon Linoff

1,242,037
58
646
786

I'm looking for information from any position period that took place within the fiscal year (Start - End). I need to know if the period started during the FY, ended during the FY or started during a previous FY but has still not ended yet. It looks like yours has better logic than mine. Thank you very much for posting your solution. – Noel Oct 15 '12 at 14:00

Nested AND/OR in SQL WHERE

1 Answers1