Grab abandoned carters from the last hour in Oracle Responsys

Question

I'm trying to grab people out of a table who have an abandon date between 20 minutes ago and 2 hours ago. This seems to grab the right amount of time, but is all 4 hours old:

SELECT * 
FROM $A$ 
WHERE ABANDONDATE >= SYSDATE - INTERVAL '2' HOUR 
  AND ABANDONDATE <  SYSDATE - INTERVAL '20' MINUTE 
  AND EMAIL_ADDRESS_ NOT IN(SELECT EMAIL_ADDRESS_ FROM $B$ WHERE ORDERDATE >= sysdate - 4)

also, it grabs every record for everyone and I only want the most recent product abandoned (highest abandondate) for each email address. I can't seem to figure this one out.

score 0 · Answer 1 · answered Jul 09 '16 at 22:07

If the results are EXACTLY four hours old, it is possible that there is a time zone mismatch. What is the EXACT data type of ABANDONDATE in your database? Perhaps TIMESTAMP WITH TIMEZONE? Four hours seems like the difference between UTC and EDT (Eastern U.S. with daylight savings time offset).

For your other question, did you EXPECT your query to only pick up the most recent product abandoned? Which part of your query would do that? Instead, you need to add row_number() over (partition by [whatever identifies clients etc.] order by abandondate), make the resulting query into a subquery and wrap it within an outer query where you filter by (WHERE clause) rn = 1. We can help with this if you show us the table structure (name and data type of columns in the table - only the relevant columns - including which is or are Primary Key).

score 0 · Answer 2 · answered Jul 10 '16 at 04:58

Try

SELECT * FROM (
    SELECT t.*,
           row_number() 
             over (PARTITION BY email_address__ ORDER BY ABANDONDATE DESC) As RN 
    FROM $A$ t
    WHERE ABANDONDATE >= SYSDATE - INTERVAL '2' HOUR 
      AND ABANDONDATE <  SYSDATE - INTERVAL '20' MINUTE 
      AND EMAIL_ADDRESS_ NOT IN(
            SELECT EMAIL_ADDRESS_ FROM $B$ 
            WHERE ORDERDATE >= sysdate - 4)
)
WHERE rn = 1

another approach

SELECT *
FROM $A$
WHERE (EMAIL_ADDRESS_, ABANDONDATE) IN (
    SELECT EMAIL_ADDRESS_, MAX( ABANDONDATE )
    FROM $A$
    WHERE ABANDONDATE >= SYSDATE - INTERVAL '2' HOUR 
          AND ABANDONDATE <  SYSDATE - INTERVAL '20' MINUTE 
          AND EMAIL_ADDRESS_ NOT IN(
                SELECT EMAIL_ADDRESS_ FROM $B$ 
                WHERE ORDERDATE >= sysdate - 4)
    GROUP BY EMAIL_ADDRESS_
)

Grab abandoned carters from the last hour in Oracle Responsys

2 Answers2