Tuesday, 20 August 2013

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

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

No comments:

Post a Comment