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
1answer
1k views

Query Activity to Return Number of Opens and/or Number of Clicks

I am trying to pull data about a sent email into a data extension using a SQL query in the Query Activity section. The below is my query which pulls back all information about an email using a ...
1
vote
2answers
5k views

what update type to use in query for adding and updating in data extension

I have a query in marketing cloud that run daily to add data in a data extension. What should be the "UPDATE TYPE" if i want to add new records in my target data extension. I do not want to update ...
1
vote
1answer
747 views

Create a data extension from a Synchronized Contact Data Extension

I am trying to create a copy of a Synchronized Contact Data Extension (from Salesforce) into the non Synchronized Data extension using a SQL query activity. Can someone give me an example of the ...
0
votes
2answers
3k views

How to use SQL ORDER BY statement in marketing cloud (error says requires TOP)

I have a data extension that is created using SQL query on hidden tables that record tracking info related to sent emails. Each table contains JOB ID which is the key to associating the tracking data. ...
0
votes
3answers
2k views

Query Failing: Time Out

I have a query that has started failing due to the fact that it has over 400M rows. Although there are joins and fuzzy matching (LIKE '%XXXX%') it is still failing. Using NO LOCK too I have used ...
1
vote
1answer
812 views

Aggregating Marketing Cloud Data Views

I have created a Data Extension (account) which contains an account_id and various boolean fields and email address, I want to create a field which stores the last opened date from _Opened table via ...
2
votes
1answer
472 views

Selecting full records, but only 1 of many (if multiple exist)

Say I have a table: ID | Store | Distance 111 | ABC | 0.0249126539935 111 | ABC | 0.0249126539935 111 | DEF | 0.0149126539935 111 | DEF | 0.0149126539935 I am attempting to return: ID | Store | ...
0
votes
1answer
1k views

Using CASE to update a field

I'm getting an error on my case statement in SFMC. Is it a simple mistake of syntax? SELECT *, CASE WHEN status <> 'active' THEN 'active' ELSE status END AS status FROM [sample table] WHERE ...
2
votes
1answer
671 views

Exact Target SQL- LIKE condition does not return anything

New to ET and have written a query in the automation studio. I have two tables I am looking through and am trying to return what matches my LIKE condition. The LIKE is looking for emails from TableA ...
0
votes
1answer
134 views

Querying members of a specific list, who are not in a few specific data extensions, and who have opened/clicked in 180 days

I'm almost certain there is a more efficient way to write this, but I can't seem to see how exactly. Would it be better to do the first query to populate a DE, and then run a 2nd query against that ...
1
vote
1answer
572 views

System error when running a query with unions to join multiple data extensions

I am trying to pull the data from multiple data extensions to another DE and i am using union to achieve it.Also I have subscriber key as a primary key in the target data extension.However i get ...
0
votes
1answer
380 views

how to use query in marketing cloud that uses data extension for where clause?

Right now I have this query which works great: SELECT SubscriberID, EmailAddress FROM ent._Subscribers WITH (NOLOCK) WHERE SubscriberID = '1234567890' But, since I want to find out EmailAddress of ...
0
votes
1answer
2k views

Update boolean value in data extension from false to true with Sql Query

I have an existing Data Extension with attributes that contain booleans fields that are marked as false. I want to force those fields and the boolean value to now equal true. I am wondering if there ...
0
votes
2answers
987 views

How to Avoid The Duplicate Subscriber Key

How to Avoid The Duplicate Subscriber Key in Query Activtiy this my Query Select DISTINCT s.SubscriberKey, s.JobID, s.BounceCategory as BounceType, s.SubscriberID, s.IsUnique, (CONVERT(varchar,s....
3
votes
2answers
399 views

Ordered Data Extension into 3 ordered segments

Trying to take 3.8MM records and break them up into 3 groups. Group 1 : 0 - 160,000 (already done) Group 2: 160,001 - 960,000 Group 3: 960,001 - 3,610,000 They need to be ordered by when they were ...
0
votes
1answer
89 views

Data type conversions SQL

I have two data extension (DE1 and DE2) with a boolean field called Subscribe, an email address field called EmailAddress and a text field called Campaign. I want to create a Query with the following ...
0
votes
2answers
709 views

SQL help for Query Activity in SFMC

I need to calculate a field for a Data Extension based on multiple records in another Data Extension. JourneyEntrance (Data Extension to be updated) SubscriberKey, PolicyNumber, Complete Cases (...
0
votes
1answer
82 views

eventdate to equal 01/07/2017

I have written this SQL below. I'm trying to get the 1st of July 2017. however, it's pulling through the 30th June and the 1st of July but can quite figure out what's wrong. SELECT SubscriberKey , ...
0
votes
3answers
354 views

Filtering a Query

Is it better to create a query with filtering within the query to populate a data extension - or - do you create a query and then create a filtered extension based on your parameters? My use case is ...
0
votes
3answers
1k views

What's the best way to exclude domains from a send?

I want to exclude recipients at two different domains from sends. Is Audience Exclusion Script the best way to accomplish this? If yes, what does that script look like? However, the data extensions I'...
0
votes
1answer
165 views

How do I find the most common instance for a customer?

I want to determine the most common store a customer shops at. For example, I have a table that lists the store someone shopped. Customer_ID, Store_Number: 1456, 255 1456, 255 1456, 266 1456, 277 ...
1
vote
1answer
377 views

Error with Duplicate Primary key in Marketing Cloud SQL

We have multiple newsletters that generate leads for marketing emails. I'm trying to automate the process of consolidating those leads into one master data extension after they have been on a ...
1
vote
0answers
416 views

Total sent in last 7 days from data view

I am using this query to pull how many emails were sent in last 7 days. Select count(DISTINCT JobID) AS TotalUniqueSent FROM _Sent WITH (NOLOCK) WHERE DATEDIFF(day,EventDate,GETDATE()) <= 7 But ...
0
votes
1answer
225 views

SQL Count Query Query Without Data Extension?

Is there a way to simply get record counts of a query without having to populate a data extension? I want to see how many records meet some criteria without creating a new DE, or a new filtered DE. ...
0
votes
1answer
2k views

Combined Data Extensions (3 DE's) SQL Query

I am attempting to build a combined data extension from 3 other related data extensions via Query & Automation Studio SQL Query. I have successfully (though I'm not sure that the # of records I ...
1
vote
2answers
847 views

Emailing Report - Automation Studio

Is there a way to Email a Scheduled Report (created using SQL) in Marketing Cloud Automation Studio?
0
votes
1answer
277 views

How to format a number with comma?

Currently, I am using this query SELECT top 10 snt.Domain, count(*) AS DomainCount from _Sent snt group by snt.Domain order by count(*) desc The data type of "DomainCount" is number and the ...
1
vote
1answer
81 views

SFMC Query: Never received/Never received an email that contains - single query

i am wanting to join two Quries together so they work as one rather than independently (both work as intended on their own) Query 1 The first one will select all subscribers who are customers and ...
1
vote
2answers
7k views

Using query for a specific date

I am trying to pull data from SENT data view based on date. What format of date should I use? I have tried this but no results: SELECT * FROM _Sent WITH (NOLOCK) WHERE EventDate = '2017-05-29' I ...
0
votes
1answer
46 views

QC Journey Builder Output

Is it possible to verify the output in each journey builder by using SQL to verify?
1
vote
1answer
346 views

Using SQL script on data views for calculating stats

I have created an autoamtion that pulls the data from Data View and saved the records in a data extension (My_Sub_DE) using the following sql query. SELECT * FROM ent._Subscribers WITH (NOLOCK) Now, ...
0
votes
1answer
505 views

Query Error - cannot convert nvarchar

I am trying to return details associated with a certain email address . I am using the query below: select emailaddress ,subscriberkey ,record_type_name__c from mastersubscriberde where ...
0
votes
1answer
200 views

I want to query a list of all clicks for the previous day and order them by the URL, but I can't get that to work?

Here's the query I'm trying. I'd like all similar URLs together so when I use some ampscript I can accurately dedupe them, but this isn't working. Anyone have ideas what I might be doing wrong? ...
0
votes
1answer
574 views

How do I fetch list of in-active subscribers?

Any query to grab a list of inactive subscribers? I want to list out all subscribers those have received at least 5 emails but did not open any of those 5 emails in past 3 months?
0
votes
1answer
520 views

Query To Pull Contact Records That Have Been Created In The Last 1 Hour

SELECT WheelersUsers.user_id,WheelersUsers.email,WheelersUsers.created FROM WheelersUsers WHERE ((DATEDIFF(hh,GETDATE(),WheelersUsers.created)* -2 )/2) <= 1 And I wanted to know how I can ...
0
votes
1answer
124 views

Issue querying _Click table to get users clicking the same number of times in two or more different `LinkNames`

I am having a hard time with a query that I did for Salesforce Marketing Cloud, and you might know where is the error. I am querying the table _Click, (table schema), and I want all my users that had ...
0
votes
1answer
140 views

SQL Query Script to Pull Automation Data

How can I write a SQL query to pull a list of all recipients and their email addresses of an email in an automation?
1
vote
1answer
294 views

Fetching all Testclasses only from un manage package

Query: Select id from apexclass where class name like '%test%'. Above query if fetching both managed and unmanaged package classes, can i filter only unmanaged package.
1
vote
1answer
395 views

SQL query, simple LEFT JOIN substraction

I have a very simple query, but somehow it fails, im pretty sure its just a syntax error, but I can find the error... Its very simular to this example below.. I want every that ONLY exists in TableA ...
0
votes
0answers
297 views

How to compare timestamp stored in Data Extension as Text

I am downloading a Report into from Salesforce Cloud into a SFMC Data Extension. The report consists of contacts that have signed up Today. A sample data of the 'Created Date Backend' field is : 31....
3
votes
3answers
189 views

Case copy value from another field into target data extension

I'm trying to write this query and the idea is to look at the field "MessageName" and when that field value is equal to 'Text 1 Response', I want to copy the field value from another field called ...
0
votes
1answer
518 views

Query activity : To group a field based on Account ID [closed]

I have a data extension (DE_A) that has following coloumns: Column 1: Account ID (no duplicates), Column 2: Date (the field is date) I have another data extension (DE_B) that is subset of DE_A ...
1
vote
1answer
2k views

Data extension Inner Join - Incorrect syntax near the keyword 'WHERE'

I am trying to join 2 data extensions and input the result to a third data extension: SELECT [Account ID] AS [Account ID], [Created Date Backend] AS [Created Date] FROM [DE A] A INNER JOIN [DE B] B ...
0
votes
1answer
2k views

SOQL Joins compared to SQL Joins [closed]

What are the key differences between SOQL Joins and SQL Joins?
3
votes
2answers
1k views

How to Split a string by delimited character and create new rows for every split using SQL query

Hello I have a requirement wherein I have a table row containing a field having value such as ABCDE|12345A|187251D| and so on. The exact length is not known. I want to write a query in SQL to split ...
0
votes
1answer
100 views

Joining Multiple DEs - system error

We're having problems joining multiple DEs to bring back every row of data. We're using this SQL: FROM CIPD_Master_Contact_DE MC FULL JOIN Contact_Salesforce_2 SF ON MC.Contact_Number=SF....
0
votes
3answers
100 views

Query - Only send to subscribers that are in uploaded CSV

I would like to only send email to Subscribers in List who are included in the file uploaded in FTP, not the whole list. Is this possible to do with a Query that populates a data extension? If not ...
0
votes
2answers
2k views

Querying business unit from Enterprise Account

I have a requirements for reports on business units from the enterprise level. I am trying to generate reports on data from each business unit from the enterprise level. Is there a way to write SQL ...
1
vote
1answer
109 views

Marketing Cloud - Replacing Multiple hardcoded values in JOINS with values from Parameter Table

Salesforce Marketing Cloud query activities do not allow variables or temporary tables according to the "SQL Support" section of this official documentation (http://help.marketingcloud.com/en/...
0
votes
1answer
72 views

How to properly sort away contacts with SQL?

I have two methods of subtracting subscribers, when generating a new segment. Simplistically, it looks as follows: Method A: SELECT SubscriberKey, ... other fields FROM TableX WHERE SubscriberKey ...

1
9 10 11
12
13