Help structuring MySQL SELECT statement

Question

I have a transaction table that registers email reads using a tracking pixel in our email marketing messages. I'm trying to structure a query that will give me the records in the transaction table whose last recorded email read was more than 6 weeks ago. Reason being, those transactions haven't "engaged" in our email marketing and I need to update one of their subscriber fields to reflect that (so they can receive a different type of email).

If I were to write it out in English, the statement would look kind of like this:

Select the row from transactions where the subscriber id IS present more than 6 weeks ago but IS NOT present in the last 6 weeks.

So, one initial thought is to create my recordset of those who have opened emails in the last 6 weeks, then compare that against the rest of the table? Can I do this in one statement or should I break it into two?

Thanks in advance for anybody who can help with this.

Joe Stefanelli · Accepted Answer · 2011-09-19 20:28:12Z

3

SELECT SubscriberId
    FROM TransactionTable
    GROUP BY SubscriberId
    HAVING MAX(ReadDate) < DATE_SUB(NOW(), INTERVAL 6 WEEK)

edited Sep 19, 2011 at 20:28

answered Sep 19, 2011 at 20:23

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Stephan Hovnanian Over a year ago

Thanks Joe; if I have a couple more WHERE conditions where do I put them? (There's one field that actually excludes them from this query regardless, call it status = "C")

Joe Stefanelli Over a year ago

The WHERE comes between the FROM and the GROUP BY.

Stephan Hovnanian Over a year ago

Awesome. Worked. Got about 120 records all within the right timeframe. Going to compare a sample of them against a recordset inside 6 weeks to see if this is indeed the solution. Thanks again!

Stephan Hovnanian Over a year ago

this worked perfectly. Just so I understand the MAX part correctly, it's going to read all the ReadDate values in the table but only return those records whose highest value is less than 6 weeks ago?

Marc B · Accepted Answer · 2011-09-19 20:22:19Z

0

No need for fancy math. Your requirement boils down to a simple < clause: Any user which has no transactions recorded in the last 6 weeks:

SELECT userID
FROM transactionstable
WHERE MAX(timestampfield) < DATE_SUB(now(), INTERVAL 6 WEEK)
GROUP BY userID

answered Sep 19, 2011 at 20:22

Marc B

362k44 gold badges433 silver badges508 bronze badges

3 Comments

Joe Stefanelli Over a year ago

But this could still return customers who also have more recent transactions.

Stephan Hovnanian Over a year ago

thanks for the quick reply; however, I see how that will give me all the records prior to the 6-week-mark, but how does the query know whether one of those subscribers was also found inside the 6-week-mark? Because I don't want to update those people...just the ones that are outside the 6-week-mark but not inside.

Marc B Over a year ago

Good point. revamped the query a little bit. Mostly the same as Joe's above, but using a WHERE clause instead of HAVING.

MahanGM · Accepted Answer · 2011-09-19 20:27:20Z

0

Define a field which shows the last 6 weeks activity. I mean an integer 0 or 1. Then:

SELECT * FROM transtable WHERE activityfield = 0 AND datefield < DATE_SUB(now(), INTERVAL 6 WEEK)

answered Sep 19, 2011 at 20:27

MahanGM

2,3925 gold badges32 silver badges48 bronze badges

Comments

Wasif · Accepted Answer · 2011-09-19 20:28:41Z

0

If the database you are working with supports Oracle SQL, then try this:

SELECT * from transactions
WHERE subscribed_date < SYSDATE-(6*7);

answered Sep 19, 2011 at 20:28

Wasif

1711 silver badge5 bronze badges

Collectives™ on Stack Overflow

Help structuring MySQL SELECT statement

4 Answers 4

4 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related