SQL help - SELECT / WHERE / CASE?

Question

I knock my head on the table, trying to do something in SQL, I understand the logic, and I can easily complete the task in Excel, but where is it fun:

This database is considered as a WorkOrder table, and I select the following columns:

SELECT WorkOrderNumber,
WorkOrderDescription,
WorkOrderHistoryDescription,
RaisedDateTime,
FinishedDateTime

What I need, I'm trying to do, is to return all work orders created within a certain month, which is quite simple:

WHERE RaisedDateTime >= CONVERT(DATETIME, '2014-12-01' ) and <= CONVERT(DATETIME, '2014-12-31' )

I also want them to return the records that were raised before, but were closed this month, I tried the following, which seemed to work:

WHERE ( RaisedDateTime >= CONVERT(DATETIME, '2014-12-01' ) AND RaisedDateTime <= CONVERT(DATETIME, '2014-12-31' ) )
OR ( FinishedDateTime >= CONVERT(DATETIME, '2014-12-01' ) AND FinishedDateTime <= CONVERT(DATETIME, '2014-12-31' ) AND RaisedDateTime < CONVERT(DATETIME, '2014-12-01' ) )

However, when I inserted the PreventativeMaintenanceID IS NULL after the above, I found the work orders that mattered in this column, if I only have the first line of the WHERE statement, everything works fine.

/ , ? , CASE, .

Answer 1

, , , , , , :

SELECT WorkOrderNumber,
       WorkOrderDescription,
       WorkOrderHistoryDescription,
       RaisedDateTime,
       FinishedDateTime
  from tbl
 WHERE (
       (RaisedDateTime between CONVERT(DATETIME, '2014-12-01') AND
       CONVERT(DATETIME, '2014-12-31')) OR
       (FinishedDateTime between CONVERT(DATETIME, '2014-12-01') AND
       CONVERT(DATETIME, '2014-12-31') AND
       RaisedDateTime < CONVERT(DATETIME, '2014-12-01'))
       )
   and PreventativeMaintenanceID IS NULL

, , OR