Questions tagged [sql]

Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS)

Filter by
Sorted by
Tagged with
0
votes
0answers
10 views

Query All Results from a Journey in a single SQL

I am trying to query All the email results from a Journey in a single SQL but it doesn't work, so I separated a few steps to identify where is the issue. My final goal is to have a Data Extension with ...
0
votes
0answers
41 views

Split a Data Extension into 5 groups [closed]

J'ai une Data Extension qui contient deux colonnes Email et Engagement, je veux diviser cette DE sur 5 segments de 6000 ligne et à chaque DE je sélectionne le maximum (TOP) de "Engagement" ...
0
votes
1answer
21 views

De-duplicating emails before send

I am trying to write a query to avoid duplicate emails being sent out. I am using contact ID as subscriber key, so multiple contacts can share the same email address across the DB. I do not want to be ...
0
votes
2answers
26 views

Is SubscriberKey the only field you can query from a MobileConnect list?

I need to query a MobileConnect List to delete some Contact records. I created a MobileConnect List and then used automation studio to query it into a DE. This worked fine BUT is SubscriberKey the ...
0
votes
1answer
31 views

Is TriggererSendDefinitionObjectID unique for Opens?

I´m having trouble to query Opened emails from a Journey accurately. I want to keep a Data Extension with all the openings from the journey <'Homem_Casado_30_55'>, version number=2. So this is ...
2
votes
1answer
32 views

SQL: How to get the right SUM of two different columns from two different data extensions into one new DE

I have the following tables: In the third table I want to combine these data with the SUM option in SQL so the total of clicks and Sends are in one field. Like this: I created the following query: ...
0
votes
1answer
33 views

SQL: Unable to convert string to date [closed]

I have a data extension in which date is importing as text. I want to convert this text to date for segmenting the data. Here is my query, when i run this in automation I get 'Could not convert date ...
0
votes
2answers
33 views

Pushing data from one data extension to another

In Marketing Cloud, I have two data extensions. Both Data Extensions are being populated via entries in a journey. One runs off of the contact object and the other runs off the opportunity object. The ...
-4
votes
1answer
44 views

What is cast and convert in SFMC? [closed]

What is cast and convert in SFMC eg- SELECT Count(*) as Count, CAST(GETUTCDATE() as date) AS Day, GETUTCDATE() AS [Updated Date] FROM ENT.[Demo4 Contact__IQ] WHERE [Status Code] = 'Active'
0
votes
2answers
59 views

How would I retrieve data from one data extension and add it to another Data Extension? [closed]

SourceCodes are fields we assign to each email. They correspond to the email name. I need to retrieve the SourceCodes from Data Extension One (our source DE used for sending) and append them to the ...
0
votes
0answers
17 views

SQL query to find the name of the Sendable Data extension that was used to send a email

I am checking if there is a way to fetch the name of Sendable Data Extension to which the Email was being sent to using query. I have used Data Views to find the ListName. But I want get Data ...
1
vote
1answer
32 views

Marketing Cloud Query Activity SQL Syntax Error [closed]

New to SQL so be nice! :) I'm trying to run a simple SQL query in Automation studio, unfortunately I am getting this error - "An error occurred while checking the query syntax. Errors: Incorrect ...
-1
votes
1answer
24 views

SQL query to find Job ID and email name for triggered & scheduled email sends

I was trying to implement this answer given in StackExchange - Query for Emails sent in the past month, unsubscribes opens, clicks for each If you look at, “III-C. SQL Query”, Paragraph in the answer, ...
0
votes
1answer
36 views

Find the ‘total sent’ for each JOB IDs in last 1 month along with ‘list name’ it was sent to [closed]

Currently by using this query, I get the number of total_sends for a particular JOB id. But I want to modify the Query so that it shows total_sends , ListName , Date for that each job ID within a ...
0
votes
1answer
103 views

How to use SQL LEFT JOIN in Salesforce Marketing Cloud

I have questions around SQL join. I have 2 tables customer and events One customer can have optin/attend/Cancel multiple events. One customer can have Confirmed/planning/Cancel multiple Orders. now I ...
1
vote
1answer
35 views

DateTime is stored in SFDC as UTC, then sent over to SFMC via Connector and is converted to Central Time - need to reconvert back to UTC

I have implemented Connector between SFMC and Sales Cloud. The date/time values that are brought over with various objects, when they arrive in SFMC, they seem to be converted to CST (SFMC servers ...
0
votes
0answers
23 views

SSJS Execute SQL and Retrieve Result

Within SSJS, i want to execute SQL to retrieve a filtered data set from data extensions and views. Then i want to insert that data as a payload into a destination DE. I got the second part working ...
0
votes
0answers
24 views

'Journey Email Send By Day Report' (CSV file) cant be added into data extension

I am saving the ‘Journey Email Send Summary by Day’ as a CSV file into FTP. Which looks like this.. Then I am importing it using Automation Studio using ‘import activity’. My target DE has these ...
0
votes
0answers
27 views

Querying _Sent, joining to my Triggered Send DE gives me a count that is MUCH larger than my Triggered Send DE total count

I'm attempting to join my triggered send data extension to the _Sent data view. We need to pull delivery information into a DE along with the corresponding record's data payload (the data in the ...
1
vote
1answer
45 views

SQL query to find unengaged subscriber confusion

I want to exclude subscribers who have not ‘opened’ or ‘clicked’ email in last 6 months. I have the below sql query. Is this SQL query correct? Does the final results of this sql query results have ...
0
votes
1answer
23 views

Automation to prevent duplicate emails from sending a week apart

I have marketing cloud automation set up that puts our end users into one of 6 groups based on last character of their ID. Every month we send out an NPS Survey on the first Tuesday of every month and ...
0
votes
1answer
40 views

Unable to get percentage value in SQL Marketing cloud

Everyone The below query is working fine without bounce_rate condition. When I am adding the bounce_rate it is throwing an error. Anyone please suggest on this. SELECT j.emailname, j.emailsubject, s....
1
vote
1answer
36 views

SQL to not insert line if RecordTypeID = “123” or “123”

I have the below SQL in Marketing Cloud. I want to stop a new line being entered into my data extension if it contains one of two RecordTypeId's. Thanks in advanced! SELECT Contacts.Id, Accounts....
1
vote
1answer
43 views

Marketing Cloud SQL using CHARINDEX and LEFT / RIGHT

im trying to grab the value of a text field in a Data extension. It has a specifix syntax: example_IWantToGrabThis_example I want to grab the value between the underscores. I think i need to use ...
0
votes
1answer
75 views

Append a value to a Data Extension Field using SQL

I tried to follow the example used in this link, but I'm having trouble getting it to work. I get an err msg (Error saving the Query field.An unmatched parenthesis occurs in the query.). for ...
1
vote
1answer
39 views

Marketing Cloud SQL issue, Retrieving Latest six entries in Sent Data view with a minimum of 6

I want a data extension which has the latest six e-mail sent events from the Sent data view based on Eventdate, only when the subscriber has a minimum of six records in the Sent Data view I want to ...
1
vote
1answer
11 views

Retrieving Parent Account from OpportunityLineItems

I've made a few attempts to pull an opportunity field (Bill_To_Account__c from line items. I came across this post but can't figure out the solution. Attempt 1 SELECT Id, Forecast_Start_Date__c, ...
0
votes
1answer
21 views

How to get Related (Asset) Details of Organization

I am trying to get the Description value of the Asset of an Organization and need a query for that. Is that possible? When I try query SELECT AccountId, Product2.Name, Product2.ProductCode, ...
0
votes
1answer
40 views

Finding all the zip codes in a radius with all zip codes in the same field

I am trying to build out an way to return all of the surrounding zip codes for a particular one in a single field, so that I can use it for additional queries. I found this on the Salesforce Marketing ...
1
vote
1answer
41 views

Are subqueries supported in SFMC SQL?

When I try the following test of a subquery in SFMC Query Studio, I get the error message "Unable to create temporary data extension: Field name cannot be blank." According to official ...
1
vote
2answers
46 views

SQL to filter by year [closed]

I am unable to filter by year in SQL. This is my SQL: SELECT c.Organisation__c, DATEPART(year, s.ContractYear__c) AS ContractYear__c FROM .... WHERE DATEPART(year, s.ContractYear__c) = '2020' and it'...
1
vote
1answer
44 views

SQL to change part of a datetime to a specific time of that day

I have a use case where I want to use the "Wait by Attribute" function in Journey Builder to wait until a specific time of day to send and I need to figure out the SQL to change just the ...
1
vote
1answer
23 views

SQL Query to Combine Duplicates into New DE

I am having a hard time creating a query that takes duplicate leads located in the same Data Extension but are separated by the SW Tag field which breaks them into another row. I would like to combine ...
0
votes
0answers
25 views

Error: Violation of PRIMARY KEY constraint - How can I avoid inserting changed data with same PK?

I'm having an issue with a primary key violation that is a little different than others posted. I have a Contact, who can have one or more Reservations. I need to pull a list for a Journey very ...
0
votes
1answer
30 views

how to extract all click counts per url or link name for specific jobs using data views?

I would like to use data view _CLICK to extract all a list of all clicked URLs along with the count (how many unique subscribers clicked on the link) for specific job ids. Can anyone help me with the ...
-1
votes
1answer
26 views

How to delete records automatically and periodically , object cases? [duplicate]

I couldn't find a way to delete records from the case object automatically periodically with code apex please help me with this.
0
votes
1answer
20 views

Query Studio - Could not convert date and/or time from string data type

On one data extension, I have a DateIn field modeled as text. The value can be in one of the two formats like: 2015-06-23 16:12:04.703 8/6/2018 11:46:59 AM When I cast the field to date and use it ...
0
votes
1answer
33 views

how to write a trigger without nested FOR loop

I wrote a trigger but My professor asked me to write it without nested loop and I don't know how to do it. I want to do is when a contact gets created i need to take field named- 'Description' of ...
1
vote
2answers
77 views

how to make sure update contact step in journey builder honors primary key?

I am trying to create one Data Extension that can be used to log all contacts going into multiple journeys and then use update contact event within journey to update row when a contact exist journey. ...
1
vote
2answers
62 views

Validating phone numbers using REGEX in SFMC

I am looking into a use case of validating phone numbers for 3 Scandinavian countries (DK, SE, NO) which have between 8 and 10 digits, and the 2-digit country code in front. I have looked into the ...
1
vote
1answer
56 views

Journey Handling: Deleted or non existent subscribers

Scope: Journey Builder Main Problem: Dealing with deleted / unsubscribed and never existent subscribers inside a journey. These kind of subscribers shall not receive emails from E-Mail Activities ...
0
votes
1answer
30 views

Display multiple Trigger sends email names on landing page

I want to display the names of all the trigger send emails present in the account on landing page. A user should be able to select the one name from this list (Like dropdown list). I have tried for ...
0
votes
1answer
62 views

SQL query to exclude subscribers

I'm trying to pull the data from orderDE, where purchaseddate is (day - 1). if the customer only purchased a single product in a day, include him in results. If a customer purchases more than 1 ...
0
votes
1answer
48 views

Subscribers that subscribed in the last 30 days but haven't purchased

I'm using this logic in the Query Studio: SELECT UUID FROM [CL_ACTIVE_SUBSCRIBERS] WHERE S.UUID NOT IN ( SELECT [Contact key] FROM [CL-OrderCompleted] ) AND FORMAT(S.FIRST_TIME, 'yyyy-MM-dd') &...
0
votes
1answer
37 views

Marketing Cloud - Remove Trailing Char0x00 via SQL

I have been trying to create a SQL Query that would remove the trailing 0x00. Here is the sample data: I tried following the steps from https://stackoverflow.com/questions/43409279/sql-server-...
0
votes
0answers
40 views

Trying to get a fully comprehensive list of SFMC Data Views

Help documentation shows a number of data views, however, there are a number of data views that are not documented such as _SmsMemberSharedShortCode. Is there any way via the API to retrieve a fully ...
0
votes
0answers
23 views

Automation Studio to fetch 'subscriber status' = 'active' from All subscribers and store it in a new Data Extension? [duplicate]

I need to create a automation, I want to fetch 'All Subscribers'(from email studio) into DE Filter this DE to show only Subscriber_Status=active My goal : Create a DE with customers that have ...
0
votes
0answers
17 views

Finding SubscriberKey Who Did Not Open Across Two JobIDs

I am trying to create a SQL query that would return results for contacts that were sent two emails and didn’t open both. The count should be around 9,000 but I’m getting a much higher number. I think ...
0
votes
1answer
46 views

Querying _job data view is resulting DeliveredTime = Null

I have a journey builder send in a child BU without using any publication list. In the journey builder UI and email tracking, I can see that sends were being delivered and opened. However, querying ...
0
votes
2answers
41 views

How to limit a query in Marketing cloud

I am trying to query a data extension which stores email to get back subscriber key, the DE stores email from a cloud page form so that I could redirect them to a profile center by making a custom ...

1
2 3 4 5
14