Abdul Rahman Abdul Rahman - 5 months ago 10
SQL Question

MySql Count(sno) to Show old record by date

I have the following table: (Screenshot)

enter image description here

SELECT
COUNT(h.sno) AS total
FROM
receipt_mov_history h,
receipt r
WHERE h.rec_sno = r.sno
AND h.assigned_to = 6
AND h.is_completed = 0
AND h.completed_by = 0
AND h.assigned_on <= DATE_SUB(h.assigned_on, INTERVAL 15 DAY)
ORDER BY h.sno DESC


I am running the above query to get all the records which having the assigned_to = 6 and older than or equal to 15 days (15 days older record). In the snapshot table you can see there is only one row which is older than 15 days (highlighted). But the above query returns 2 in result.
I am not sure whats wrong with my query. Please help in me in this regard.

Answer
SELECT 
     COUNT(h.sno) AS total 
 FROM
     receipt_mov_history h,
     receipt r 
 WHERE h.rec_sno = r.sno 
    AND h.assigned_to = 6 
    AND h.is_completed = 0 
    AND h.completed_by = 0
    AND h.assigned_on <= DATE_SUB(now(), INTERVAL 15 DAY)
ORDER BY h.sno DESC 
Comments