Nsryjdtyk

MySQL and PHP

I am attempting to write a SQL statement that will allow me to identify an individual that has not checked in this month. I am stuck on how to write the code.

I have 2 tables

tbl_cust c
tbl_attend a

    SELECT

       c.custRFID,

       a.attendID,

       a.RFID,

       a.attendDate,

       a.attendStatus,

       a.attendStatusCode,

       a.attendNotes

    FROM

       tbl_cust c RIGHT OUTER JOIN tbl_attend a ON c.custRFID = a.RFID

    WHERE

       MONTH(a.attendDate) <> MONTH(CURDATE()) AND YEAR(a.attendDate) = 

       YEAR(CURDATE())

The code filters out this month, however, it returns every date prior to this month. I only want to display the names from table c that don't have an entry in table a for this month. I don't need all the records.

-------------------------------------------------------------

ID    RFID          Date         Time   Status    Status Code

-------------------------------------------------------------

1     01 02 03 E1   2018-01-03   1835    1        1

2     02 02 04 E2   2018-01-06   1235    1        1

3     01 02 03 E1   2018-02-07   1801    1        1

4     02 02 04 E2   2018-02-11   1109    1        1

5     01 02 03 E1   2018-03-03   1835    1        1

6     02 02 04 E2   2018-03-06   1235    1        1

7     01 02 03 E1   2018-04-07   1801    1        1

8     02 02 04 E2   2018-04-11   1109    1        1

9     01 02 03 E1   2018-05-01   1032    1        1

If this was May 16, 2018 the following should be the output:

-----------------------------------------------------------

ID    RFID          Date         Time   Status    Status Code

-------------------------------------------------------------

8     02 02 04 E2   2018-04-11   1109    1        1

This is only using 2 customers, as the actual table will eventually have over 5,000 customers in it. with at least 1 entry per month. Some will have 3 of 4 entries per month, however, I only want to see those with no entry in the current calendar month. I.e.... If they checked in anytime during the calendar month of May 2018, they should not be displayed on the output.

Thank you in advance.

If you want someone who has ever checked in, then you can use aggregation:

select RFID

from attend a

group by RFID

having max(attendDate) < date_add(curdate(), interval 1 - day(curdate()) day);

I would write this as an EXISTS query:

SELECT

   c.custRFID

FROM

   tbl_cust c

WHERE NOT EXISTS (SELECT 1 FROM tbl_attend a

                  WHERE c.custRFID = a.RFID AND

                  MONTH(a.attendDate) = MONTH(CURDATE()) AND

                  YEAR(a.attendDate) = YEAR(CURDATE()));

You can do this using LEFT JOIN.
Also, try not to use YEAR(column) & MONTH(column) functions in ON/WHERE clauses as SQL will not be able to use indeces in this case.
Something like this should do the trick:

SELECT

    c.custRFID,

    a.attendID,

    a.RFID,

    a.attendDate,

    a.attendStatus,

    a.attendStatusCode,

    a.attendNotes

FROM

    tbl_cust c

    LEFT JOIN tbl_attend a ON (

            c.custRFID = a.RFID

        AND a.attendDate > DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 1 MONTH)

        AND a.attendDate <= LAST_DAY(CURDATE())

    )

WHERE

    a.RFID IS NULL