0

I'm looking to add a field (LAST_OPENED) to a Data Extensions I created.

ET Subscriber has the records with all the fields needed but I was passed a list with out the (LAST_OPENED) field, I want to JOIN the two so the QUERY matches the records in ET Subscriber to the ones in my Data Extensions and adds (LAST_OPENED) as a result.

Here is the query I tried:

SELECT
t.*
FROM    
(SELECT A.*, ROW_NUMBER() OVER (PARTITION BY A.[LAST_OPENED] ORDER BY A.[LAST_OPENED]) as rn 
FROM Ent.[ET Subscriber] A
INNER JOIN [B_Med_Invitations_Audience20170531] B ON A.[LAST_OPENED] = B.[LAST_OPENED]

)  as t 
WHERE   t.rn = 1
  • 1
    What error / undesired result is it giving you? – Samuel Dare May 31 '17 at 18:08
  • If B_Med_Invitations_Audience does not have the Last_Opened field then joining them on that will not return any results – Samuel Dare May 31 '17 at 18:25
  • You're dealing with datetime datatypes, so the join would only work if both dates have the exact same times. – Adam Spriggs Jun 1 '17 at 13:11
  • Did this work for you? – Samuel Dare Jul 8 '17 at 9:35
1

From your question, it would appear that you are trying to join on a field that is not common between the two data extensions. Your query would therefore return no results.

For the sake of this exercise, I will assume the following.

  1. The [ET Subscriber] is an enterprise level DE
  2. [ET Subscriber] and [B_Med_Invitations_Audience] have a common field (id).
  3. You cannot PARTITION BY and ORDER BY the same variable

Try this:

SELECT t.* 
FROM   (SELECT B.*, 
               Row_number() 
                 OVER ( 
                   partition BY A.[last_opened] 
                   ORDER BY A.id) AS rn, 
               A.[last_opened] 
        FROM   ent.[et subscriber] A 
               INNER JOIN [b_med_invitations_audience20170531] B 
                       ON A.id = B.id) AS t 
WHERE  t.rn = 1 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.