Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – query to find all records from a table with clients that have had activity from 2019 to 2022 but no activity since

I got a table clients and another table of authorization. They are tied by foreign key orgid. I want to be able to pull distinct list of clients that had any records in authorizations from 1/1/2019 to 12/31/2022 but no records after that. datecreated is the column in authorization i would be pulling from.

I have this but not sure how to remove the ones that have had activity after 12-31-2022.

SELECT DISTINCT
    c.*
FROM
    clients AS c
        JOIN
    authorization AS a ON c.orgid = a.orgid
WHERE
    (a.datecreated > '2019-01-01 00:00:00'
        AND a.datecreated < '2022-12-31 00:00:00')
Tags:

2

Answers


  1. 0

    You can use a correlated subquery to check if one clinet has any activities after 31.12

    SELECT DISTINCT
    c.*
FROM
    clients AS c
        JOIN
    authorization AS a ON c.orgid = a.orgid
WHERE
    (a.datecreated > '2019-01-01 00:00:00'
        AND a.datecreated < '2022-12-31 00:00:00')
AND NOT EXISTS ( SELECT 1 FROM authorization 
                 WHERE orgid = a.orgid AND datecreated > '2022-12-31 00:00:00')
    Login or Signup to reply.
  2. 0

    nbk gives you already a very good answer. This is a follow-up answer, optimizing more things:

    1. There is no need for a JOIN if you just need result from table c. You can use an EXISTS-Clause, make it even more understandable, as you use also an NOT EXIST-Clause.
    2. You can use BETWEEN…AND instead >= and <=
    SELECT DISTINCT
    c.*
FROM
    clients c
WHERE EXISTS (
    SELECT 1
    FROM authorization a
    WHERE a.orgid = c.orgid
    AND a.datecreated BETWEEN '2019-01-01 00:00:00' AND '2022-12-31 00:00:00'
)
AND NOT EXISTS (
    SELECT 1
    FROM authorization a
    WHERE a.orgid = c.orgid
    AND a.datecreated >= '2023-01-01 00:00:00'
)

    Be carefull, using the time 00:00:00, as this will maybe give you some records after 00:00:00 on the same day when using > or >=, which is maybe not what you want. To avoid this, you can either

    1. use the YEAR-Function: ex. YEAR(a.datecreated) > 2022
    2. use the DATE Function: ex. DATE(a.datecreated) > 2022-31-12
    3. correct the time to 23:59:59
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search