0

I am in the process of building a mock up preference centre and for this of course i need the assumption that a Subscriber Key can exist multiple times in the data extension where i am saving the preferences updates.

I want to be able to correctly get via SQL the latest entry of a Subscriber Key. In the query below I need to be able to retrieve however has a preference for Winter =True and for Europe= True.

As you can see from the print screen attached i have 2 Subscriber Keys but the most recent entry does not have these preferences as true. I would expect my query to return 0 (as that's the most recent info i have for that subscriber) however the result returns 1 (associated with the first time i submitted the preference mock up cloud page).

select b.[Contact ID],b.[Booking: Booking Number],b.[First Name],b.[Booking Passenger Email],a.DateSubmitted
from 
(select SubscriberKey,max(Datesubmitted) as [DateSubmitted],Europe,Winter from [AUS mock Preference Centre - NO primary key]
group by SubscriberKey,Europe,Winter) as a
JOIN [MTP TT JB CRIS 2] as b 
ON b.[Contact ID]=a.SubscriberKey
where (a.Europe='True' and a.Winter='True')

How should i amend my query in order to be able to retrieve my data successfully?

Giuliettaenter image description here enter image description here

  • You are in a mess because you cannot decide which entry is the most recent one. You need to have a date field in your dataextension and then get the most recent. Maybe (but only maybe and it can be wrong) you could also use the ROW_NUMBER ( ) function and only get the highest row number but i believe that can give you wrong information. Normally you would set a date field with default value "current date" to solve things like this.or is datesubmitted inside the dataview? I dont see it. – Johannes Schapdick May 28 at 9:16
  • thanks @JohannesSchapdick. I actually do have a Date field that holds the time stamp when that record submitted the cloud page with the associated preferences. Please see the attached image. that is also the field that i am using sql to pull the most recent entry max(Datesubmitted) but i do not get correct results – Giulietta May 28 at 9:19
1

I believe it would be something like this:

select
b.[Contact ID],b.[Booking: Booking Number],b.[First Name],b.[Booking Passenger Email],a.DateSubmitted
from (
    select 
    aus.SubscriberKey as sKey,aus.DateSubmitted,aus.Europe,aus.Winter
    , row_number() over (partition by aus.SubscriberKey order by aus.DateSubmitted desc) as row
    from [AUS mock Preference Centre - NO primary key] as aus
) as a
JOIN [MTP TT JB CRIS 2] as b 
ON b.[Contact ID]=a.sKey
where (a.Europe='True' and a.Winter='True' and a.row = 1)

You don't need group by because you want to receive a single row and you do not want to aggregate it.

I havent tested this but its usually the way to go, i guess ;) You can also watch the row number and partition function here

  • thanks @Johannes Schapdick; it worked. – Giulietta May 28 at 13:25
  • 1
    I hope you understood how it works and the principle behind it. It can help you many times ;) – Johannes Schapdick May 28 at 13:28

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.