I’m trying to make a query with different specifications in WHERE clause and some of them are being ignored.
x
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.
Advertisement
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.