New answers tagged

1

You can use this query to deduplicate using the email address: SELECT SubscriberKey, Email FROM (SELECT DEST.SubscriberKey, DEST.Email, DEST.Creation_Date, ROW_NUMBER() OVER (PARTITION BY DEST.Email ORDER BY DEST.Creation_Date DESC) AS Row FROM YOUR_DE_NAME DEST ) as s WHERE row=1 In this case, the query will return the record ...


0

@Gortonington's answer regarding Three-Valued Logic solved the problem. Putting some additional info in an answer here for the sake of formatting. Specifically in this case I used a CASE statement that looked like this: SELECT * FROM [TABLE A] a LEFT JOIN [TABLE B] b ON a.ContactKey = b.SubscriberKey WHERE a.Seasonal_Gift_Inspiration = 1 AND b....


2

I think you would have to separate this into 2 queries. One to a staging table and then another to overwrite the final file. Query 1: SELECT EmailAddress, Field1, Field2, Field3, Field4, CASE WHEN Field1 IS NOT NULL AND Field1 != '' THEN 1 ELSE 0 END + CASE WHEN Field2 IS NOT NULL AND Field2 != '' THEN 1 ELSE 0 ...


0

I have only tried this in SQL Management Studio, but should work in Marketing Cloud too. It should be enough to get you going. SELECT ContactKey, PopulatedColumns FROM ( SELECT ContactKey, (CASE WHEN COALESCE(Column1, '') != '' THEN 1 ELSE 0 END + CASE WHEN COALESCE(Column2, '') != '' THEN 1 ELSE 0 END + CASE WHEN ...


0

Could you use the LEN() function to determine the length of the max field value. Something like this? Select * From [Table Name] Where LEN(Field Name) = 250 (or whatever the max character length is)


2

The issue you are facing is that SQL uses Three-Valued Logic, which means there is True, False and Unknown(NULL). A value that is NULL therefore cannot be true or false, which causes issues with Not Equals conditional statements in SQL. For instance, lets say TableA has values in ColumnA of 1|2|NULL and you use the following SQL: SELECT * FROM TableA ...


4

Your first query does not include the _Open data view, so it won't work for your use case. If you want to display all subscribers who opened an email, you can use the following, bearing in mind what Lukas said - Data Views only hold last 6 months worth of tracking data: SELECT s.SubscriberKey, j.EmailName, s.EventDate as SentDate, o.EventDate as ...


0

In your second query, write C.ContactKey is not NULL. Something like SELECT * FROM [TABLE A] a LEFT JOIN [TABLE B] b ON a.ContactKey = b.SubscriberKey LEFT JOIN [TABLE C] c ON a.ContactKey = c.ContactKey WHERE b.SubscriberKey IS NULL AND c.ContactKey IS not NULL Also for consistent results, ensure that you filter out NL records in your query to populate ...


3

Unfortunately there is no way to gather this information as it is only present in the submit HTTP headers and after that it disappears once the processing page is closed. Unless you have some sort of pre-existing analytics around these (which it doesn't sound like you do) or you store the raw submit headers somewhere, you are out of luck. Usually in ...


4

The issue might be that the date format of the two is not matching correctly. Try using: DATEDIFF(year, CAST(creation_date__c as DATE), CAST(getdate() as DATE)) So that you cast each as identical data types, which should remove any possible data mismatch.


2

If your field creation_date__c is of the correct type, date, this should fulfil your use case: SELECT DATEDIFF(year, creation_date__c, getdate()) AS AnniversaryYears FROM DataExtensionName Result: The difference between GETDATE() and SYSDATETIMEOFFSET() is that the latter will just add the time zone, which won't be comparable to creation_date__c since it ...


1

Try this.. SELECT s.SubscriberKey, j.EmailName, s.EventDate as SentDate, o.EventDate as OpenDate, c.EventDate as ClickDate FROM Recipients r LEFT JOIN _Sent s ON s.SubscriberKey = s.SubscriberKey LEFT JOIN _Job as j ON s.JobID = j.JobID LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o....


0

I'm not sure I quiet understood what's the exact error. But I'm not familiar with the first part of your WHERE clause. Instead of writing: WHERE s.Emailname = (EmailABC,EmailXYZ) Try using the IN operator: WHERE s.Emailname IN (EmailABC,EmailXYZ) This shall do it.


0

A primary key is an identified Unique ID. So basically it is the definition that differentiates a record from being an 'add' or an 'update'. So when you do an overwrite without a primary key, it just adds all records into the DE, including those with the same CustomerKey, because you did not identify it as a primary key. When you change it to contain a ...


1

Two remarks: you also compare the year, which will never evaluate to true. you don’t observe the system time zone (UTC-6) which in some cases can cause issues, as the date of birth is normally provided in local time zone Here is a sample SQL overcoming both issues: select * from anywhere WHERE datepart(day, [Date of birth]) = datepart(day,...


2

Rows are being updated based on your primary key(s). Both your subscriber key and appointment ID should be selected as primary keys in the destination data extension. So if your data is not being updated, then either only one of the above fields is selected as primary key in data extension, your row matching the keys is missing, or the field to be updated is ...


8

I'd recommend combining this into a single query. Also converting to the date data type will is less costly than converting all of the dates to strings. Something like this: SELECT ds.emailaddress , case when ds.dateJoined >= convert(date, getDate()-14) then 'New' when ds.dateJoined >= convert(date, getDate()-90) and ds....


0

You say in Extended all boolean values are empty, so they are NULL. I believe making a Field != 'value' statement in SQL includes all other records where Field has a value different from 'value', but it does not include records where Field has no value. Your second query returns results because of e.Boolean IS NULL. I hope that helps. By the way: Are you ...


0

You didn't say what the name of your data extension is, I'm going to assume it's DataExtension1. I think this should work: SELECT GlobalID, CategoryDescription, MAX FROM DataExtension1 WHERE MAX = (SELECT Max(MAX) FROM DataExtension1) Note that with this statement you would get 2 records if there were 2 rows with the same MAX value. I hope that helps. ...


5

I will be honest and I am not sure what most of your question is asking, but I tried to fix your query as best I can to provide JobID | TotalSends | TotalBounces | TotalDelivered for all that happened in previous month. SELECT s.JobID , SUM(ISNULL(s.TotalSends,0)) as TotalSends , SUM(ISNULL(b.TotalBounces,0)) as TotalBounces , SUM(s....


4

Using LEFT JOINS with appropriate Data Views will ensure that you will get both, Opens OR Clicks, instead of just getting Opens that also have a Click: SELECT r.customerID AS SubscriberKey, r.shouldbesentmarketingemails AS Subscribed, j.EmailName, s.EventDate as SentDate, o.EventDate as OpenDate, c.EventDate as ClickDate FROM Recipients r LEFT JOIN ...


0

If you add a Wait period to the end of your journey and set it to hold people for 11 months and then make sure the setting is to allow re-entry only after exit in Journey Settings this will work. The SQL you have put on there is fine


Top 50 recent answers are included