WHERE clause is not filtering all specifications

I’m trying to make a query with different specifications in WHERE clause and some of them are being ignored.

SELECT DISTINCT f.NameLawyer AS 'Comisiona', 
f.Bill As 'Factura', 
f.Amount AS 'Monto_Comisionado', 
f.IdTicket
FROM Bills f 
INNER JOIN Cases i on f.CaseName = i.CaseName
INNER JOIN Payments_received c ON f.IdPRC = c.IdPRC
INNER JOIN Clients cl ON f.IdClient = cl.IdClient
LEFT JOIN type_of_reclaim tr ON tr.id_claim = i.type_of_claim 
WHERE 
/*Search specifications being ignored Client/NameLawyer/IdTicket*/
cl.IdClient =  3 
AND f.NameLawyer = 'FooName'
AND f.IdTicket is null 
/* */
AND month(f.date_payed) = month(CAST('2020-09-14' AS DATETIME)) 
/* */
AND YEAR(f.date_payed) = year(CAST('2020-09-14' AS DATETIME)) 
/**/
OR month(f.date_payed) IS NULL
/**/
AND f.Date_Bill <= DATEFROMPARTS(YEAR(DATEADD(MM, -2, CAST('2020-09-14' AS DATE))), MONTH(DATEADD(MM, -2, CAST('2020-09-14' AS DATE))), DAY(EOMONTH((DATEADD(MM, -2, CAST('2020-09-14' AS DATE)))))) 
AND
/*BETWEEN*/
 c.Date_Payment_received BETWEEN 
 DATEFROMPARTS(YEAR(DATEADD(MM, -2, CAST('2020-09-14' AS DATE))), MONTH(DATEADD(MM, -2, CAST('2020-09-14' AS DATE))), DAY(DATEADD(DAY, 1, EOMONTH(DATEADD(MM, -3, CAST('2020-09-14' AS DATE)))))) 
AND DATEFROMPARTS(YEAR(CAST('2020-09-14' AS DATE)), MONTH(CAST('2020-09-14' AS DATE)), DAY(DATEADD(DAY, 10, EOMONTH(CAST('2020-09-14' AS DATE))))) 
/*END BETWEEN*/

I guess there’s some clause that I’m losing in front of my eyes.

Answer

Wrap this part in parenthesis

AND YEAR(f.date_payed) = year(CAST('2020-09-14' AS DATETIME)) 
/**/
OR month(f.date_payed) IS NULL

Example

(AND YEAR(f.date_payed) = year(CAST('2020-09-14' AS DATETIME)) 
/**/
OR month(f.date_payed) IS NULL)

Basically the naked OR is throwing off your results.

Advertisement

Answer