MySQL query fetch incorrect data

Question

I have a query which must fetch payment graphs which must be payed 1 month ago. For example, it must get rows with payment date with 2017.12.04 (post date) and earlier, but it also get data for December 17, 10, etc. this is a query

select CONTRACT_ID
from PaymentGraph
where date(now()) >= date_sub(PAYMENT_DATE, interval DAY(LAST_DAY(now())) day)
  and state = 'A'
  and ifnull(paid, 0) < amount

I don't know why it works this way, can anybody say what may be the problem?

Add some sample table data and the expected result - as formatted text, not images. — jarlh
– jarlh, Commented Jan 4, 2018 at 13:02

Jan Zeiseweis · Accepted Answer · 2018-01-04 13:06:03Z

2

It looks like your date comparison is a bit to complicated, you can write it a bit easier try this:

SELECT CONTRACT_ID
FROM PaymentGraph
WHERE PAYMENT_DATE <= curdate() - INTERVAL 1 MONTH
    AND STATE = 'A'
    AND ifnull(paid, 0) < amount;

answered Jan 4, 2018 at 13:06

Jan Zeiseweis

3,7482 gold badges19 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Radim Bača · Accepted Answer · 2018-01-04 13:06:20Z

2

Try this

select CONTRACT_ID
from PaymentGraph
where PAYMENT_DATE <= DATE_ADD(curdate(),INTERVAL -1 MONTH)
      and state = 'A'
      and (paid < amount or paid is null)

This solution is good in a sense that all attributes are SARGable, therefore, an index such as PaymentGraph(PAYMENT_DATE, state, paid) can be fully used

answered Jan 4, 2018 at 13:06

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

Collectives™ on Stack Overflow

MySQL query fetch incorrect data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related