CODEWITHSUNDEEP
phpmysql
Stephan Hovnanian
Stephan Hovnanian

Reputation: 189

Help structuring MySQL SELECT statement

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.

Upvotes: 0

Views: 67

Answers (4)

Wasif
Wasif

Reputation: 171

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

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

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

Upvotes: 3

MahanGM
MahanGM

Reputation: 2382

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)

Upvotes: 0

Marc B
Marc B

Reputation: 360672

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

Upvotes: 0

Related Questions