CODEWITHSUNDEEP
mysqlsql
Czeshirecat
Czeshirecat

Reputation: 546

counting records in one table that occur before and after a max date in another table

(MySql engine) I have a table that records contacts between staff and members of a club called "Contacts". I need to know whether the latest contact made a positive effect on a members attendance by comparing the average number of times the member logged before and after that contact during an overall time period. Logins are stored in the logins table. User info is stored in the users table.

The following sql statement pulls individual lines out with a line for each login (1 per day) per club member for club members who had at least one contact during the period. What I'm stuck with is finding the total number of logins for pre and post maxcontactdate per member.

So I want the resulting table to have columns with rows grouped on recid, fk_staff_users_recid

"recid", "maxcontactdate", "fk_staff_users_recid", "pre_maxcontactdate_login_count", "post_maxcontactdate_login_count"

Can somebody help please?

SELECT
  recid,
  maxcontactdate,
  fk_staff_users_recid,
  logtime
FROM
(
  /* Selects user id, with the staff that made the contact, and the max contact date for that member of staff */
  SELECT fk_users_recid,
          fk_staff_users_recid,
          MAX(contactdate) AS maxcontactdate
  FROM
    contacts
  WHERE
    contactdate BETWEEN '2013-07-20' AND '2013-08-20'
  GROUP BY fk_users_recid, fk_staff_users_recid
)contacts,
users

JOIN
(
  /* Selects all login dates between the dates specified */
  SELECT fk_users_recid,
         DATE(logins.logintime) AS logtime
  FROM
    logins
  WHERE
    logintime BETWEEN '2013-07-20' AND '2013-08-20'
  GROUP BY fk_users_recid, logtime
)logins
ON logins.fk_users_recid = users.recid 

/* Only pull the members who had contacts during the period */
WHERE
users.recid = contacts.fk_users_recid

Upvotes: 0

Views: 278

Answers (1)

GarethD
GarethD

Reputation: 69789

You can do this using a conditional count (CASE WHEN .. inside the count statement)

SELECT  recid,
        maxcontactdate,
        fk_staff_users_recid,
        COUNT(CASE WHEN logins.logtime < contacts.maxcontactdate THEN 1 END) AS pre_maxcontactdate_login_count,
        COUNT(CASE WHEN logins.logtime >= contacts.maxcontactdate THEN 1 END) AS post_maxcontactdate_login_count,
        COUNT(*) AS Total_Logins
FROM    (   SELECT  fk_users_recid, fk_staff_users_recid, MAX(contactdate) AS maxcontactdate
            FROM    contacts
            WHERE   contactdate BETWEEN '2013-07-20' AND '2013-08-20'
            GROUP BY fk_users_recid, fk_staff_users_recid
        ) contacts
        INNER JOIN Users    
            ON users.recid = contacts.fk_users_recid 
        INNER JOIN
        (   SELECT  fk_users_recid, DATE(logins.logintime) AS logtime
            FROM    logins
            WHERE   logintime BETWEEN '2013-07-20' AND '2013-08-20'
            GROUP BY fk_users_recid, logtime
        ) logins
            ON logins.fk_users_recid = users.recid
GROUP BY recid, maxcontactdate, fk_staff_users_recid;

Upvotes: 2

Related Questions