Search code examples
sqlsql-serverparentheses

SQL Server - Order of Operations difficulty

Trying to figure out how to order this properly to get the expected result. What I'm looking for is A to be true, and any of B, C, or D to be true also. Starting_Date and Ending_Date are defined earlier in the code.

  1. If I use A && ((B)||(C)||(D)) as it's written below, I get no results returned.
  2. If I use A && (B || C || D), which I think is logically identical to the above, also returns no results.
  3. If I use A && B || C || D, which I think groups A and B together, I get several thousand results
  4. If I put parentheses around the Between statements, I get an error (invalid syntax)
Declare @Starting_Date  date = '2019-01-01'
Declare @Ending_Date    date = '2019-01-31'

Select x
From y   
Where   REPORTING_UNIT = '36B11' --A
                        AND ((Closing_Date          BETWEEN @Starting_Date AND @Ending_Date) --B Closing date is between report date range (eg: episode already open before period)
                        OR  (Opening_Date           BETWEEN @Starting_Date AND @Ending_Date) --C Opening date is between report date range (eg: episode was opened at some point during the period)
                        OR  (Reverse_Opening_Date   BETWEEN @Starting_Date AND @Ending_Date)) --D Reverse opening date is between report date range (eg: episode was closed at some point, then reopened during the period)

Expected results:

Year of 1858 is the database's way of say "it isn't closed". So in this example, all of these would be included except for line #14, which was closed prior to our defined ending date. Lines 5 & 6 would also be included because the episode was open during the period we are evaluating.

enter image description here

Edit: Added starting and ending date coded in and some expected results.

Solution

  • Just keep REPORTING_UNIT = '36B11' AND Closing_Date BETWEEN @Starting_Date AND @Ending_Date conditions in the WHERE clause and see the results that you get and then add the OR conditions one by one and you may get an idea of why this is happening. Also, after a cursory glance at your data above, logically only row 5 should be included. BETWEEN clause will just check the date range and row 5 satisfies that.